How to: AD + MSSQL

в 5:39, , рубрики: active directory, ldap, 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 (меньше секунды) вываливается

Cannot fetch a row from OLE DB provider «ADSDSOObject» for linked server «ADSI».

Сразу скажу что это настраиваемое ограничение самого 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'' ')

Получаем:

Cannot get the data of the row from the OLE DB provider «ADSDSOObject» for linked server «ADSI». Could not convert the data value due to reasons other than sign mismatch or overflow.

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 провйдер ругнется и скажет что

An error occurred while preparing the query «тут наш запрос» for execution against OLE DB provider «ADSDSOObject» for linked server «ADSI».

Да, скобки искать нельзя. Сначала я думал что экранировать можно через кавычки, потом думал что через слеш, потом через амперсанд, много думал. Не получается. Ответ пришелся сам по себе: я этого же пользователя попробовал найти в 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

Источник

  1. msdmo:

    Доброго времени суток.
    А как построить запрос, что бы искать пользователей по части слова в описании?
    Например в свойствах учетной записи в описании написано “Начальник какого-то отдела” и мне через запрос со всей АД надо получить всех пользователей, у которых в описании есть слово “начальник”.

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js