Технический справочник по универсальному соединителю SQL

В этой статье описывается универсальный соединитель SQL. Статья относится к следующим продуктам:

• Microsoft Identity Manager 2016 (MIM2016);
• Microsoft Entra ID

Для MIM2016 соединитель доступен в качестве скачивания из Центра загрузки Майкрософт.

Чтобы увидеть, как работает этот соединитель, см. раздел Универсальный соединитель SQL: пошаговое руководство.

Общие сведения об универсальном соединителе SQL

Универсальный соединитель SQL позволяет интегрировать службу синхронизации с системой базы данных, предоставляющей подключение ODBC.

На системном уровне текущий выпуск соединителя поддерживает следующие функции.

Необходимые компоненты

Прежде чем использовать соединитель, установите на сервере синхронизации такие компоненты:

• Microsoft .NET 4.6.2 Framework или более поздней версии
• 64-разрядные клиентские драйверы ODBC.
• Если вы используете соединитель для взаимодействия с Oracle 12c, для этого требуется Oracle Instant Client 12.2.0.1 или более поздней версии с пакетом ODBC.
• Если соединитель используется для взаимодействия с Oracle 18c-23c, для этого требуется Oracle Instant Client 18-23 или более поздней версии с пакетом ODBC, а также системная переменная NLS_LANG для поддержки символов UTF8, например NLS_LANG=AMERICAN_AMERICA. AL32UTF8.
• Этот соединитель использует подготовленные инструкции SQL и несколько инструкций для каждой транзакции. Некоторые системы RDBM могут иметь проблемы в своих драйверах ODBC, связанных с обработкой транзакций, подготовленными на стороне сервера инструкции SQL и несколькими операторами в одной транзакции. Настройте параметры подключения DSN соответствующим образом, чтобы убедиться, что эти инструкции правильно отправляются в базу данных.Например, Драйвер ODBC MySQL версии 8.0.32 требует параметров NO_SSPS=1 и MULTI_STATEMENTS=1. Другие параметры, такие как autocommit или "фиксация только для успешных операций", могут повлиять на обработку экспорта пакетов; Дополнительные сведения см. в администраторе базы данных. Чтобы устранить неполадки во время экспорта, задайте размер пакета экспорта равным 1 и включите подробное ведение журнала соединителя.

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

Разрешения в подключенном источнике данных

Для создания или выполнения всех задач, поддерживаемых универсальным соединителем SQL, необходимо следующее:

• db_datareader
• db_datawriter

Порты и протоколы

Сведения о портах, необходимых для работы драйвера ODBC, см. в документации поставщика базы данных.

Создание нового соединителя

Чтобы создать универсальный соединитель SQL, в службе синхронизации последовательно выберите элементы Агент управления и Создать. Выберите Универсальный соединитель SQL (Microsoft) .

Подключение

Для подключения соединитель использует DSN-файл ODBC. Создайте DSN-файл, последовательно выбрав в меню «Пуск» пункты Администрирование и Источники данных ODBC. С помощью средства администрирования создайте DSN-файл , который будет использован соединителем.

При создании универсального соединителя SQL первым экраном будет экран настройки подключения. Вам потребуется указать следующие сведения:

• Путь к DSN-файлу
• Аутентификация
  • Имя пользователя
  • Пароль

База данных должна поддерживать один из перечисленных ниже методов проверки подлинности.

• Проверка подлинности Windows. Для проверки подлинности пользователя в базе данных используются учетные данные Windows. Указанные имя пользователя и пароль используются для проверки подлинности в базе данных. Этой учетной записи потребуются разрешения для доступа к базе данных.
• Проверка подлинности SQL. Для подключения к базе данных используются имя пользователя и пароль, указанные на экране настройки подключения к базе данных. Если имя пользователя и пароль хранятся в DSN-файле, приоритет имеют учетные данные, указанные на экране настройки подключения.
• База данных SQL Azure аутентификации. Дополнительные сведения см. в статье "Подключение к База данных SQL через проверку подлинности Microsoft Entra".

