Підключення до зовнішніх джерел даних

SQL Server також здатний встановлювати підключення до будь-якого сумісного з OLE DB або ODBC джерела даних Цей звязок може бути встановлена ​​як в Management Studio, так і за допомогою коду SQL

Підключення в Management Studio

Підключення до іншого сервера може бути встановлено за допомогою Management Studio або програмного коду У Management Studio повязані сервери перераховані у вузлі Security – тут у кожного звязку вже визначено, як підключитися до віддаленого сервера і зареєструватися в ньому Клацніть правою кнопкою миші на вузлі Security під імям сервера і виберіть пункт New Linked Server Відкриється форма властивостей повязаного сервера, показана на рис 152

Підключення до джерел даних, відмінним від SQL Server, описується далі в цьому розділі

Вибір сервера

У вкладці General форми Linked Server Property введіть імя зовнішнього сервера і встановіть перемикач в положення SQL Server Для підключення до іменованого екземпляру SQL Server введіть це імя в форматі сервер \ екземпляр без квадратних дужок На рис 152 підключається сервером є Noli \ SQL2

Puc 152 Форма властивостей повязаного сервера

SQL Server 2005 може підключитися до будь-якого екземпляру SQL Server 2000, SQL Server 7, але не може підключитися до SQL Server 65 без використання драйвера 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 sysservers 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 sysservers 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 sysservers 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, або програмним шляхом рядку підключення потрібна додаткова інформація (крім імені підключається сервера, провайдера та імені продукту) Деякі настройки поширених джерел даних наведено в табл 151

Як приклади підключення до джерел даних, відмінним від SQL Server, ми за допомогою розподілених запитів поповнимо навчальну базу даних Cape Hateras Adventures інформацією з Access і Excel Дана навчальна база даних моделює типовий малий бізнес, який в даний час використовує Access і Excel для зберігання списку клієнтів і розкладу

Таблиця 151 Налаштування підключення до інших джерел даних

Підключення к .

Імя провайдера

Продукт

Джерело даних

Рядок провайдера

MS Access

MS Jet 40 OLE DB

Access 2003

Місцезнаходження файлу бази даних

null

Excel

MS Jet 40 OLE DB

Excel

Місцезнаходження файлу з робочим листом

Excel 50

Oracle

MS OLE Provider for Oracle

Oracle

Системний ідентифікатор Oracle

null

Підключення до Excel

Приклад, використаний в цьому розділі, можна безпосередньо взяти зі сценарію CHA2_Convert sql Він переміщує дані зі старої версії (Access і Excel) в нову (SQL Server) Співробітники компанії раніше зберігали розклад турів в Excel (рис 153)

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

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

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

Puc 154 Пять таблиць визначені в Excel як іменовані діапазони

Наступний приклад коду SQL налаштовує робочу книгу Excel як повязаний сервер:

Execute sp_addlinkedserver @server = 1CHAl_Schedule1,

@srvproduct = 1 Excel1,

@provider = 1 MicrosoftJetOLEDB40,

@datasrc = C:\SQLServerBible\CHAl_Schedulexls,

@provstr = Excel 501

Електронні книги 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 ,

‘MicrosoftJetOLEDB40,

‘С: \ SQLServerBible \ CHAl_Customersmdb;

Якщо у вас зявилися складнощі при роботі з розподіленими запитами, в першу чергу перевірте контекст безпеки Access не очікує встановлення будь-якого контексту безпеки, тому не відображені реєстраційні записи будуть встановлені у незахищений контекст

EXEC sp_addlinkedsrvlogin @rmtsrvname = CHAl_Schedule1,

@useself = false1

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*