Querying LDAP from SQL

Transact-SQL
1

I need to query LDAP with out a linked server. I cannot get my syntax right, between the ' quotes, and the variables I am all mixed up.

SELECT Cast(objectGUID AS uniqueidentifier) 
FROM OPENROWSET( 'ADSDSOObject'
				,'adsdatasource'; @AdUser ; @adPassword 
				,'SELECT objectGUID, SAMAccountName
			FROM ''LDAP://Domainname.company.com/ou=northamerica,dc=domainname,dc=company,dc=com'' 
				WHERE Name = ' @Lookupname '')
2 
    Set @ldapSql = 'SELECT  @userObjectGuid = Cast(objectGUID AS uniqueidentifier) ' + Char(13)
SET @ldapSql = @ldapSql + 'FROM ' +  Char(13)
SET @ldapSql = @ldapSql + 'OPENROWSET(' + Char(39) + 'ADSDSOObject' + Char(39) +',' + Char(39) + 'adsdatasource'+ Char(39) +';' + Char(39) + @aduser + Char(39) + ';' + Char(39) + @AdPassword + Char(39) +', ' +  Char(13)
SET @ldapSql = @ldapSql +  Char(39) +'SELECT objectGUID, SAMAccountName ' +  Char(13)
SET @ldapSql = @ldapSql + 'FROM '+ Char(39) + Char(39) + @LdapQuery + Char(39) +  Char(39) +  Char(13)
SET @ldapSql = @ldapSql + 'WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''''') ' +  Char(13)
SET @ldapSql = @ldapSql + 'WHERE sAMAccountName = ' + Char(39) +  @SamAccountName  + Char(39)
3

Personally I would do it like this:

First create a string of the inner SQL query. I would use quote-marks instead of CHAR(39), but note that they should only be doubled-up to one level; It you find it hard to read perhaps create a @Parameter for the double-quote - i.e.

DECLARE @SQuote char(1) = '''', @DQuote char(2) = ''''''
SELECT	[@SQuote=@SQuote, [@DQuote]=@DQuote
SELECT	[FooBar] = '''''Foo'';''Bar'''''
-- can be rewritten as:
SELECT	[FooBar] = @DQuote + 'Foo' + @SQuote + ';' + @SQuote + 'Bar' + @DQuote

I've used REPLACE in case @LdapQuery itself contains any single-quote (i.e. beware of SQL Injection):

SET	@QuerySql = 'SELECT objectGUID, SAMAccountName ' +  Char(13)
	+ 'FROM ''' 
		+ REPLACE(@LdapQuery, '''', '''''')
		+ '''' +  Char(13)
	+ 'WHERE objectCategory = ''Person'' AND objectClass = ''user'''
SELECT [DEBUG1]=@QuerySql

Then I would use that @QuerySql VALUE within the OPENROWSET, also using REPLACE to double-up any quote marks within it. I find this approach much easier than trying to use triple or quadruple quote marks in a single, composite, string. Again, I've used REPLACE on other @Parameters to make sure that any embedded quotes get doubled-up.

SET	@ldapSql = 'SELECT  @userObjectGuid = Cast(objectGUID AS uniqueidentifier) ' + Char(13)
	+ 'FROM ' +  Char(13)
	+ 'OPENROWSET(''ADSDSOObject'',''adsdatasource'';'''
				+ REPLACE(@aduser, '''', '''''')
				+ ''';''' 
				+ REPLACE(@AdPassword, '''', '''''')
				+ ''', ' +  Char(13)
			+  '''' + REPLACE(@QuerySql, '''', '''''') 
			+  ''''
		+ ')' + Char(13)
	+ 'WHERE sAMAccountName = ''' 
		+ REPLACE(@SamAccountName, '''', '''''')
		+ ''''
SELECT [DEBUG2]=@ldapSql