Различаемое имя служит привязкой. Если выбрать этот параметр, различаемое имя будет также использоваться в качестве атрибута привязки. Этот подход можно использовать для простой реализации, но он имеет следующие ограничения:

• Соединитель поддерживает только один тип объекта. Поэтому все ссылочные атрибуты смогут указывать только на тот же тип объекта.

Тип экспорта: замена объектов. Экспорт объекта выполняется целиком, с заменой имеющегося объекта, даже если были изменены только некоторые атрибуты.

Схема 1 (определение типов объектов)

На этой странице настраивается способ определения соединителем различных типов объектов в базе данных.

Каждый тип объектов представляется в виде раздела и может быть настроен впоследствии на странице Configure Partitions and Hierarchies(Настройка разделов и иерархий).

Метод обнаружения типа объекта. Соединитель поддерживает следующие методы определения типа объектов.

• Фиксированное значение. Объекты указываются в виде списка, разделенного запятыми. Например: User,Group,Department.
  
• Таблица, представление или хранимая процедура. Указывается имя таблицы, представления или хранимой процедуры, а затем имя столбца, в котором содержится список типов объектов. Если вы используете хранимую процедуру, нужно будет указать ее параметры в следующем формате: [имя]:[направление]:[значение]. Все параметры указываются в отдельных строках (чтобы создать новую строку, нажмите CTRL+ВВОД).
  
• SQL-запрос. Этот параметр позволяет указать SQL-запрос, который возвращает один столбец с типами объектов. Например, SELECT [Column Name] FROM TABLENAME. Возвращаемый столбец должен иметь строковый тип (varchar).

Схема 2 (определение типов атрибутов)

На этой странице настраивается способ определения имен и типов атрибутов. Для всех типов объектов, указанных на предыдущей странице, указываются параметры конфигурации.

Метод обнаружения типа атрибута. Соединитель поддерживает следующие методы обнаружения типов атрибутов для всех типов объектов, определенных на экране «Схема 1».

• Таблица, представление или хранимая процедура. Укажите имя таблицы, представления или хранимой процедуры, используемой для поиска имен атрибутов. Если вы используете хранимую процедуру, нужно будет указать ее параметры в следующем формате: [имя]:[направление]:[значение]. Все параметры указываются в отдельных строках (чтобы создать новую строку, нажмите CTRL+ВВОД). Чтобы определить имена атрибутов в многозначном атрибуте, укажите список таблиц или представлений, разделенных запятой. Многозначные атрибуты не поддерживаются, если имена столбцов в родительской и дочерней таблицах совпадают.
• SQL-запрос. Этот параметр позволяет указать SQL-запрос, который возвращает один столбец с именами атрибутов. Например, SELECT [Column Name] FROM TABLENAME. Возвращаемый столбец должен иметь строковый тип (varchar).

Схема 3 (определение привязки и различаемого имени)

На этой странице для каждого определенного типа объекта можно настроить привязку и атрибут различаемого имени. Можно выбрать несколько атрибутов, чтобы сделать привязку уникальной.

• Многозначные и логические атрибуты в список не включаются.

• Один атрибут нельзя одновременно использовать для различаемого имени и привязки, если только на странице настройки подключения не выбран параметр Различаемое имя служит привязкой .

• Если на странице настройки подключения выбран параметр Различаемое имя служит привязкой , то на этой странице потребуется указать только атрибут различаемого имени. Этот атрибут также будет использоваться в качестве атрибута привязки.

  

Схема 4 (определение типа атрибута, ссылки и направления)

На этой странице можно настроить тип (целое число, двоичное значение или логическое значение) и направление для каждого атрибута. Здесь приводятся все атрибуты со страницы Схема 2 , включая многозначные атрибуты.

