Search

Wednesday, September 26, 2007

Querying Active Directory Through SQL Server

Integrating your application with Active Directory (or another LDAP based directory) is a common requirement in many business applications. Almost every language has a way to query LDAP but little known is the approach of integrating SQL Server with your LDAP controller. This quick article will guide you through setting up and using LDAP queries through SQL Server 2000 with Active Directory as the LDAP controller.

First, you need to create a SQL Server linked server. Set it up through SQL Server Enterprise Manager:
  1. Open SQL Server Enterprise Manager
  2. Go to the database server to which you will be adding the linked server
  3. Expend "Security"
  4. Right-click on "Linked Servers" and click on "New Linked Server..."
  5. Fill in the following:
    1. Under the General tab:
      1. Linked Server: adsi (or whatever you want to call it)
      2. Server type: select Other Data Source
      3. Provider name:select OLE DB Provider for Microsoft Directory Services
      4. Under Provider Options: check that Allow InProcess is checked
      5. Leave the rest of the fields blank
    2. Under the Security tab:
      1. Local Login: sqlServerUser
      2. Remote User: ntaccount@domain.com (such as bkostadinov@ica.com or ica.com\bkostadinov)
      3. Remote Password: userPassword for the above domain account
Through Query Analyzer:
  1. Change the "AllowInProcess" registry key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ADSDSOObject to "dword:00000001"
    1. Edit the registry manually or put the following in a .reg file and execute it:
      Windows Registry Editor Version 5.00
      
      [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ADSDSOObject]
      
      "AllowInProcess"=dword:00000001
            
  2. Open Query Analyzer (or your choice of query tool)
  3. Connect to the server to which you will be adding the linked server
  4. Change the provided values and execute the following code:
-- Change 'adsi' to the desired name of the linked server
exec sp_addlinkedserver
  'adsi',
  '',
  'ADSDSOObject',
  ''
go

-- Change 'adsi' to the desired name of the linked server
-- Change 'sqlUser' to the username of local sql server user
-- Change 'domainName\userName' to a domain account
-- (the format can be 'domainName\userName' or 'userName@domainName')
-- Change 'domainUserPassword' to the password of the domain account
exec sp_addlinkedsrvlogin
  'adsi',
  false,
  'sqlServerUser',
  'domainName\userName',
  'domainUserPassword'
go

Or you can just fill in the values on top of the following script and run that:

declare @linkedServerSql nvarchar(4000),
  @linkedServerName varchar(100),
  @localSqlUsername varchar(100),
  @domainUsername varchar(100),
  @domainUserPassword varchar(100)

-- Set the local sql server user
set @localSqlUsername = 'sqlUser'
-- format can be 'domainName\userName' or 'userName@domainName'
set @domainUsername = 'domainName\userName'
set @domainUserPassword = 'domainUserPassword'

set @linkedServerName = 'adsi'

set @linkedServerSql = '
  exec sp_addlinkedserver
  ''' + @linkedServerName + ''',
  '''',
  ''ADSDSOObject'',
  '''' 

exec sp_addlinkedsrvlogin
  ''' + @linkedServerName + ''',
  false,
  ''' + @localSqlUsername + ''',
  ''' + @domainUsername + ''',
  ''' + @domainUserPassword + ''''

