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'' ')

Answer 2

I was unable to use objectGUID in the where clause, but I did find that I could bind directly to the objectGUID which gives me the same end result.

DECLARE @qry varchar(8000) 
DECLARE @ObjectGUID uniqueIdentifier

SET @ObjectGUID = (SELECT objectGUID FROM temp_table)

SET @qry = 'select *
FROM openquery(ADSI, ''
    SELECT  givenName,
                sn,
                sAMAccountName,
                objectGUID              
from    ''''LDAP://<GUID=' + CAST(@ObjectGUID as CHAR(36)) + '>''''
'')'

EXEC(@qry)

Answer 3

I'm also storing objectGUID from AD into SQL server in my project and i use uniqueidentifier. (However I don't use OpenQuery but instead using a windows service to constantly sync from AD and populate the DB.)

Although when got from AD objectGUID appears as a binary array (varbinary),
it is actually representing a GUID, the corresponding type in SQL is uniqueidentifier.
And GUID is 16 byte in size (128 bits) only.

This is not tested, but please try something like this:

Select CONVERT(uniqueIdentifier,objectGUID) as Id, ...
FROM OpenQuery(ADSI,
    'SELECT objectGUID, ...
     FROM ...
     WHERE...')

Answer 4

It is unclear what you mean by "how to query the database using the objectGUID", but if you want to know the exact datatype, there are two ways:

1. If you are using at least SQL Server 2012, then you can try sp_describe_first_result_set. It has several restrictions so does not work in all situations.

2. You can dump the results to a temp table and then examine its structure:

   SELECT fields
   INTO #tmp
   FROM openquery(...);
   
   EXEC tempdb.dbo.sp_help '#tmp';
   

Use whatever datatype is returned (probably BINARY(128) or VARBINARY(128)) when creating columns to store the values or when declaring local variables.

EDIT:
So we now know that objectGUID is a VARBINARY(256). In order to use that properly in your query, remove three sets of single-quotes from the escaped string within an already escaped string. Also, we need to convert the VARBINARY to a VARCHAR so that it can be concatenated into the Dynamic SQL string. When using the CONVERT function be sure to use the "style" number of 1 which translates the hex digits to a string of hex digits (i.e. "0x12D5"); if you don't specify a "style" the default action is to translate to the characters denoted by those hex digits (i.e. "Hello!").

DECLARE @Query VARCHAR(8000),
        @ObjectGUID VARBINARY(256);

SELECT @ObjectGUID = objectGUID
FROM   temp_table;

SET @Query = 'SELECT *
FROM OPENQUERY(ADSI, ''
    SELECT
    givenName, 
    sn, 
    sAMAccountName
    FROM ''''LDAP://DC=somedomain,DC=com'''' 
    WHERE objectGUID = ' + CONVERT(VARCHAR(300), @ObjectGUID, 1) + '
    ORDER BY displayName;
 '')';

PRINT @Query; -- see what SQL is being executed
EXEC(@Query);

Answer 5

Have you tried CAST or CONVERT in TSQL? http://msdn.microsoft.com/en-us/library/ms187942.aspx has a couple examples.