Главная » Microsoft SQL Server, Базы данных » Подключение к внешним источникам данных

0

SQL Server также способен устанавливать подключение к любому совместимому с OLE DB или ODBC источнику данных. Эта связь может быть установлена как в Management Studio, так и с помощью кода SQL.

Подключение в Management Studio

Подключение к другому серверу может быть установлено с помощью Management Studio или программного кода. В Management Studio связанные серверы перечислены в узле Security — здесь в каждой связи уже определено, как подключиться к удаленному серверу и зарегистрироваться в нем. Щелкните правой кнопкой мыши на узле Security под именем сервера и выберите пункт New Linked Server. Откроется форма свойств связанного сервера, показанная на рис. 15.2.

Подключение к источникам данных, отличным от SQL Server, описывается далее в этой главе.

Выбор сервера

Во вкладке General формы Linked Server Property введите имя внешнего сервера и установите переключатель в положение SQL Server. Для подключения к именованному экземпляру SQL Server введите это имя в формате сервер\экземпляр без квадратных скобок. На рис. 15.2 подключаемым сервером является Noli\SQL2.

Puc. 15.2. Форма свойств связанного сервера

SQL Server 2005 может подключиться к любому экземпляру SQL Server 2000, SQL Server 7, но не может подключиться к SQL Server 6.5 без использования драйвера ODBC.

Конфигурирование регистрационных данных

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

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

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

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

Server: Msg 7416, Level 16, State 1, Line 1 Access to the remote server is denied because no login-mapping exists.

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

Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user ‘SQL1.

Этот параметр установлен по умолчанию в Management Studio.

?               Подключение может быть установлено с использованием контекста безопасности текущей регистрационной записи. Когда локальный экземпляр SQL Server устанавливает соединение с удаленным сервером, он делегирует полномочия (т.е. входит на удаленный сервер с использованием реквизитов текущего пользователя). Этот метод аналогичен использованию списка пользователей и выбору параметра Impersonate, за тем исключением, что в данном случае используется делегирование полномочий. Для передачи контекста безопасности учетная запись должна быть одной и той же; в данном случае недостаточно одинакового имени пользователя и пароля.

Права и роли пользователей для распределенных запросов будут назначаться на удаленном сервере.

Для использования делегирования полномочий на всех серверах должна быть запущена операционная система Windows 2000 или Windows ХР; при этом должны быть активизированы Active Directory и Kerberos.

Этот параметр используется по умолчанию при создании подключения с помощью кода T-SQL.

В большинстве распределенных запросов от SQL Server к SQL Server контекст безопасности текущей учетной записи является лучшим параметром подключения, поскольку сохраняет идентичность пользователя и подтверждает план безопасности. Если инфраструктура не поддерживает Active Directory и Kerberos, лучше отображать список пользователей.

?               Подключение может быть установлено с использованием текущего контекста безопасности. Последний параметр назначает каждому пользователю, не отображенному в списке, заранее запрограммированную учетную запись внешнего сервера. Хотя этот метод может показаться простейшим, он предоставляет всем локальным пользователям одинаковый доступ к серверу. Использование этого параметра может разрушить любой стоящий план защиты информации, поскольку не позволяет внешнему экземпляру SQL Server достичь уровня защиты С2.

Конфигурирование параметров

На третьей вкладке формы связанного сервера, Server Options, доступны следующие параметры, управляющие тем, как SQL Server ожидает получения данных от удаленного сервера.

?               Collation Compatibility. Установите этот параметр в истинное значение (true), если два сервера используют одинаковый набор символов и порядок сопоставления.

?               Data Access. Если этот параметр установлен в ложное значение (false), то он запрещает распределенные запросы к удаленному серверу.

?               RPC. Если этот параметр установлен в истинное значение, то к внешнему серверу могут быть выполнены вызовы удаленных процедур.

?               RPC Out. Если этот параметр имеет истинное значение, то вызовы удаленных процедур разрешено принимать от внешнего сервера.

?               Use Remote Collation. Истинное значение этого параметра определяет использование набора символов и их порядка внешнего сервера вместо существующих на текущем сервере.

