Создание связанных серверов (ядро СУБД SQL Server)

  • Статья

Область применения: SQL Server Управляемый экземпляр SQL Azure

В этой статье показано, как создать связанный сервер и получить доступ к данным из другого SQL Server, Управляемый экземпляр SQL Azure или другого источника данных с помощью SQL Server Management Studio (SSMS) или Transact-SQL. Связанные серверы позволяют ядру СУБД SQL Server и управляемым экземплярам SQL Azure выполнять чтение данных из удаленных источников данных и выполнять команды на удаленных серверах баз данных (например, источниках данных OLE DB) за пределами экземпляра SQL Server.

Общие сведения

Связанные серверы настроены на включение ядра СУБД для выполнения инструкции Transact-SQL, включающей таблицы в другом экземпляре SQL Server или другом продукте для работы с базами данных, например Oracle. В качестве связанных серверов можно настроить источники данных многих типов, в том числе сторонних поставщиков баз данных и Azure Cosmos DB.

После создания связанного сервера можно выполнять распределенные запросы к этому серверу, причем в запросах могут соединять таблицы из нескольких источников данных. Если связанный сервер определен как экземпляр SQL Server или Управляемый экземпляр SQL Azure, можно выполнить удаленные хранимые процедуры.

Возможности связанного сервера и необходимые аргументы могут сильно различаться. Примеры в этой статье содержат типичный пример, но все параметры не описаны. Дополнительные сведения см. в статье sp_addlinkedserver (Transact-SQL).

Разрешения

При использовании инструкций Transact-SQL требуется разрешение ALTER ANY LINKED SERVER на сервере или членство в предопределенных ролях сервера setupadmin . При использовании Management Studio требуется разрешение CONTROL SERVER или членство в предопределенных ролях сервера sysadmin .

Создание связанного сервера с помощью SSMS

Порядок создания связанного сервера с помощью SSMS:

Открытие диалогового окна "Создание связанного сервера"

В СРЕДЕ SQL Server Management Studio (SSMS):

  1. Открыть обозреватель объектов.
  2. Разверните узел Объекты сервера.
  3. Щелкните правой кнопкой мыши пункт Связанные серверы.
  4. Выберите Создать связанный сервер.

Изменение свойств связанного сервера на странице "Общие"

На странице "Общие" в поле связанного сервера введите имя экземпляра SQL Server, с которым вы связываетесь.

Примечание.

Если экземпляр SQL Server является экземпляром по умолчанию, то введите имя компьютера, на котором размещается экземпляр SQL Server. Если экземпляр SQL Server является именованным, введите имя компьютера и имя экземпляра, например Accounting\SQLExpress.

При необходимости укажите Тип сервера и связанные сведения:

  • SQL Server
    Определите связанный сервер как экземпляр Microsoft SQL Server или Управляемый экземпляр SQL Azure. При использовании этого метода определения связанного сервера имя, указанное в поле Связанный сервер, должно быть сетевым именем этого сервера. Кроме того, все таблицы, полученные от сервера, будут получены из базы данных, по умолчанию определенной для имени входа на связанный сервер.

  • Другой источник данных
    Укажите тип сервера OLE DB, отличный от SQL Server. Включение этой функции активирует дополнительные параметры, расположенные под ней.

    • Поставщик
      Выберите источник данных OLE DB в окне списка. Поставщик OLE DB зарегистрирован в реестре с данным идентификатором PROGID.

    • Наименование продукта:
      Введите название продукта для источника данных OLE DB, который добавляется в качестве связанного сервера.

    • Источник данных
      Введите имя источника данных согласно интерпретации поставщика OLE DB. Если вы подключаетесь к экземпляру SQL Server, укажите имя экземпляра.

    • Строка поставщика
      Введите уникальный программный идентификатор (PROGID) поставщика OLE DB, соответствующий источнику данных. Примеры допустимых строк поставщика см. в sp_addlinkedserver (Transact-SQL).

    • Местонахождение
      Введите местонахождение базы данных, понятное поставщику OLE DB.

    • Каталог
      Введите имя каталога, который следует использовать при соединении с поставщиком OLE DB.

Изменение свойств связанного сервера на странице "Безопасность"

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

Добавление сопоставлений имен входа

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