• DataType. Используется для сопоставления типа атрибута с типом, известным для модуля синхронизации. По умолчанию используется тот же тип, который определен в схеме SQL, но типы DateTime и Reference не определяются автоматически. Для этих типов необходимо указать DateTime или Reference.
• Направление. В качестве направления атрибута можно выбрать Import, Export или ImportExport. Значением по умолчанию является ImportExport.

Примечания:

• Если соединителю не удалось определить тип атрибута, будет использоваться тип данных String.
• Вложенные таблицы можно считать таблицами с одним столбцом. В Oracle строки вложенной таблицы хранятся в произвольном порядке. Тем не менее при извлечении вложенной таблицы в переменную PL/SQL строкам последовательно присваиваются подстрочные знаки (начиная с 1). В результате строками можно оперировать как массивом.
• VARRYS не поддерживаются в этом соединителе.

Схема 5 (определение разделов для ссылочных атрибутов)

На этой странице для всех ссылочных атрибутов указываются разделы (типы объектов), на которые ссылается атрибут.

Если выбран параметр DN is anchor(Различаемое имя служит привязкой), то нужно использовать тип объекта, используемого как источник ссылки. Ссылаться на объект другого типа нельзя.

Если выбрано значение "*", необходимо также указать имя столбца с типом объекта.

После импорта вы увидите нечто похожее:

Глобальные параметры

Страница «Глобальные параметры» используется для настройки импорта изменений, формата даты и времени и метода проверки пароля.

Универсальный соединитель SQL поддерживает следующие способы импорта изменений.

• Триггер. См. статью Generating Delta Views Using Triggers (Создание представлений изменений с помощью триггеров).
• Водяной знак. Это общий подход, который может использоваться с любой базой данных. Запрос водяного знака предварительно заполнен и зависит от поставщика базы данных. В каждой используемой таблице или представлении должен присутствовать столбец для водяного знака. Он нужен для отслеживания операций вставки и изменения в таблице, а также в зависимых (многозначных или дочерних) таблицах. Требуется синхронизация времени между службой синхронизации и сервером базы данных. В противном случае некоторые записи при импорте изменений могут быть опущены.
  Ограничение:
  • Функция водяных знаков не поддерживает удаленные объекты.
• Моментальный снимок (работает только с Microsoft SQL Server). См. статью о создании представлений изменений с использованием моментальных снимков.
• Отслеживание изменений (работает только с Microsoft SQL Server). См. статью Об отслеживании изменений (SQL Server).
  Ограничения:
  • Атрибуты привязки и различаемого имени должны быть частью первичного ключа для выбранного объекта в таблице.
  • Во время импорта и экспорта данных с использованием функции отслеживания изменений SQL-запросы не поддерживаются.

Дополнительные параметры. Указывается часовой пояс сервера базы данных, определяющий расположение сервера. Это значение используется для поддержки различных атрибутов формата даты и времени.

Соединитель всегда хранит значения даты и времени в формате UTC. Чтобы операции преобразования даты и времени работали правильно, необходимо указать часовой пояс и формат сервера базы данных. Формат должен быть выражен в формате .NET.

Во время экспорта все атрибуты времени и даты, передаваемые соединителю, указываются в формате времени UTC.

Конфигурация паролей. Соединитель предоставляет возможности синхронизации паролей и поддерживает установку и изменение паролей.

Для синхронизации паролей в соединителе имеется два метода.

• Хранимая процедура. Для установки и изменения пароля требуются две соответствующие хранимые процедуры. В полях Set Password SP (Параметры хранимой процедуры установки пароля) и Change Password SP (Параметры хранимой процедуры изменения пароля) укажите параметры, как в следующем примере.
• Расширение пароля. Для этого метода требуется библиотека DLL расширения паролей (необходимо указать имя библиотеки DLL расширения, которая реализует интерфейс IMAExtensible2Password). Сборка с расширением паролей должна быть помещена в папку с расширениями, чтобы соединитель мог загрузить библиотеку DLL в среде выполнения.

Также на странице Настройка расширений потребуется включить управление паролями.

Настройка разделов и иерархий

