I think it’s common problem as Active Directory gets bigger in organizations, control over content of that mission-critical component seems to be lost. Same happened in my place. In addition to that we had to provide information from AD on a regular basis in a form of report. We had no tools to pull all data from AD, process it and present in some readable format. However, I was working with skilled SQL guy who was also very good in Reporting Services. So, I proposed that we can link Active Directory with SQL Server and use Transact-SQL to pull information from AD, process it on SQL and then make it readable and presentable through Reporting Services. That would allow SQL guy to process all information and do all kind of reporting required.
Here are components of our LAB environment before solution went to production:
And below steps how we made SQL Server 2008 R2 to work with Active Directory to use Transact-SQL queries to pull information from directory database.
As usual, first I setup whole environment in LAB environment. Servers required for that:
- lab-dc-01 – 172.16.90.11 – Domain Controller
- lab-sql-01 – 172.16.90.21 – SQL Server 2008 R2
Both machines are virtual machines on VMware Workstation 8 and are running Windows Server 2008 R2.
SQL Server has ability to define Linked Servers. This mechanism allows to communicate with external data sources and refer to those sources from Transact-SQL queries on your SQL Server machine.
One of those external sources can be Microsoft Directory Service, which in our case is represented by Active Directory. So, now we need to inform SQL Server that there is additional source of data available and this source of data is in fact Active Directory.
Linking lab-dc-01 with lab-sql-01
To define directory services as Linked Server follow steps:
- Logon to lab-sql-01 and start Microsoft SQL Server Management Studio
- In Object Explorer navigate to Server Objects and right-click on Linked Servers and then click New Linked Server
- In New Linked Server window populate information on General page and Security page as shown below
- Linked server: ADSI
- Server type: Other data source
- Provider: OLE DB Provider for Microsoft Directory Services
- Product name: Active Directory Services 2.5
- Data source: adsdatasource
- Provider string: ADSDSOObject
- For the login not defined in the list above, connections will: Be made using login’s current security context
Once Linked Server is defined just write queries to read information from Active Directory and you can process all results in Transact-SQL.
SELECT * FROM OPENQUERY (ADSI , 'SELECT displayName, userPrincipalName FROM ''LDAP://lab-dc-01/DC=laboratory,DC=ltd'' WHERE objectClass = ''Person''')
And here you ca see how same query looks executed in Microsoft SQL Server Management Studio.