How to: AD + MSSQL to SSRS

Good day. I decided that the AD data processing experience using SQL would be useful not only for me, I couldn’t find an intelligible manual in Russian.

_{For the presentation style, I ask you not to hit hard, I tried to explain as accessible as possible, as I wanted to see when I was looking for info.}

Actually, the task: “And make us a report on all the users of the company, and so that its groups are derived.” I don’t know how real it is with some kind of native AD administration tools, but the first idea that came to mind was to write a request to AD and pull out all the information that it could put in a thread and continue to process what your heart desires, whether it's a reporting service or something in the spirit of crystal reports. How to reach to AD?

It turns out that MSSQL has a native OLE DB provider for connecting to AD. Add our linked server to server objects:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI' , @srvproduct=N'Active Directory Service Interfaces' , @provider=N'ADSDSOObject' , @datasrc=N'adsdatasource' 

')
At once I would like to note that the code used in the topic has been tested several times over the past few days on different servers (2008/2008 R2 / 2012) and the generated sql script is given.

_{How via gui I will not say, because I do not know;)}

After we have created the server, you need to add a login to it:

 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI' ,@useself=N'False' ,@locallogin=NULL ,@rmtuser=N'Domain\user' ,@rmtpassword='password' 

I want to note that in the settings of the server itself, we obviously do not specify the specific ldap connection that interests us.

Settings generated by the sql server:

 EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true' 

That's it, we have a server. Now you need a query. For queries you can use two dialects:

1. 1) SQL similar syntax - and used it.
2. 2) LDAP is a similar syntax - in my opinion it looks more logical, but I found out about it when I did it.
   

Requests are executed via OPENQUERY to the server of interest to us. In the query in the FROM, we write a link in the form of LDAP: // dc = maindomain, dc = rootfolder, we wrap it in apostrophes and write a select sheet. This link can be obtained either from admins, or you can search the ad for the branch you need with any convenient ldap browser.
Request:

 SELECT * FROM OPENQUERY(ADSI,'SELECT cn,sAMAccountName FROM ''LDAP://dc=maindomain,dc=rootfolder''') 

Click f5. Ur request works. Joy is short, after 900 lines (less than a second) falls out

Can not fetch a row from the OLE DB provider "ADSDSOObject" for the linked server "ADSI".

At once I will say that this is a custom restriction of the AD itself, and not the driver, or the provider, or something else. Well, if it is not only 2000, in 2000 it is not configured. How it is configured we can see here .
We can go two ways: ask admins to reconfigure or bypass this limitation. The ultimate goal is to get all users.
Reduce the search range:

 SELECT * FROM OPENQUERY(ADSI,'SELECT cn,sAMAccountName FROM ''LDAP://dc=maindomain,dc=rootfolder'' WHERE AND objectClass=''person'' AND objectClass<>''computer'' ') 

Again a lot? Well, then you need to cheat. You can search for a specific user for example. But then we need to know the logins of all users is also not good. Well, we find at least those whose login begins with abc. We will search by field sAMAccountName - this is a field which in all AD schemes seems to be as it is, well, if you are not using an extremely unique scheme. If, however, extreme, then contact the admins let them find it (the scheme) for you. I think the field decided. Now the dialect differs from the ansi sql:

 SELECT * FROM OPENQUERY(ADSI,'SELECT cn,sAMAccountName FROM ''LDAP://dc=maindomain,dc=rootfolder'' WHERE sAMAccountName =''abc*'' AND objectClass=''person'' AND objectClass<>''computer'' ') 

If there are users starting with abc, then he will return something to you:

sAMAccountName cn
abceeeee abceeeee dfdf eee

Yes, in reverse order. No, I do not know why. Well, for example, we will think up a nested loop with enumeration of combinations of letters from a to z and a search of the type sAMAccountName = aa *, then sAMAccountName = ab *, omit the dynamic query. For example, users received. Now you need to get the group.

Please:

 SELECT * FROM OPENQUERY(ADSI,'SELECT memberOf,cn,sAMAccountName FROM ''LDAP://dc=maindomain,dc=rootfolder'' WHERE sAMAccountName =''abc*'' AND objectClass=''person'' AND objectClass<>''computer'' ') 

We get:

Can not get the data from the OLE DB provider "ADSDSOObject" for the linked server "ADSI". Could not convert or mismatch or overflow.

memberOf can be somewhat, well, logical in principle. We will ask not the user’s groups, but the groups in which there is a user and we will invite the whole structure. To get all the groups, we are looking for a group whose member has the registered dn of our desired user.

 SELECT * FROM OPENQUERY(ADSI,'SELECT name FROM ''LDAP://dc=maindomain,dc=rootfolder'' WHERE objectCategory = ''Group'' AND member=''CN=username,OU=Departments,dc=maindomain,dc=rootfolder'' ') 

Groups got what else to pull out of them is better to look in the scheme.

Now try the user "umi (Yumi Chinese Department)". Well, strange user, I do not argue. But the English name, and in Russian brackets, is quite normal. Okay, not the point.
Request:

 SELECT * FROM OPENQUERY(ADSI,'SELECT name FROM ''LDAP://dc=maindomain,dc=rootfolder'' WHERE objectCategory = ''Group'' AND member=''CN=umi(  ),OU=Departments,dc=maindomain,dc=rootfolder'' ') 

The answer is also obtained if this user has any groups. But if we export a user from where something where we have a user is called some sort of Russian long abracadabra, then the string we will have

umi( , , )
And on cn we have a limit of 64 characters, total we get:

umi( ,
From the point of view of the query, everything is fine, sql will not be cursed on the request curve, but ldap provider will swear and say that

“Here is our query” for the execution against the OLE DB provider “ADSDSOObject” for the linked server “ADSI”.

Yes, you can not look for brackets. At first I thought that it was possible to screen through quotes, then I thought that through a slash, then through an ampersand, I thought a lot. Does not work. The answer came by itself: I tried to find the same user in the ldap browser (you can take anyone, I think it will still be useful). And when constructing a query when I typed a slash, he himself suggested to me which characters can be escaped.
The list here is:

1. ( - \28
2. ) - \29
3. * - \2A
4. \ - \5C
5. Carriage Return - \0D
6. Line Feed - 0A
7. NUL - \00


Separately, it is worth noting the apostrophe, as you see in the list of screenings apostrophe no. From AD's point of view, everything is fine, the query is working, an error is thrown exactly at the request processing by the provider. It can only be escaped through sql

Our dn looks like this: CN=Luk'yanova Inna ( ),OU=Departments,dc=maindomain,dc=rootfolder .

We assigned it somehow in @dn. So that you do not have to sit around picking how many apostrophes put here:

 SET @dn = REPLACE(@dn,'''','''''''''') 

Well, and then, and then either in the procedure, or just where you need to push this query (queries) and work with the sql-tables as you like.

_{PS about errors in the topic, please notify me in a personal, and not through the comments.}