На странице разделов и иерархий выберите все типы объектов. Каждый тип помещается в отдельный раздел.

Здесь также можно переопределить значения, указанные на страницах Подключение или Глобальные параметры.

Настройка привязок

Эта страница доступна только для чтения, так как привязки к этому моменту уже определены. К выбранному атрибуту привязки всегда добавляется тип объекта, что обеспечивает его уникальность для различных типов объектов.

Настройка параметров шагов выполнения

Шаги настраиваются в профилях выполнения соединителя. Эти настройки относятся к фактическим операциям импорта и экспорта данных.

Полный импорт и импорт изменений

Универсальный соединитель SQL поддерживает следующие методы полного импорта и импорта изменений.

• Таблица
• Представления
• Хранимая процедура
• Запросы SQL

Таблица или представление
Для импорта многозначных атрибутов объекта в поле имени многозначных таблиц или представлений потребуется указать список таблиц или представлений. Соответствующие условия объединения с родительской таблицей указываются в поле Условие объединения. Если в источнике данных содержится несколько таблиц с несколькими значениями, можно использовать объединение в одно представление.

Пример. Нужно импортировать объект Employee и его многозначные атрибуты. Существует две таблицы: Employee (главная таблица) и Department (многозначная таблица). Выполните следующие действия.

• В поле Таблица/представление/хранимая процедура введите Employee.
• В поле Имена многозначных таблиц или представленийвведите Department.
• Введите условие объединения сотрудников и отделов в поле Условие объединения. Например, Employee.DEPTID=Department.DepartmentID.

Хранимые процедуры

• Для больших объемов данных рекомендуется реализовать разбиение на страницы с помощью хранимых процедур.
• Чтобы хранимые процедуры поддерживали разбиение на страницы, потребуется указать начальный и конечный индексы. См. статью Efficiently Paging Through Large Amounts of Data (Эффективное разбиение больших объемов данных).
• @StartIndex и @EndIndex во время выполнения заменяются соответствующими значениями размера страницы, которые настраиваются на странице Configure Step (Настройка шага). Например, если соединитель получает первую страницу, а размер страницы равен 500, то @StartIndex примет значение 1, а @EndIndex — 500. При получении соединителем следующих страниц и изменении @StartIndex и @EndIndex эти значения будут соответствующим образом увеличиваться.
• Для выполнения параметризованной хранимой процедуры укажите параметры в формате [Name]:[Direction]:[Value] . Все параметры указываются в отдельных строках (чтобы создать новую строку, нажмите CTRL+ВВОД).
• Универсальный соединитель SQL также поддерживает операции импорта из связанных серверов на сервере Microsoft SQL Server. Если сведения следует получить из таблицы на связанном сервере, то имя таблицы должно быть указано в формате [ServerName].[Database].[Schema].[TableName]
• Универсальный соединитель SQL поддерживает только те объекты, структура (имя псевдонима и тип данных) которых в шагах выполнения и в схеме совпадает. Если структура объекта, полученная из схемы и указанная в сведениях о шагах выполнения, отличается, такой объект не будет обработан соединителем SQL.

Запросы SQL

Пример SQL-запроса с разбивкой на страницы — неправильный запрос не будет работать, так как используется новый символ строки:

WITH A AS 
  (select dense_rank() over (order by BusinessEntityID) 
    rownumber, BusinessEntityID, DeptID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, ModifiedDate, Password 
    from Employees
  ) select * from A where rownumber between @StartIndex and @EndIndex

Пример SQL-запроса с разбивкой на страницы — правильный запрос:

WITH A AS (select dense_rank() over (order by BusinessEntityID) rownumber, BusinessEntityID, DeptID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, ModifiedDate, Password from Employees) select * from A where rownumber between @StartIndex and @EndIndex

• Запросы с несколькими результирующими наборами не поддерживаются.
• SQL-запрос поддерживает разбиение на страницы и предоставляет начальный индекс и конечный индекс в качестве переменной для поддержки разбиения на страницы.