?               Collation Name. Определяет порядок сопоставления для распределенных запросов. Этот параметр может быть установлен, если параметр Collation Compatibility имеет значение true.

?               Connection Timeout. Время ожидания соединения в миллисекундах.

?               Query Timeout. Время ожидания выполнения распределенного запроса в миллисекундах.

Как только соединение будет корректно установлено, скорее всего, станет доступным список таблиц в соответствующем узле внешнего сервера. Перечисленные таблицы будут принадлежать базе данных, принятой на внешнем сервере по умолчанию. Если этой базой данных является master, а утилита Management Studio сконфигурирована для сокрытия системных объектов, то никаких таблиц вы не увидите.

Удаление связанного сервера в Management Studio также удаляет все отображения регистрационных записей.

Подключение с помощью T-SQL

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

Установка подключения

Для установки подключения к внешнему серверу с помощью программного кода используется системная хранимая процедура sp_addlinkedserver. Если соединение к удаленному серверу было установлено и имя экземпляра этого сервера доступно как имя подключения, то требуются всего два параметра: имя внешнего сервера и серверный продукт. Следующая команда создает подключение к экземпляру SQL2 на моем сервере тестирования ([XPS\Developer]):

— Примечание: сервер разработки автора называется XPS — Экземпляры этого сервера:

— [XPS] SQL Server 2000 Developer Edition — [XPS\Developer] SQL Server 2005 Developer Edition — [XPS\SQLExpress] SQL Server 2005 Express Edition — [XPS\Standard] SQL Server 2005 Standard Edition EXEC sp_addlinkedserver @server = ‘XPS\SQLExpress’,

@srvproduct = ‘SQL Server1;

Если вы собираетесь запустить этот сценарий, вам потребуется изменить имя На заметку сервера и его экземпляра, чтобы они соответствовали вашей конфигурации.

Для подключения к экземпляру сервера с использованием имени, отличного от реального имени экземпляра, в инструкцию добавляются два параметра. Первый параметр, provider, должен иметь значение SQLOLEDB, а параметр @datasrc (источник данных) передает реальное имя экземпляра SQL Server. Параметр @srvproduct (серверный продукт) остается пустым. Параметр @server должен содержать имя связанного сервера, которое должно быть известно. В следующем примере показано, как выполняется подключение к экземпляру SQL2 сервера Noli, однако в запросах этот сервер будет упоминаться как Yonder:

EXEC sp_addlinkedserver @server = 1 Yonder1,

@datasrc = ,Noli\SQL2I,

@srvproduct = 1 ‘ ,

@provider=’SQLOLEDB1;

I Представление каталога sys . servers перечисляет все серверы, включая под- SVS ключенные. Системная хранимая процедура sp_linkedservers также воз- I * вращает информацию обо всех подключенных серверах.

SELECT [Имя] , Продукт, Провайдер, Источник_данных FROM sys.servers WHERE Is_Linked = 1;

Для удаления существующего подключения к серверу (не влияющего на сам внешний сервер) используется системная хранимая процедура sp_dropserver:

EXEC sp_DropServer ©server = 1 Yonder1;

Если для связанного сервера существуют какие-либо ограничения, то они также удаляются.

Распределенная защита и регистрация

В утилите Management Studio вопрос безопасности разбит на две части: отображение регистрационных данных, режим работы с не отображенными регистрациями. В Т-SQL для решения обоих вопросов используется системная хранимая процедура sp_addlinkedsrvlogin:

sp_addlinkedsrvlogin

@rmtsrvname = ‘ имя_удаленного_сервера’ ,

@useself = ‘useself’, (по умолчанию True)

@locallogin = ‘локальная_учетная_запись’, (по умолчанию Null)

@rmtuser = 1удаленный_пользователь’, (по умолчанию Null)

@rmtpassword = 1удаленный_пароль’ (по умолчанию Null);

Если связанный сервер был добавлен с помощью кода Т-SQL, а не средствами Management Studio, то параметры безопасности для не отображенных регистраций уже сконфигурированы для использования текущего контекста безопасности.

