Storing and retrieving Active Directory objectGUID in SQL Server

Question

I am close to making this work. I need to identify a user on our intranet. I need to store that user's objectGUID in a SQL Server database table, and be able to retrieve that record again. I have several different applications, PHP, ASP Classic and ASP.Net. I thought it might be easiest to perform the AD lookup in SQL Server.

I am able to connect to AD by using the steps in this tutorial http://sql.dzone.com/news/querying-active-directory-thro

I can retrieve the objectGUID and anything else I need, but I am not sure how to store the objectGUID in the database or how to query the database using the objectGUID.

I think it is the data type (128 length byte array?) and needs to be converted but I am not sure how to do it.

Selecting a record from active directory and inserting into a table shows the data type inserted objectGUID as varbinary(256)

select  *
into temp_table
from  openquery(adsi, '
select  givenName,
                sn,
                sAMAccountName,
                objectGUID              
from    ''LDAP://dc=somedomain,dc=com''
where   sAMAccountName = ''some_user''
')

Just to test, I tried querying AD with objectGUID retrieved from the temp_table above.

declare @qry varchar(8000)
declare @var varbinary(256)
set @var = (SELECT objectGUID from temp_table)
set @qry = 'select *
from openquery(ADSI, ''
    select
    givenName, 
    sn, 
    sAMAccountName
    from ''''LDAP://DC=somedomain,DC=com'''' 
    where objectGUID = ''''+@var+''''   
    ORDER BY displayName
'')'

exec(@qry)

Returns no rows...

Initially I thought this was the right syntax with the quotes

where objectGUID = '+@var+'

but returned an error: Invalid operator for data type. Operator equals add, type equals varchar

So maybe I am close with wrong syntax, or still a data type problem?

Thanks in advance.

Answer 1

To retrieve data from AD in single query, when You ask for specific object GUID or multiple GUIDs, there is a way to use where clause instead of from LDAP://<GUID=your guid> expression (which is very convenient in the case of query for single GUID).

The GUID of Active Directory object in where clause of Your query must have a form of string where GUID's every byte expressed in hexadecimal notation is preceded by backslash sign:

\1B\C1\93\F8\25\32\72\4E\8B\48\48\62\BB\44\49\7A

For example, You have the GUID: F893C11B-3225-4E72-8B48-4862BB44497A. First, You have to convert it to binary(16) type (byte array of length 16) and then convert to hexadecimal string and finally insert backslashes like in example above:

declare @g uniqueidentifier = 'F893C11B-3225-4E72-8B48-4862BB44497A';
declare @gs nvarchar(max);

set @gs = CONVERT(nvarchar(max), CONVERT(binary(16), @g), 2);

declare @c int = 16;
while @c > 0
begin
set @c = @c - 1;
set @gs = STUFF(@gs, (2 * @c) + 1, 0, '\');
end;

declare @q nvarchar(max) = 
'select * from openquery(AD, 
'' select cn from ''''LDAP://DC=domain,DC=com'''' 
where objectGUID = ''''' + @gs + '''''  
'')';

exec(@q);

Above code creates a following query:

select * from openquery(AD, 'select cn from ''LDAP://DC=domain,DC=com'' where objectGUID = ''\1B\C1\93\F8\25\32\72\4E\8B\48\48\62\BB\44\49\7A'' ')