импорт изменений;

Для конфигурации импорта изменений требуются некоторые дополнительные настройки по сравнению с полным импортом.

• Если для отслеживания изменений выбран метод "Триггер" или "Моментальный снимок", то в поле History Table or Snapshot database name (Таблица журнала или имя базы данных моментальных снимков) вы сможете указать таблицу журнала или базу данных моментальных снимков.
• Нужно также указать условие объединения таблицы журнала и родительской таблицы. Например, Employee.ID=History.EmployeeID.
• Чтобы отслеживать транзакции в родительской таблице с помощью таблицы журнала, нужно указать имя столбца со сведениями об операциях (добавление, обновление и удаление).
• Если для отслеживания изменений выбран метод "Водяной знак", в поле Water Mark Column Name(Имя столбца водяного знака) необходимо указать имя столбца со сведениями об операциях.
• Столбец Изменить атрибут типа нужен для изменения типа. Этот столбец позволяет сопоставить изменение в основной или многозначной таблице с типом изменения в представлении изменений. В этом столбце может быть указан тип изменений Modify_Attribute для изменений на уровне атрибута, либо Add, Modify или Delete для изменений на уровне объекта. Если значение отличается от значения по умолчанию (добавление, изменение или удаление), то новые значения можно определить с помощью этого параметра.

Экспорт (Export)

Универсальный соединитель SQL поддерживает четыре метода экспорта.

• Таблица
• Представления
• Хранимая процедура
• Запросы SQL

Таблица или представление
Если выбрать параметр "Таблица или представление", то соединитель создает соответствующие запросы на экспорт.

Хранимые процедуры

Если выбрать параметр "Хранимые процедуры", то для экспорта потребуется три разных хранимых процедуры, чтобы выполнить операции вставки, обновления или удаления.

• Add SP Name (Имя хранимой процедуры добавления). Эта хранимая процедура выполняется при поступлении объекта в соединитель для вставки в соответствующую таблицу.
• Update SP Name (Имя хранимой процедуры обновления). Эта хранимая процедура выполняется при поступлении объекта в соединитель для обновления в соответствующей таблице.
• Delete SP Name (Имя хранимой процедуры удаления). Эта хранимая процедура выполняется при поступлении объекта в соединитель для удаления из соответствующей таблицы.
• В качестве параметра для хранимой процедуры используется атрибут, выбранный из схемы. Например, @EmployeeName: INPUT: EmployeeName. EmployeeName выбирается в схеме соединителя, и соединитель заменяет соответствующее значение при выполнении экспорта.
• Для выполнения параметризованной хранимой процедуры укажите параметры в формате [Name]:[Direction]:[Value]. Все параметры указываются в отдельных строках (чтобы создать новую строку, нажмите CTRL+ВВОД).

SQL-запрос

Если выбрать параметр "SQL-запрос", то для экспорта потребуется три разных запроса для выполнения операций вставки, обновления или удаления.

• Insert Query (Запрос на вставку). Этот запрос выполняется при поступлении объекта в соединитель для вставки в соответствующую таблицу.
• Update Query (Запрос на обновление). Этот запрос выполняется при поступлении объекта в соединитель для обновления в соответствующей таблице.
• Delete Query (Запрос на удаление). Этот запрос выполняется при поступлении объекта в соединитель для удаления из соответствующей таблицы.
• В качестве значения параметра для запроса используется атрибут, выбранный из схемы. Например, Insert into Employee (ID, Name) Values (@ID, @EmployeeName).

Пример многофакторного обновления SQL-запроса. Новый символ строки используется для разделения инструкций SQL:

update Employee set jobTitle=@JOBTITLE where BusinessEntityID=@BUSINESSENTITYID
insert into ChangeLog VALUES (@BUSINESSENTITYID)

Устранение неполадок

• Сведения о том, как включить ведение журнала для устранения неполадок соединителя, см. в статье How to Enable ETW Tracing for Connectors (Включение трассировки событий Windows для соединителей).