Store and Retrieve the Active Directory objectGUID in SQL Server
I am close to this work. I need to identify a user on our intranet. I need to store this user objectGUID
in a SQL Server database table and get this record again. I have several different applications: PHP, ASP Classic and ASP.Net. I thought it would be easiest to do an AD lookup in SQL Server.
I can connect to AD using the steps in this tutorial http://sql.dzone.com/news/querying-active-directory-thro
I can get objectGUID
and whatever I need, but I'm not sure how to save objectGUID
to the database or how to query the database using objectGUID
.
I think it is a datatype (array length of 128 bits) and needs conversion, but I'm not sure how.
Selecting an entry from the active directory and inserting into the table shows the datatype inserted by the 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 the 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 ...
I originally thought it was the correct quoted syntax
where objectGUID = '+@var+'
but returned an error: Invalid operator for data type. Operator is add, type is varchar
So maybe I'm close to having a wrong syntax or still a data type issue?
Thanks in advance.
source to share
I was unable to use the objectGUID in the where clause, but I found I could bind to the objectGUID directly, 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)
source to share
Have you tried CAST
or CONVERT
at TSQL? http://msdn.microsoft.com/en-us/library/ms187942.aspx has a couple of examples.
source to share
It's not clear what you mean by "how to query the database using objectGUID", but if you want to know the exact data type, there are two ways:
-
If you are using at least SQL Server 2012 you can try sp_describe_first_result_set . It has several limitations, so it doesn't work in all situations.
-
You can output the results to a temp table and then examine its structure:
SELECT fields INTO #tmp FROM openquery(...); EXEC tempdb.dbo.sp_help '#tmp';
Use any data type (perhaps BINARY(128)
or VARBINARY(128)
) when creating columns to store values or when declaring local variables.
EDIT:
So now we know what objectGUID
VARBINARY (256) is. To use this query correctly in your query, remove the three sets of single quotes from the escaped string in the already escaped string. Also, we need to convert VARBINARY to VARCHAR so that it can be concatenated into Dynamic SQL string. When using the function, CONVERT
be sure to use a style number 1
that converts hex digits to a string of hex digits (ie "0x12D5"); if you do not specify "style", the default action is to translate the characters denoted by hexadecimal digits (ie "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);
source to share
I am also storing objectGUID from AD to SQL Server in my project and I am using uniqueidentifier. (I don't use OpenQuery, however, but instead use a Windows service to keep in sync with AD and populate the database.)
Although when retrieved from AD, the objectGUID appears as a varbinary array,
it actually represents a GUID, the corresponding type in SQL is a unique identifier.
And the GUID is 16 bits in size (only 128 bits).
This hasn't been tested, but try something like this:
Select CONVERT(uniqueIdentifier,objectGUID) as Id, ...
FROM OpenQuery(ADSI,
'SELECT objectGUID, ...
FROM ...
WHERE...')
source to share