locked
Retrieve GUID or SID FROM Active Directory via ADSI and T-SQL only? RRS feed

  • Question

  • Hi all,

     

    is there a way to get an object's SID or GUID using T-SQL only?

     

    Up to now I got the following code of Active Directory Service Interfaces working,

    but GUID or SID are not among the parameters known to me.

     

    Code Snippet

    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

     

    SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

     

    I do not want to use anything other then SQL Server 2000 to get an AD-object's primary key.

     

    Any comments would be appreciated.

     

    Thank you!

     

    Regards,

    caracol

     

    ---

    Windows 2000 Server, SQL Server 2000, AD in W2K only mode

    Tuesday, June 26, 2007 2:04 PM

Answers

  • Hi all,

     

    just in case there should be someone else looking for identifiable data to be extracted from Active Directory:

     

    A complete list with all attributes can be found at

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/adschema/attributes_all.asp

     

    For every attribute the LDAP-Display-Name is given which can be accessed by ADSI.

     

    My SELECT from above should be

    Code Snippet
    SELECT * FROM OpenQuery(ADSI, 'SELECT objectGUID, title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

     

    then.

     

    Regards,

    caracol

    Thursday, June 28, 2007 1:56 PM

All replies

  • This is not an SSIS question.  Moving to the Transact-SQL Forum.
    Tuesday, June 26, 2007 2:09 PM
  • Hi all,

     

    just in case there should be someone else looking for identifiable data to be extracted from Active Directory:

     

    A complete list with all attributes can be found at

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/adschema/attributes_all.asp

     

    For every attribute the LDAP-Display-Name is given which can be accessed by ADSI.

     

    My SELECT from above should be

    Code Snippet
    SELECT * FROM OpenQuery(ADSI, 'SELECT objectGUID, title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whatever,DC=domain,DC=org'' where objectClass = ''User''')

     

    then.

     

    Regards,

    caracol

    Thursday, June 28, 2007 1:56 PM
  •  

    Can we find user detail using a GUID using sql query. Tried


    SELECT *
    FROM OPENQUERY(ADSI,'SELECT ObjectGUID,ST,facsimileTelephoneNumber, company, postalCode,l, streetAddress,  displayName, samAccountName , sn , givenName , telephonenumber , department,
     mail, title , adspath  FROM ''LDAP://dc=sl,dc=ad,dc=csx,dc=com''  WHERE objectCategory = ''user'' AND GUID=''C608536E-77CE-4F25-9A84-DAFBCC434057''') 

     

    Not getting any data.

    Thursday, August 14, 2008 8:24 PM
  • I don't know if you are still looking for this (probablyu u have solved a long time ago), but i hope this works for others aswell.

    1. you are not getting any data because both objectGUI and ObjectSID are stored in a binary array.
    2. the easiest way to get SID on T-SQL is SUSER_SID() function i have matched  it with AD objectSID and it's the same


    regards 

    Jorge

    PS i know this is not the right forum... but i can't seem to find it on the T-SQL one
    Tuesday, June 16, 2009 1:33 PM