{"id":101,"date":"2012-01-13T02:37:32","date_gmt":"2012-01-13T02:37:32","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=101"},"modified":"2014-03-01T02:47:57","modified_gmt":"2014-03-01T02:47:57","slug":"querying-active-directory-data-from-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/querying-active-directory-data-from-sql-server\/","title":{"rendered":"Querying Active Directory Data from SQL Server"},"content":{"rendered":"<p>In this post I&#8217;ll show you how to query Active Directory using linked servers and the OPENQUERY command.<\/p>\n<h3>Create Linked Server<\/h3>\n<p>First thing we&#8217;ll do is create our linked server, Active Directory Service Interface also known as ASDI, to Active Directory using the code below:<\/p>\n<div>\n<pre>USE [master]\r\nGO \r\nEXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'\r\nEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\\USER',@rmtpassword='*********'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'\r\nGO \r\nEXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'\r\nGO<\/pre>\n<\/div>\n<p>Make sure you change the @rmtuser and @rmtpassword variables to a login and password that has access to your Active Directory.<\/p>\n<hr \/>\n<h3>Querying Active Directory<\/h3>\n<p>Once the linked server is created we can now setup our query to return the information we need.<\/p>\n<p>First, you&#8217;ll need to ask your Network\/Systems Administrator for your LDAP info then we can continue to the query.<\/p>\n<p>Here is how the LDAP connection is broken down:<\/p>\n<ul>\n<li>For our example it looks like this: <strong>LDAP:\/\/DOMAIN.com\/OU=Players,DC=DOMAIN,DC=com<\/strong><\/li>\n<li>LDAP:\/\/Domain.com &#8211; is the name of a domain controller<\/li>\n<li>\/OU=Players &#8211; this is the Organization Unit, in our case (Players)<\/li>\n<li>,DC &#8211; this is the Domain Name broken up by domain and extension name<\/li>\n<li>So&#8230;.LDAP:\/\/DomainControllerName.com\/OU=OrganizationalUnit,DC=DOMAIN,DC=NAME<\/li>\n<\/ul>\n<p>According to the problem, this user needs to return the companies email addresses and phone numbers. To do this we can use the code below:<\/p>\n<p>(note &#8211; you will need to change your domain information for this to work)<\/p>\n<div>\n<pre>SELECT * FROM OpenQuery ( \r\n  ADSI,  \r\n  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber \r\n  FROM  ''LDAP:\/\/DOMAIN.com\/OU=Players,DC=DOMAIN,DC=com'' \r\n  WHERE objectClass =  ''User'' \r\n  ') AS tblADSI\r\nORDORDER BY displayname<\/pre>\n<\/div>\n<p>As you can see this query will return Active Directory&#8217;s Display Name, Telephone Number, Email Address, Mobile Number, and Fax Number. Also note, that when you query Active Directory it actually creates the SELECT statement backwards. I started the SELECT statement with SELECT displayname&#8230; but in the results pane it displayed displayName last as shown below.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-102\" alt=\"SQL Freelancer SQL Server Query Active Directory\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory.png\" width=\"580\" height=\"97\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory.png 580w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-300x50.png 300w\" sizes=\"auto, (max-width: 580px) 100vw, 580px\" \/><\/a>If you wanted to view more columns for each user we can use the below code to display fields such as: FirstName, Office, Department, Fax, Mobile, Email, Login, Telephone, Display Name, Title, Company, Pager, Street Address, and more.<\/p>\n<div>\n<pre>SELECT * FROM OpenQuery\r\n  ( \r\n  ADSI,  \r\n  'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName, \r\n  mail, mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname \r\n  FROM  ''LDAP:\/\/DOMAIN.com\/OU=Players,DC=DOMAIN,DC=com''\r\n  WHERE objectClass =  ''User'' \r\n  ') AS tblADSI\r\nORDER BY displayname<\/pre>\n<\/div>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-103\" alt=\"SQL Freelancer SQL Server Query Active Directory\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-1.png\" width=\"793\" height=\"116\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-1.png 793w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-1-300x43.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-1-624x91.png 624w\" sizes=\"auto, (max-width: 793px) 100vw, 793px\" \/><\/a><\/p>\n<p>You can also filter out columns using a WHERE clause. In this example I only want to return results where users have a fax number.<\/p>\n<div>\n<pre>SELECT * FROM OpenQuery\r\n  ( \r\n  ADSI,  \r\n   'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName, mail,  \r\n  mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname\r\n  FROM  ''LDAP:\/\/DOMAIN.com\/OU=Players,DC=DOMAIN,DC=com''   \r\n  WHERE objectClass =  ''User'' \r\n  ') AS tblADSI\r\nWHERE facsimileTelephoneNumber IS NOT NULL\r\nORDER BY displayname<\/pre>\n<\/div>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-104\" alt=\"SQL Freelancer SQL Server Query Active Directory\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-2.png\" width=\"757\" height=\"99\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-2.png 757w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-2-300x39.png 300w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Query-Active-Directory-2-624x81.png 624w\" sizes=\"auto, (max-width: 757px) 100vw, 757px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this post I&#8217;ll show you how to query Active Directory using linked servers and the OPENQUERY command. Create Linked Server First thing we&#8217;ll do is create our linked server, Active Directory Service Interface also known as ASDI, to Active Directory using the code below: USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N&#8217;ADSI&#8217;, @srvproduct=N&#8217;Active Directory [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[38,37,40],"class_list":["post-101","post","type-post","status-publish","format-standard","hentry","category-linked-servers","tag-active-directory","tag-linked-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=101"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/101\/revisions"}],"predecessor-version":[{"id":105,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/101\/revisions\/105"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}