exec sp_executesql @linkedServerSql
Run a query to verify that the linked server works. The query below will give you all the users in the dc=ica,dc=com (change that to match your own domain):
select  *
from  openquery(adsi, '
select  givenName,
    sn,
    sAMAccountName,
    displayName,
    mail,
    telephoneNumber,
    mobile,
    physicalDeliveryOfficeName,
    department,
    division
from    ''LDAP://dc=ica,dc=com''
where   objectCategory = ''Person''
        and
        objectClass = ''user''
')
Below, is an alternate syntax which you can use to apply ldap filters with almost universal syntax. The following will get all the users in LDAP but limit the result set to those users who's "given" & "sn" names are not empty. It will also apply a filter to the "division" attribute and exclude any records that match "system" and "generic".
declare @ldapFilter nvarchar(1000), @ldapSQL nvarchar(4000)

-- Set the filter to exlude objects that have a division of "System" and "Generic"
set @ldapFilter = '(!division=System*)(!division=Generic)'

-- Create an ldap query to get all users under dc=ica,dc=com
set @ldapSQL = '
select  givenName as firstName,
        sn as lastName,
        displayName,
        lower(sAMAccountName) as accountName,
        telephoneNumber as phoneNumber,
        mobile as cellPhoneNumber,
        mail as emailAddress,
        department,
        physicalDeliveryOfficeName as siteName
from    openquery(adsi, ''<LDAP://dc=ica,dc=com>
    (&(objectCategory=Person)(objectClass=user)'+ @ldapFilter + ');
        givenName,
    sn,
    sAMAccountName,
    displayName,
    mail,
    telephoneNumber,
    mobile,
    physicalDeliveryOfficeName,
    department,
    division;
    subtree'')
where   givenName is not null
    and
    sn is not null'

exec sp_executesql @ldapSQL

Some things to note:

"LDAP" is case sensitive, if you try using "ldap", the query will throw an error.

Querying the Active Directory server will work fine from Query Analyzer even if you do not run sp_addlinkedserverlogin. However, if you try to execute the query from the web (with something like ColdFusion, you will get an error similar to:

[Macromedia][SQLServer JDBC Driver][SQLServer]OLE DB provider 'ADSDSOObject' reported an error. The provider indicates that the user did not have the permission to perform the operation.

21 comments:

  1. Was wonder how to deal with the 1000 record limit that AD imposes on a return and is hadled by a paging object in vb?

    ReplyDelete
  2. Hi, i have a question....can i specify an OU in the query?

    regards

    ReplyDelete
  3. Yes, you can use an OU in the LDAP query.

    ReplyDelete
  4. Hello, Thanks for the article on the useful feature. I hope to make it work.

    can you please explain if the
    "AllowInProcess" should be "dword:00000001" or just "00000001". Is it Hexa or just decimal?

    Also I am not sure how to configure the 'dc' part of the connection string: I tried the foll:
    dc=xxx,dc=yyy,dc=zzz (where the company domain is xxx.yyy.zzz example: mail.yahoo.com)
    dc=MyDomain
    dc=ldap-server
    dc=Domain,dc=com
    etc

    Can you please guide me where I can get the exact setting for this.

    Thanks

    ReplyDelete
  5. "AllowInProcess" should be "dword" type with a valud of "00000001".

    To configure DC for mail.yahoo.com it would be: dc=yahoo,dc=com

    ReplyDelete
  6. Thanks for your reply. I am testing by running

    select * from openquery(LS, 'select * from ''LDAP://dc=Domain,dc=com''')

    but get the message:
    OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e09: The provider indicates that the user did not have the permission to perform the operation.].
    Msg 7399, Level 16, State 1, Line 2
    OLE DB provider 'ADsDSOObject' reported an error. The provider indicates that the user did not have the permission to perform the operation.

    That prompts me to ask this question:
    Can the Remote User account be the same as the Local Login?

    Thanks so much, again.

    ReplyDelete
  7. Hello Boyan,

    Thank you for the clear overview.
    I am trying to get this to work with AD LDS, but I am running in to the error "The provider indicates that the user did not have the permission to perform the operation" almost every time. To only way I can get it to work is by configuring the linked server to use the login's current security context (I think this is the same as setting "useself" to TRUE in the procedure sp_addlinkedsrvlogin). If I configure the same credentials (the credential with which I am logged in to SQL Server) for the linked server (rmtuser and rmtpassword) the connection fails. I don't understand this at all, do you?
    My goal is to have an internal SQL Server user create a view on the AD LDS.

    ReplyDelete
  8. @Koningin Juliana:

    The user accessing AD must be an AD user. You cannot use a SqlServer account to access AD.

    If rmtuser is not an AD user then it won't work. If rmtuser is an AD user try rmtuser@yourdomain.com or YOURDOMAIN\rmtuser.

    You may need to set up a new account for this.

    I'm guessing you're using Windows Authentication to log into SqlServer which would explain why it.

    I'm pretty sure you can't create views in AD, either. You'd have to create a view in your local database over a query from the AD database.

    ReplyDelete
  9. Hi - I'm querying AD from SQL 2000 and am trying to extract the 'Info' field which I believe is the user notes field. However, I'm having the problem where only the first 256 characters are coming across...is there any way round this? None of the convert/format functions appear to work...

    ReplyDelete
  10. Sorry mate, not sure why you are only getting 256 characters. Never tried to use that field. Google is your friend I guess.

    ReplyDelete
  11. Great blog, thanks couple of questions though
    my query only return 1000 rows, how to deal with the 1000 record limit that AD imposes on a return ?
    how do we collect lastpwdset value from AD using same query ?
    Appriciate any help

    ReplyDelete
  12. I don't know a way to get past 1000 records. I think that's the limit of this method.

    ReplyDelete
  13. You rock mate.
    This has been an irritating one for me for some time but finally I am able to query my LDAP through SQL.

    Thanks a zillion

    ReplyDelete
  14. Would you have any interest in updating this for MSSQL 2005? You provide details that other places do not, but the process changed for 2005, so I'm still having issues.

    ReplyDelete
  15. I could try this on SQL Server 2005 snd 2008 and see what I figure out.

    ReplyDelete
  16. Hi Boyan,

    You saved me by mentioning that ldap is case sensitive, I was scratching my head for two days on an error that I was recieving. As soon as I changed ldap to "LDAP" it worked like magic. Thanks a lot!
    My LDAP queries work only when I am on that machine, where as if I try running the same query through sql management studio connecting from some other machine, it doesnot run. Can you please tell me why this is happening and how to resolve this?

    Thanks,
    TP

    ReplyDelete
  17. when I say "SELECT * FROM LDAP://svrname", it returns only AdsPath. How can I see other fields like cn, c, sn, title and others ?

    ReplyDelete
  18. To handle more than 1000 records, consider using a cursor?

    ReplyDelete
  19. Any luck making this work for MSSQL 2005 or 2008?

    Thanks in advance

    ReplyDelete
  20. Hey guys, this is an old post, but I thought I would share a quick cursor that I created to loop through the different OU's and avoid the 1000 record limit.

    My solution specifically queries the users OU which which is a subordinate of many other OUs -- therefore none of the individual Users OUs are greater than the 1000 record limit.

    Also on the my post I included a link to another solution the uses SQL to correctly page through the AD results which will work no matter how big your OUs are.

    Check out my blog if you have time.
    The Emerging DBA

    ReplyDelete
  21. Excellent article! We are linking to this particularly great post on our site.
    Keep up the good writing.

    Visit my blog post; cute stationery

    ReplyDelete

// //]]>