An error occurred while preparing the query ошибка

I’m having some trouble configuring a SQL statement to perform an openquery on LDAP from SQL Server. I followed the instructions as laid out here: Querying Active Directory from SQL Server 2005 but I’m having some trouble putting the final pieces together.

Firstly, I didn’t know where my LDAP server was. So I did a nslookup and found the default server as:

abc.domain.popo.local

I configured my OPENQUERY as

SELECT * FROM OPENQUERY( ADSI, 'SELECT * FROM ''LDAP://DC=abc,DC=domain,DC=popo,DC=local'' WHERE
objectCategory = ''User''')   

However, I get an error saying that

An error occurred while preparing the query «SELECT * FROM ‘LDAP://DC=abc,DC=domain,DC=popo,DC=local’ WHERE objectCategory = ‘User'» for execution against OLE DB provider «ADSDSOObject» for linked server «ADSI».

What is the likely issue here? Am I setting up the DC incorrectly (because I don’t even know what DC means)? Or is it more likely that I just have the wrong server altogether for LDAP?

I have been trying to configure a linked server to AD and have found plenty of write ups on how to do it, but have had zero luck with getting it to work.  I also read that the ability was removed after SQL 2008; is that correct?  I am running 2014
and am seeing the following error trying to expand the tree:

Then the following error when trying to query:

SELECT * FROM OpenQuery(ADSI, ‘SELECT displayName FROM »LDAP://<DOMAIN>/DC=DOMAIN,DC=<DOMAIN>» WHERE objectCategory=»User» ‘)

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query «SELECT displayName FROM ‘LDAP://—-/DC=—-,DC=—-‘ WHERE objectCategory=’User’ » for execution against OLE DB provider «ADSDSOObject» for linked server «ADSI». 

The linked server is set for ‘Be made by the login’s current security context’ and local to remote is configured SA to a domain account we use for lookups.  

It does not seem to matter if I am logged in to the server with SQL or Windows credentials.  Thank you in advance for any guidance!

-Drew

Время на прочтение
5 мин

Количество просмотров 33K

Доброго дня. Решил что опыт обработки данных AD посредством SQL будет полезен не только мне, тем более внятного мануала на русском я так и не нашел.

_{За стиль изложения прошу сильно не бить, старался объяснить как можно доступнее, как сам хотел увидеть когда искал инфу.}

Собственно задача: «А сделайте нам отчет по всем пользователям компании, да так чтобы и группы его выводились». Не знаю насколько это реально какими то нативными средствами администрирования AD, но первая идея пришедшая в голову написать запрос к AD и вытянуть с него всю инфу которую только можно в какую нить табличку и дальше уже обрабатывать чем душе угодно, будь то reporting service или что нить в духе crystal reports. Как же достучаться до AD?

Оказывается у MSSQL есть нативный OLE DB провайдер для подключения AD. Добавляем наш linked server в server objects:

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 это сделать не скажу, ибо не знаю}

После того как мы создали сервер необходимо добавить к нему логин:

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

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

Настройки генерируемые sql-сервером:

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'

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

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

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

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 это не настраивается. Как это настраивается можем посмотреть здесь.
Можем пойти двумя путями: попросить админов перенастроить или обойти это ограничение. Конечная цель получить всех пользователей.
Сузим круг поиска:

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:

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

Если есть пользователи начинающиеся на abc, то он вам что нибудь вернет:

sAMAccountName cn
abceeeee abceeeee dfdf eee

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

Просим:

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 нашего искомого пользователя

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(юми китайский департамент)». Ну странный пользователь, не спорю. Но английское имя, а в скобках русское, вполне себе нормально. Ладно, не суть.
Запрос:

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. Чтоб не пришлось вам лишний раз сидеть ковырять сколько апострофов поставить вот:

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

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

_{P.S. об ошибках в топике прошу оповещать меня в личку, а не через комментарии.}