Запрос информации домена

Используя функцию openrowset или openquery, можно запрашивать информацию из различных источников данных. Попробуем получить доменную информацию с помощью запроса к LDAP. Чтобы не создавать связанный сервер будем использовать функцию openrowset. Первым параметром этой функции является имя поставщика данных. Список установленных провайдеров можно посмотреть, вызвав расширенную хранимую процедуру xp_enum_oledb_providers. Для запроса доменной информации функции openrowset требуется передать запрос к домену, используя специальный синтаксис. Например, если Вы работаете в компании MyCompany, в России, то, скорее всего, можно написать такой запрос:
select top 10 givenName [Имя], sn [Фамилия],
from openrowset
(   'ADSDSOObject',
    'LDAP://MyCompany/DC=MyCompany,DC=ru;',
    '
        select givenName, sn
        from ''LDAP://DC=MyCompany,DC=ru''
        where objectClass = ''Person''
    ')

Через этот запрос можно извлекать самую разнообразную информацию о пользователях, или например, получать список пользователей определенной группы. Например, ниже представлена расширенная версия:
select top 100
    sAMAccountName [Учетная запись],
    givenName [Имя],
    sn [Фамилия],
    displayName [Полное имя],
    initials [Инициалы],
    replicate ( '|', 20 ) [ ],
    company [Компания],
    title [Должность],
    department [Подразделение],
    division [Департамент],
    physicalDeliveryOfficeName [Офис],
    wWWHomePage [Сайт],
    replicate ( '|', 20 ) [ ],
    telephoneNumber [Телефон],
    pager [Пейджер],
    mail [Электронный адрес],
    mobile [Мобильный телефон],
    homePhone [Домашний телефон],
    streetAddress [Адрес],
    postOfficeBox [Почтовая ячейка],
    l [Город],
    st [Область],
    co [Страна],
    c [Буквенный код страны],
    countryCode [Числовой код страны],
    replicate ( '|', 20 ) [ ],
    userWorkstations [Рабочая станция],
    homeDrive [Диск],
    homeDirectory [Директория],
    mailNickName [Имя в электронном адресе],
    manager [Менеджер],
    distinguishedName,
    case when cast ( accountExpires as varchar ( 100 ) ) in
        ( '0', '9223372036854775807' ) then 'Пароль не истекает'
        else convert ( varchar ( 100 ), dateadd ( [dd],
            cast ( accountExpires / ( 1000000 * 60. * 60. * 24. ) as int ),
            cast ( '1601-01-02' as datetime2 ) ), 103 ) end [Истечение пароля],
    case when msExchUserAccountControl = 0 then 'Учетная запись активна'
        when msExchUserAccountControl = 2 then 'Учетная запись заблокирована' else null
        end [Доступ],
    case when userAccountControl & 8388608 <> 0 then 'Пароль истек'
        else 'Пароль НЕ истек' end [Срок пароля],
    case when userAccountControl & 32 <> 0 then 'Пароль НЕ требуется'
        else 'Пароль требуется' end [Наличие пароля]
from openrowset
(    'ADSDSOObject',
    'LDAP://MyCompany/DC=MyCompany,DC=ru;',
    '
        select
            distinguishedName,
            co,
            title,
            accountExpires,
            company,
            streetAddress,
            postOfficeBox,
            l,
            st,
            c,
            countryCode,
            wWWHomePage,
            initials,
            givenName,
            pager,
            homePhone,
            userWorkstations,
            homeDrive,
            homeDirectory,
            mailNickName,
            manager,
            sn,
            sAMAccountName,
            displayName,
            mail,
            telephoneNumber,
            mobile,
            physicalDeliveryOfficeName,
            department,
            division,
            msExchUserAccountControl,
            userAccountControl
        from ''LDAP://DC=MyCompany,DC=ru''
        where objectClass = ''Person''
    ')
В этом запросе извлекается личная информация о сотрудниках компании, в частности, имя, фамилия, данные о должности, подразделении, адресах, телефонах.
Помимо персональных данных, извлекаются данные о правах доступа пользователя. Например, поле userAccountControl представляет собой битовую маску. Проверяя наличие определенных битов, в запросе проверяется обязателен ли для учетной записи пароль, истек ли срок действия пароля. Поле msExchUserAccountControl используется для проверки блокировки учетной записи. Поле AccountExpires представляет собой количество микросекунд с 02.01.1602 до момента истечения срока действия пароля, поэтому дата истечения пароля определяется в запросе с помощью этого поля и функции dateadd. Также определенные значения AccountExpires свидетествуют о том, что у учетной записи пароль никогда не истекает.


В запросе используется top для извлечения не всех записей, а только их части. Это связано с тем, что у контроллера домена имеется свойство MaxValRange, которое влияет на то, какое количество записей может быть возвращено с помощью одного запроса. Если попытаться вернуть запросом большее число записей, то возникнет ошибка.


Можно получать данные LDAP, используя и другой подход: ADO. В этом случае ограничение MaxValRange не помешает получить все записи. Требуется создавать соединение и действовать, используя стандартные методы: задать строку подключения, передать в ADO-команду текст LDAP-запроса и вызвать метод execute. Ниже реализация этого плана в хранимой процедуре dbo.GetLDAPInfo:


if sessionproperty ( N'quoted_identifier' ) = 0
       set quoted_identifier on
go

if sessionproperty ( N'ansi_nulls' ) = 0

       set ansi_nulls on
go

if object_id ( N'dbo.GetLDAPInfo', N'P' ) is null

       exec ( N'create proc dbo.GetLDAPInfo as return 1' )
go

alter proc dbo.GetLDAPInfo

as
begin
    declare @res int, @conn int, @cmd int, @prop int, @propInst int, @propSearch int, @propAsync int, @ADOrs int
    declare @ErrMsg nvarchar ( max ) = N'', @step nvarchar ( 1000 ), @src varchar ( 300 ), @desc varchar ( 300 ), @curhan int

    begin try

        set @step = 'создание соединения'
        exec @res = sp_OACreate 'ADODB.Connection', @conn out
        set @curhan = @conn
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'задание провайдера'
        exec @res = sp_OASETProperty @conn, 'Provider', 'ADsDSOObject'
        set @curhan = @conn
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'открытие соединения'
        exec @res = sp_OAMethod @conn, 'Open'
        set @curhan = @conn
        if @res <> 0 raiserror ( @step, 16, 1 )
       
        set @step = 'создание команды'
        exec @res = sp_OACreate 'ADODB.Command', @cmd out
        set @curhan = @cmd
        if @res <> 0 raiserror ( @step, 16, 1 )
       
        set @step = 'соединение для команды'
        exec @res = sp_OASETProperty @cmd, 'ActiveConnection', @conn
        set @curhan = @cmd
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'коллекция свойств команды'
        exec @res = sp_OAGetProperty @cmd, 'Properties', @prop out
        set @curhan = @cmd
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'свойство page size'
        exec @res = sp_OAMethod @prop, 'Item', @propInst out, 'Page Size'
        set @curhan = @prop
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'установка page size'
        exec @res = sp_OASETProperty @propInst, 'Value', '1000'
        set @curhan = @propInst
        if @res <> 0 raiserror ( @step, 16, 1 )
       
        set @step = 'свойство SearchScope'
        exec @res = sp_OAMethod @prop, 'Item', @propSearch out, 'SearchScope'
        set @curhan = @prop
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'установка SearchScope'
        exec @res = sp_OASETProperty @propSearch, 'Value', '2'
        set @curhan = @propSearch
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'свойство Asynchronous'
        exec @res = sp_OAMethod @prop, 'Item', @propAsync out, 'Asynchronous'
        set @curhan = @prop
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'установка Asynchronous'
        exec @res = sp_OASETProperty @propAsync, 'Value', 1
        set @curhan = @propAsync
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'создание набора записей'
        exec @res = sp_OACreate 'ADODB.Recordset', @ADOrs out
        set @curhan = @ADOrs
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'текст запроса'
        exec @res = sp_OASETProperty @cmd, 'CommandText', 'select distinguishedName, co, title from ''LDAP://DC=MyCompany,DC=ru'' where objectClass = ''Person'''
        set @curhan = @cmd
        if @res <> 0 raiserror ( @step, 16, 1 )


        set @step = 'выполнение запроса'
        exec @res = sp_OAMethod @cmd, 'Execute', @ADOrs out
        set @curhan = @cmd
        if @res <> 0 raiserror ( @step, 16, 1 )
       
        set @step = 'получение результата'
        exec @res = sp_OAMethod @ADOrs, 'getrows'
        set @curhan = @ADOrs
        if @res <> 0 raiserror ( @step, 16, 1 )
    end try
    begin catch
        if error_message () = @step
        begin
            exec sp_OAGetErrorInfo @curhan, @src out, @desc out
            set @ErrMsg = N'Ошибка OLE; статус: ' + isnull ( @step, N'неизвестно' ) +
                '; код: ' + isnull ( cast ( @res as nvarchar ( 100 ) ), N'неизвестно' ) +
                N'; источник: ' + isnull ( @src, N'неизвестно' ) + N'; описание: ' +
                isnull ( @desc, N'неизвестно' ) + N'.'
        end
        else

        begin

            set @ErrMsg = N'Ошибка SQL: номер: ' + isnull ( cast ( error_number () as nvarchar ( 100 ) ),
                N'неизвестно' ) + N'; описание: ' + isnull ( error_message (),
                N'неизветсно' ) + N'; строка: ' +
                isnull ( cast ( error_line () as nvarchar ( 100 ) ), N'неизвестно' )
        end
    end catch

    exec sp_OADestroy @conn
    exec sp_OADestroy @cmd
    exec sp_OADestroy @prop
    exec sp_OADestroy @propInst
    exec sp_OADestroy @propSearch
    exec sp_OADestroy @propAsync
    exec sp_OADestroy @ADOrs
 
    if isnull ( @ErrMsg, N'' ) <> N''
    begin
        raiserror ( @ErrMsg, 16, 1 )
    end
end
Метод GetRows вызывается без указание возвращаемого значения, поэтому вызов sp_OAMethod сразу выдает набор всех записей, подпадающих под условие запроса LDAP. Сохранить эти записи в таблице можно, используя инструкцию insert into ... exec ... Чтобы не загромождать текст, я поместил в запрос LDAP только 3 поля, при необходимости можно добавить свои поля (надо только чтобы 2 одинаковых столбца не встречались в одном запросе, поскольку это приведет к ошибке).