В разделе Сопоставление имен входа локального сервера с именами входа удаленного сервера повторите следующую процедуру для каждого имени входа, которое необходимо сопоставить:

  1. Выберите Добавить.

  2. Укажите Локальное имя входа.

    Указывает локальное имя входа, с помощью которого может осуществляться соединение со связанным сервером. Локальное имя входа может быть именем входа с помощью проверки подлинности SQL Server или проверка подлинности Windows входа. Использование группы Windows или пользователя автономной базы данных не поддерживается. Используйте этот список для разрешения соединений только определенным именам входа или для разрешения некоторым именам входа подключаться в качестве другого имени входа.

    Примечание.

    Распространенные проблемы с связанными серверами с помощью проверка подлинности Windows в удаленный экземпляр SQL Server возникают из проблем с именами субъектов-служб (SPN). Дополнительные сведения см. в статье Поддержка имени субъекта-службы в клиентских соединениях. Диспетчер конфигурации Microsoft Kerberos для SQL Server — это диагностическое средство, которое помогает устранять неполадки Kerberos, связанные с проблемами подключения при использовании SQL Server. Дополнительные сведения см. в разделе Диспетчер конфигурации Microsoft Kerberos для SQL Server.

  3. Выберите Олицетворение (необязательно).

    Передает имя пользователя и пароль из локального имени входа на связанный сервер. Для проверки подлинности SQL Server имя входа с одинаковым именем и паролем должно существовать на удаленном сервере. Для имен входа Windows имя входа должно быть допустимым на связанном сервере.

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

  4. Укажите Удаленный пользователь, если олицетворение не используется.

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

  5. Укажите Удаленный пароль, если олицетворение не используется.

    • Указывает пароль удаленного пользователя.

  6. При необходимости выберите Удалить, чтобы удалить существующее локальное имя входа.

Укажите контекст безопасности по умолчанию для имен входа, отсутствующих в списке сопоставления

В среде с доменами, где пользователи соединяются с помощью имен входа доменов, лучшим вариантом часто оказывается Выполнять с использованием текущего контекста безопасности имени входа. Если пользователи соединяются с исходным экземпляром SQL Server по имени входа SQL Server , то лучшим вариантом часто оказывается С использованием этого контекста безопасностис последующим указанием необходимых учетных данных для проверки подлинности на связанном сервере.

Выберите один из следующих параметров.

  • Не выполнять
    Подключение не будет сделано для имен входа, не определенных в списке.

  • Выполняется без использования контекста безопасности
    Для имен входа, не определенных в списке, соединение будет выполняться без использования контекста безопасности.

  • Выполняется с использованием текущего контекста безопасности имени входа
    Для имен входа, не определенных в списке, соединение будет выполняться с использованием текущего контекста безопасности имени входа. Если установлено подключение к локальному серверу с использованием проверки подлинности Windows, то для подключения к удаленному серверу будут использоваться учетные данные Windows. При подключении к локальному серверу с помощью проверки подлинности SQL Server имя входа и пароль будут использоваться для подключения к удаленному серверу. В этом случае на удаленном сервере должны существовать учетные данные для входа с теми же именем и паролем.

  • Выполнять с использованием данного контекста безопасности
    Для имен входа, не определенных в списке, соединение будет выполняться при помощи имени входа и пароля, заданных в полях Удаленный вход и С паролем. Удаленное имя входа должно быть именем входа проверки подлинности SQL Server на удаленном сервере.

    Внимание

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

Изменение свойств связанного сервера на странице "Параметры связанного сервера" (необязательно)

Для просмотра и установки параметров сервера можно также открыть страницу Параметры сервера. Можно изменить любой из следующих параметров:

  • Совместимые параметры сортировки
    Влияет на выполнение распределенных запросов на связанных серверах. Если этот параметр имеет значение true, SQL Server предполагает, что все символы в связанном сервере совместимы с локальным сервером, что касается последовательности набора символов и сортировки (или порядка сортировки). Это позволяет SQL Server отправлять сравнения по символьным столбцам поставщику. Если этот параметр не задан, SQL Server всегда оценивает сравнения по столбцам символов локально.

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

  • Доступ к данным
    Разрешает и запрещает доступ распределенных запросов к связанному серверу.

  • RPC
    Включает удаленный вызов процедур (RPC) с указанного сервера.

  • RPC Out
    Включает RPC на определенный сервер.

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

    Если задано значение true, параметры сортировки удаленных столбцов используются для источников данных SQL Server, а параметры сортировки, указанные в имени сортировки, используются для источников данных, отличных от SQL Server.

    Если значение равно false, при распределенных запросах всегда будут использоваться установленные по умолчанию параметры сортировки на локальном сервере, в то время как имя параметров сортировки и параметры сортировки удаленных столбцов будут пропускаться. Значение по умолчанию — false.

  • Имя параметров сортировки
    Указывает имя сортировки, используемой удаленным источником данных, если используется удаленная сортировка true, и источник данных не является источником данных SQL Server. Имя должно быть одним из параметров сортировки, поддерживаемых SQL Server.

    Используйте этот параметр при доступе к источнику данных OLE DB, отличному от SQL Server, но параметры сортировки которого соответствуют одному из параметров сортировки SQL Server.

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

  • Время ожидания соединения
    Значение времени ожидания соединения со связанным сервером.

    Если значение равно 0, используется значение по умолчанию sp_configure параметра remote login timeout.

  • Время ожидания запроса
    Значение времени ожидания для запросов к связанному серверу, в секундах.

    Если значение равно 0, используется значение по умолчанию sp_configure параметра remote query timeout.

  • Разрешить продвижение распределенных транзакций
    Используйте этот параметр для защиты действий процедуры между серверами с помощью транзакции координатора распределенных транзакций (MS DTC). Если этот параметр имеет значение TRUE, то вызов удаленной хранимой процедуры приводит к запуску распределенной транзакции и прикрепляет к выполнению транзакции MS DTC. Дополнительные сведения см. в разделе sp_serveroption (Transact-SQL).

