title: Storing and retrieving Active Directory objectGUID in SQL Server
body: <p>I am close to making this work. I need to identify a user on our intranet. I need to store that user's <code>objectGUID</code> 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.</p>
  
  <p>I am able to connect to AD by using the steps in this tutorial <a href="http://sql.dzone.com/news/querying-active-directory-thro" rel="nofollow">http://sql.dzone.com/news/querying-active-directory-thro</a></p>
  
  <p>I can retrieve the <code>objectGUID</code> and anything else I need, but I am not sure how to store the <code>objectGUID</code> in the database or how to query the database using the <code>objectGUID</code>.</p>
  
  <p>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.  </p>
  
  <p>Selecting a record from active directory and inserting into a table shows the data type  inserted objectGUID as varbinary(256)</p>
  
  <pre><code>select  *
  into temp_table
  from  openquery(adsi, '
  select  givenName,
                  sn,
                  sAMAccountName,
                  objectGUID              
  from    ''LDAP://dc=somedomain,dc=com''
  where   sAMAccountName = ''some_user''
  ')
  </code></pre>
  
  <p>Just to test, I tried querying AD with objectGUID retrieved from the temp_table above.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Returns no rows...</p>
  
  <p>Initially I thought this was the right syntax with the quotes</p>
  
  <pre><code>where objectGUID = '+@var+'
  </code></pre>
  
  <p>but returned an error: Invalid operator for data type. Operator equals add, type equals varchar</p>
  
  <p>So maybe I am close with wrong syntax, or still a data type problem?</p>
  
  <p>Thanks in advance.</p>