How to: AD + MSSQL

Доброго дня. Решил что опыт обработки данных AD посредством SQL будет полезен не только мне, тем более внятного мануала на русском я так и не нашел.
_{За стиль изложения прошу сильно не бить, старался объяснить как можно доступнее, как сам хотел увидеть когда искал инфу.}
Собственно задача: «А сделайте нам отчет по всем пользователям компании, да так чтобы и группы его выводились». Не знаю насколько это реально какими то нативными средствами администрирования AD, но первая идея пришедшая в голову написать запрос к AD и вытянуть с него всю инфу которую только можно в какую нить табличку и дальше уже обрабатывать чем душе угодно, будь то reporting service или что нить в духе crystal reports. Как же достучаться до AD?
Оказывается у MSSQL есть нативный OLE DB провайдер для подключения AD. Добавляем наш linked server в server objects:

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


Сразу хотел бы отметить что код, используемый в топике, уже не раз опробован за последние несколько дней на разных серверах (2008/2008 R2/2012) и приводится генерируемый sql скрипт.
_{Как через gui это сделать не скажу, ибо не знаю ;)}
После того как мы создали сервер необходимо добавить к нему логин:

1. 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'Domainuser',@rmtpassword='password'


Хочу заметить, что в настройках самого сервера мы явно не указываем интересующее нас конкретное ldap подключение.Настройки генерируемые sql-сервером:

1. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'collation compatible', @optvalue=N'false'
2. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'data access', @optvalue=N'true'
3. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'dist', @optvalue=N'false'
4. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'pub', @optvalue=N'false'
5. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'rpc', @optvalue=N'false'
6. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=N'rpc out', @optvalue=N'false'
7. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'sub', @optvalue=N'false'
8. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'connect timeout', @optvalue=N'0'
9. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'collation name', @optvalue=null
10. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'lazy schema validation', @optvalue=N'false'
11. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'query timeout', @optvalue=N'0'
12. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'use remote collation', @optvalue=N'true'
13. EXEC master.dbo.sp_serveroptioN @server=n'adsi', @optname=n'remote proc transaction promotion', @optvalue=N'true'

Все, сервер у нас есть. Теперь нужен запрос. Для запросов можно использовать два диалекта:

1. 1) SQL подобный синтаксис — его и использовал.
2. 2) LDAP подобный синтаксис — на мой взгляд более логично выглядищий, но узнал я о нем уже когда все сделал.

Запросы выполняются через OPENQUERY к интересующему нас серверу. В запрос в FROM пишем ссылку ввида LDAP://dc=maindomain,dc=rootfolder, заворачиваем её в апосторофы и пишем селект лист. Ссылку эту можно получить либо у админов, либо самому поискать по ad нужную нам ветку любым удобным браузером ldap. Запрос:

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


Жмем f5 ура запрос работает. Радость недолгая, спустя строк 900 (меньше секунды) вываливается

Сразу скажу что это настраиваемое ограничение самого AD, а не драйвера, или провайдера, или еще чего нибудь. Ну если он только не 2000, в 2000 это не настраивается. Как это настраивается можем посмотреть здесь.
Можем пойти двумя путями: попросить админов перенастроить или обойти это ограничение. Конечная цель получить всех пользователей.Сузим круг поиска:

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


Опять много? Ну что ж, значит нужно хитрить. Можно искать конкретного пользователя например. Но нам нужно тогда знать логины всех пользователей тоже не хорошо. Ну найдем хотя бы тех у которых логин начинается на abc. Искать будем по полю sAMAccountName — это поле которое во всех схемах AD вроде как есть, ну если вы не экстремально уникальную схему используете. Если все же экстремальную, тогда обращайтесь к админам пускай найдут её (схему) для вас. Думаю с полем определились. Теперь пошли отличия диалекта от ansi sql:

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


Если есть пользователи начинающиеся на abc, то он вам что нибудь вернет:sAMAccountName cnabceeeee abceeeee dfdf eeeДа, в обратном порядке. Нет, не знаю почему. Ладно к примеру мы придумаем вложенный цикл с перебором комбинаций букв от a до z и поиск типа sAMAccountName=aa*, потом sAMAccountName=ab*, опустим динамический запрос. К примеру пользователей получили. Теперь нужно группы получить. Просим:

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


Получаем:

memberOf может быть несколько, ну логично в принципе. Попросим не группы пользователя, а группы в которых есть пользователь и приджойним всю эту конструкцию. Чтобы получить все группы, ищем группу у которой в member прописан dn нашего искомого пользователя

1. 
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'' ')


Группы получили, что еще вытянуть из них лучше посмотреть в схеме. Теперь попробуем пользователя «umi(юми китайский департамент)». Ну странный пользователь, не спорю. Но английское имя, а в скобках русское, вполне себе нормально. Ладно, не суть. Запрос:

1. 
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'' ')

Ответ тоже получим, если у этого пользователя есть какие нибудь группы. Но если мы экспортируем пользователя откуда нибудь где у нас пользователь зовется какой нибудь русской длинной абракадаброй, тогда строка у нас будет
umi(юми китайский департамент, должность не помню как искать долго, ну просто так)А на cn у нас ограничение в 64 символа, итого получаем:
umi(юми китайский департамент, должность не помню как искать долС точки зрения запроса все нормально, sql ругаться не будет на кривой запрос, но ldap провйдер ругнется и скажет что

Да, скобки искать нельзя. Сначала я думал что экранировать можно через кавычки, потом думал что через слеш, потом через амперсанд, много думал. Не получается. Ответ пришелся сам по себе: я этого же пользователя попробовал найти в ldap браузере (любого можете взять, думаю все равно пригодится). И при конструировании запроса когда я ввел слеш он сам мне подсказал какие символы можно экранировать. Список вот:

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


Отдельно стоит отметить апостороф, как видете в списке экранирований апострофа нет. С точки хрения AD все нормально, запрос работает, вываливается ошибка именно на обработке запроса провайдером.Экранировать его можно только через sql Наш dn выглядит так: CN=Luk'yanova Inna (Лукьянова Инна Инновна),OU=Departments,dc=maindomain,dc=rootfolderМы его присвоили каким то образом в @dn. Чтоб не пришлось вам лишний раз сидеть ковырять сколько апострофов поставить вот:

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


Ну а дальше, а дальше либо в процедурину, либо просто куда надо запихиваете этот запрос(запросы) и работаете уже с sql-таблицами как вам угодно.
_{P.S. об ошибках в топике прошу оповещать меня в личку, а не через комментарии.}

Автор: IamKarlson

Источник