Если параметр @locallogin имеет пустое значение NULL, то параметры применяются ко всем пользователям, не отображенным в списке. Параметр @useself идентичен параметру Impersonate, о котором мы говорили ранее.

Следующая хранимая процедура использует учетную запись Noli\Paul для доступа к серверу Noli\SQL2 под именем sa и с паролем secret:

sp_addlinkedsrvlogin

@rmtsrvname = ‘XPS\SQLExpress1,

@useself = ‘false’,

@locallogin = ‘NOLI\Paul’,

@rmtuser = ‘sa’ ,

@rmtpassword = 1 secret';

В следующем примере все не отображенные в списке пользователи настраиваются для подключения с использованием собственного контекста безопасности (рекомендуемый параметр). Имя локального пользователя равно NULL, так что эта регистрация на внешнем сервере применяется ко всем пользователям, не отображенным в списке. Параметр @useself не определяется, так что используется его значение, принятое по умолчанию, — true. Это значит, что все пользователи будут использовать текущий контекст безопасности:

EXEС sp_addlinkedsrvlogin @rmtsrvname = ‘NOLI\SQL2′;

В третьем примере мы запретим всем не отображенным в списке пользователям выполнение распределенных запросов. В нем второй параметр, @useself, установлен в значение false, а регистрационное имя и пароль отображенных пользователей равны пустым значениям (NULL):

EXEC sp_addlinkedsrvlogin ,N0LI\SQL2′/ ‘false';

I Представление каталога sys . Linked_Logins перечисляет регистрационные SVS записи. Системная хранимая процедура sp_helplinkedsrvlogin также воз-

*                 I * вращает информацию о подключенных регистрационных записях:

SELECT [Имя], Продукт, Провайдер, Источник_данных FROM sys.servers WHERE Is_Linked = 1;

Для сброса подключения к связанному серверу используется системная хранимая процедура sp_dropl inkedsrvlogin: sp_droplinkedsrvlogin

@rmtsrvname = 1имя_удаленного_сервера’, (нет умолчаний)

@locallogin = ‘локальная_учетная_запись’ (нет умолчаний);

В следующем примере мы удалим регистрационную запись Noli\Paul, отображенную наNoli\SQL2:

EXEC sp_droplinkedsrvlogin

@rmtsrvname = ‘XPS\SQLExpress’,

@locallogin = ‘NOLl\Paul';

Для удаления отображения, применяемого к не отображаемым пользователям, запустите ту же процедуру, только задайте пустую локальную учетную запись (NULL):

EXEC sp_droplinkedsrvlogin ‘XPS\SQLExpress’, NULL;

Параметры связанного сервера

Параметры связанного сервера, перечисленные во вкладке Server Options формы Linked Server Options, можно установить и программным путем с помощью системной хранимой процедуры sp_serveroption. Эта процедура должна вызываться для каждого из устанавливаемых параметров: sp_serveroption

@server = ‘сервер’,

@optname = ‘имя_параметра’,

@optvalue = ‘ значение_параметра';

Параметры в этом примере те же, что и в форме, только с дополнением параметра lazy schema validation, который отключает проверку схемы таблиц на распределенные запросы. Вы можете его использовать, когда уверены в безопасности схемы, но хотите уменьшить сетевую нагрузку.

Представление каталога sys . servers возвращает параметры связанного сервера. Системная хранимая процедура sp_helpserver также возвращает информацию о связанных серверах:

SELECT [Имя], Продукт, Провайдер, Источник_данных FROM sys.servers WHERE Is_Linked = 1;

Подключение к источникам данных, отличным от SQL Server

Если внешний источник данных — не SQL Server, вы все равно имеете возможность доступа к данным. Все зависит от доступности и функций драйверов ODBC или поставщиков OLE DB. SQL Server для доступа к внешним данным использует механизм OLE DB, и некоторые его компоненты входят в комплект сервера. Если по какой-либо причине OLE DB не доступен для некоторого внешнего источника данных, используйте поставщик Microsoft OLE DB Provider for ODBC Drivers. Практически любой тип источника данных имеет драйвер ODBC.