Сохранение связанного сервера

Нажмите ОК.

Просмотр или изменение параметров поставщика связанного сервера в SSMS

У всех поставщиков нет одинаковых параметров. Например, некоторые типы данных могут быть индексированы, а некоторые нет. Используйте это диалоговое окно, чтобы помочь SQL Server понять возможности поставщика. SQL Server устанавливает некоторые распространенные поставщики данных, однако если продукт предоставляет изменения данных, поставщик, установленный SQL Server, может не поддерживать все новейшие функции. Лучшим источником сведений о возможностях продукта, поставляющего данные, является документация по продукту.
Открытие страницы Параметры поставщиков для связанного сервера в SSMS:

  1. Открыть обозреватель объектов.
  2. Разверните узел Объекты сервера.
  3. Разверните узел Связанные серверы.
  4. Разверните узел Поставщики.
  5. Щелкните правой кнопкой мыши поставщик и выберите пункт Свойства.

Параметры поставщика:

  • Динамический параметр
    Указывает, что поставщик разрешает использовать синтаксис маркеров параметров «?» для параметризованных запросов. Установите этот параметр только в том случае, если поставщик поддерживает интерфейс ICommandWithParameters и символ «?» в качестве маркера параметров. Этот параметр позволяет SQL Server выполнять параметризованные запросы к поставщику. Возможность выполнять параметризованные запросы к поставщику может повысить производительность некоторых запросов.

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

  • Только нулевой уровень
    Для поставщика вызываются только интерфейсы OLE DB уровня 0.

  • Допускать в ходе процесса

    SQL Server позволяет поставщику создавать экземпляры в качестве внутреннего сервера. Если этот параметр не задан, поведение по умолчанию — создать экземпляр поставщика за пределами процесса SQL Server. Создание экземпляра поставщика за пределами процесса SQL Server защищает процесс SQL Server от ошибок в поставщике. Если поставщик создается за пределами процесса SQL Server, обновления или вставки, ссылающиеся на длинные столбцы (текст, ntext или изображение), не допускаются.

  • Обновления без использования транзакций
    SQL Server разрешает обновления, даже если ITransactionLocal недоступен. Если этот параметр включен, обновления для поставщика не восстанавливаются, так как поставщик не поддерживает транзакции.

  • Индекс в качестве пути доступа
    SQL Server пытается использовать индексы поставщика для получения данных. По умолчанию индексы используются только для метаданных и никогда не открываются.

  • Запретить нерегламентированный доступ
    SQL Server не разрешает нерегламентированный доступ через функции OPENROWSET и OPENDATASOURCE для поставщика OLE DB. Если этот параметр не задан, SQL Server также не разрешает нерегламентированный доступ.

  • Поддерживает оператор Like.
    Указывает, что поставщик поддерживает запросы с использованием ключевого слова LIKE.

Создание связанного сервера с помощью Transact-SQL

Чтобы создать связанный сервер с помощью Transact-SQL, используйте инструкции sp_addlinkedserver (Transact-SQL), CREATE LOGIN (Transact-SQL) и sp_addlinkedsrvlogin (Transact-SQL).

В этом примере создается связанный сервер для другого экземпляра SQL Server с помощью Transact-SQL:

  1. В Редактор запросов введите следующую команду Transact-SQL, чтобы связаться с экземпляром SQL Server с именемSRVR002\ACCTG:

    USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'SRVR002\ACCTG',   
    @srvproduct=N'SQL Server';  
GO

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

    EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'SRVR002\ACCTG',   
    @locallogin = NULL ,   
    @useself = N'True';  
GO

Дальнейшие действия после создания связанного сервера

Ниже представлен порядок выполнения проверки связанного сервера.

Проверка связанного сервера

Для проверки подлинности связанного сервера в текущем контексте безопасности можно использовать один из следующих двух подходов.

  • Чтобы проверить возможность подключения к связанному серверу в SSMS, перейдите к связанному серверу в обозреватель объектов, щелкните правой кнопкой мыши связанный сервер и выберите "Проверить подключение".

  • Чтобы проверить возможность подключения к связанному серверу в T-SQL, выполните простую SELECT инструкцию, например для получения базовых сведений каталога баз данных. Этот пример возвращает имена баз данных на связанном сервере.

    SELECT name FROM [SRVR002\ACCTG].master.sys.databases;  
GO

Присоединение таблиц со связанного сервера

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

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins  
FROM master.sys.server_principals AS local  
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked  
     ON local.name = linked.name;  
GO

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

Связанные серверы с Управляемым экземпляром SQL Azure

Если вы используете Управляемый экземпляр SQL Azure, см. следующие примеры из статьи sp_addlinkedserver (Transact-SQL):

Следующие шаги

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