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
General page
- 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
Security page
- 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.
Great post. Nice guide, works!
Thank you so much…Great Work…..
Thank you very much. It worked great, except that the query returns the first 1000 records, not the whole thing. Do I need to tweak some settings?
Great info! Got working first try.. but only receive 901 rows of data and then it errors out with:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “ADsDSOObject” for linked server “ADSI”.
Any ideas?
Usually it happens when result set has more that 1000 rows.
I didn’t work out solution for that yet. Usually just ask more specific query to reduce numer of rows below 1000.
This works VERY fine ..
any tip on how to permanently link this AD data as a readable table in SQL ?
An error Accord
Error 7301
and
Error 7321
found this in a MS website
The Microsoft OLE DB Provider for Microsoft Directory Services provides access to information in the Microsoft Windows 2000 Directory Service. The maximum number of objects that can be returned by a query using this provider is 1000.
http://technet.microsoft.com/en-us/library/ms190803(v=sql.105).aspx
How to query Local users & Groups
Thanks, I too am hitting the 1000 record limit. I realize this is a Active Directory Limit. Since I am able to bring in one record at a time, is it possible to create a sql user function to pull in multiple records for say, an SSRS report?
Enjoy.. This will get you past the limitation… Ofcourse you have to create a table to hold each iteration of the data. I commented the EXEC statement, so you can see the code it generates with the PRINT.
declare @counter nvarchar(10) , @query varchar(4000)
SET @counter=Ascii(‘A’)
WHILE @counter<=Ascii('Z')
BEGIN
–PRINT CHAR(@counter)
SET @query = 'insert into SELECT * FROM OPENQUERY( ADSI,”SELECT Name, manager,CN,givenname,SN,
mail,telephonenumber,SAMAccountName,company,displayname,distinguishedname
FROM ””LDAP://DC=corp,DC=company,DC=ad”” Where objectCategory = ””Person””
AND objectClass = ””user”” AND NOT SN=””@*”” AND CN =””’+char(@counter)+’*’+””” + ”’)’
print @query
–exec (@query)
SET @counter=@counter+1
end
Are we missing something in our server setup? If I could get this going it would save me a lot of pain and suffering in rewriting an old dts activex script…
Here’s the query: select * from openquery(ADSI,’select distinguishedName,name,member from ”;((objectCategory=Group);distinguishedName,name,member;subtree)”’)
And here’s the error: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query “select distinguishedName,name,member from ‘;((objectCategory=Group);distinguishedName,name,member;subtree)'” for execution against OLE DB provider “ADsDSOObject” for linked server “ADSI”.
Thanks.
This is great, but we are not sure how to query AD to get info. We manage our SSRS folder permissions using AD and it would be a huge benefit to be able to query AD to get a list of who has permissions to folders and reports on our SSRS. Any feedback or suggestions would be greatly appreciated.
Thanks
AD doesn’t store information about folder permissions, so in that case proper way will be to list permissions on a folder.
Any idea how we would go about doing that? Any further help will be greatly appreciated. Right now we are manually documenting who has access to which report folder. Not the ideal situation.
Thanks
Hi this is good and I have it working but I need to gather group information. Now I got the query changed to bring groups in but I can only get 2 attribute columns to appear, cn and ADsPath. I need to gather other attributes form the group such as managed by, description and email. Is this possible?
Howdy,
I have a SQL job that runs now and populates a DB with a bunch of user attributes.
I’m trying to get the ‘samaccountname’ of the manager for each of the users.
I have no idea how to do this as adding just a command to pull “manager” pulls the DN of the manager.
I understand why it does that, but how do I get the samaccountname and then add that to the list of items that populates the database?
SSRS stores all of the information about your SSRS reports in it’s own database. Connect with SSMS to the database engine of the server that is running SSRS. You’ll see a database in there called ReportServer. a query like this should help.
select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
inner join dbo.Policies B on A.PolicyID = B.PolicyID
inner join dbo.Users C on A.UserID = C.UserID
inner join dbo.Roles D on A.RoleID = D.RoleID
inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName
I am getting this error.
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query “SELECT * FROM ‘LDAP://DC1/DC=NHSINC,DC=local'” for execution against OLE DB provider “ADsDSOObject” for linked server “ADSI”.
Please advise.
Thank you
How to query the extra attribute that i have added in Active directory .
i couldn’t process the extra columns through sql server.
is there any solution?
Please check all credentials along the way between SQL and AD.
This might be lack of permissions for user you setup to query AD or you have authentication and authorization setup incorrectly in linked server.
Can you provide example query with custom attributes you defined?
Thanks Skufel for your reply.we found it.
Now we face some trouble in accessing thumbnail photo as it is getting truncated if size
exceeds 4000 is there any solution?
Hi there! I could have sworn I’ve been to this website before but after
going through many of the posts I realized it’s new to me.
Nonetheless, I’m certainly happy I stumbled upon it and I’ll
be book-marking it and checking back frequently!
Hello, I have the same error and it is not the number of records.
my query is:
select TOP 901 SAMAccountName from openquery
(
ADSI,’SELECT SAMAccountName
FROM ”LDAP://DC=vepica,DC=com”
WHERE objectCategory = ”Person” AND objectClass = ”user”
‘)
Do you know what might be causing this error?
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “ADsDSOObject” for linked server “ADSI”