Для установки подключения к серверу либо в Management Studio, либо программным путем строке подключения требуется дополнительная информация (кроме имени подключаемого сервера, провайдера и имени продукта). Некоторые настройки распространенных источников данных приведены в табл. 15.1.

В качестве примеров подключения к источникам данных, отличным от SQL Server, мы с помощью распределенных запросов пополним учебную базу данных Cape Hateras Adventures информацией из Access и Excel. Данная учебная база данных моделирует типичный малый бизнес, который в настоящее время использует Access и Excel для хранения списка клиентов и расписания.

Таблица 15.1. Настройки подключения к другим источникам данных

Подключение к… Имя провайдера Продукт Источник данных Строка провайдера
MS Access MS Jet 4.0 OLE DB Access 2003 Местоположение файла базы данных null
Excel MS Jet 4.0 OLE DB Excel Местоположение файла с рабочим листом Excel 5.0
Oracle MS OLE Provider for Oracle Oracle Системный идентификатор Oracle null

Подключение к Excel

Пример, использованный в настоящем разделе, можно непосредственно взять из сценария CHA2_Convert. sql. Он перемещает данные из старой версии (Access и Excel) в новую (SQL Server). Сотрудники компании ранее хранили расписание туров в Excel (рис. 15.3).

Рис. 15.3. Перед переходом на платформу SQL Server компания Cape Hatteras Adventures обслуживала график своих туров в электронной таблице CHAl_Schedule. xls

При работе с Excel каждая электронная страница или именованный диапазон книги появляется в SQL Server в виде таблицы, когда доступ осуществляется с помощью провайдера. В Excel именованные диапазоны определяются с помощью команды меню Insert^Name^Define. Для создания нового именованного диапазона и редактирования существующих используется диалоговое окно определения имени. Рабочая книга CHAl_Schedule имеет пять именованных диапазонов (рис. 15.4), которые выглядят практически так же, как представления SQL

Server. Каждый из пяти именованных диапазонов рабочей книги при подключении появляется как таблица. SQL Server при этом может использовать стандартные инструкции SELECT, INSERT, UPDATE и DELETE, как при обращении к обычной собственной таблице.

Puc. 15.4. Пять таблиц определены в Excel как именованные диапазоны

Следующий пример кода SQL настраивает рабочую книгу Excel как связанный сервер:

Execute sp_addlinkedserver @server = 1CHAl_Schedule1,

@srvproduct = 1 Excel1,

@provider = 1 Microsoft.Jet.OLEDB.4.0′,

@datasrc = ‘C:\SQLServerBible\CHAl_Schedule.xls’,

@provstr = ‘Excel 5.01

Электронные книги Excel не являются многопользовательскими. По этой причине SQL Server не может выполнить распределенный запрос, в котором участвует книга Excel, пока она открыта каким-либо пользователем.

Подключение к MS Access

Неудивительно, что SQL Server без труда подключается к базам данных MS Access. SQL Server использует провайдер OLE DB Jet для доступа к механизму Jet, который задействуется программой Access для доступа к данным в файлах . mdb.

Так как программа Access сама представляет собой СУБД, не существует никаких хитростей в подготовке ее баз данных, как это было в случае с Excel. Каждая таблица базы данных Access будет отображена в виде таблицы в узле Linked Servers утилиты Management Studio.

Согласно нашему сценарию, до перехода на платформу SQL Server список клиентов хранился в базе данных Access. Следующий код, который можно взять из файла CHA2_Convert. sql, связывается с базой данных Access CHAl_Customers .mdb, чтобы СУБД SQL Server могла запросить из нее данные и заполнить собственные таблицы:

EXEC sp_addlinkedserver ‘CHAl_Customers’,

‘Access 2003 ‘ ,

‘Microsoft.Jet.OLEDB.4.0′,

‘С:\SQLServerBible\CHAl_Customers.mdb';

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

EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘CHAl_Schedule1,

@useself = ‘false1;

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии