Передача логінів та паролів між SQL серверами

1.	Вступ
2.	Передача логінів та паролів між серверами SQL Server 7.0
3.	Передача логінів та паролів між серверами SQL Server 7.0 та SQL Server 2000 або між SQL Server 2000 серверами
4.	Скрипт переміщення логінів з паролями та оригінальними SID між SQL серверами
5.	Рекомендації

Інформація в цій статті стосується Microsoft SQL Server 7.0/2000 (усі видання)

Після переміщення бази даних на інший сервер, користувачі колишнього SQL сервера не зможуть підключитися до нового сервера. Це тому, що на новому сервері відсутні логіни цих користувачів і їх необхідно заповнити. У статті Microsoft Q246133 пропонується рішення, яке дозволяє спростити процедуру передачі логінів на інший сервер і описуються найбільш типові проблеми, які можуть виникнути. Якщо після переміщення бази даних на інший сервер Ви отримаєте таке повідомлення про помилку:

Msg 18456, Level 16, State 1 Login failed for user '%ls'.

Ви повинні передати логіни та паролі на новий сервер.

SQL Server 7.0 Data Transformation Services (DTS) Object Transfer дозволяє передавати логіни та користувачів між двома серверами, але не передбачає передачу паролів для автентифікованих SQL Server логінів. Щоб передавати логіни і паролі одного SQL Server 7.0 на інший, можна використовувати наведену нижче процедуру sp_help_revlogin . Ця процедура створює сценарій, який може бути виконаний на новому сервері, де будуть створені логіни з правильними SID та встановленими колишніми паролями.

Щоб передати логіни та паролі SQLServer 7.0 на будь-який екземпляр SQL Server 2000, або між двома екземплярами SQL Server 2000, можна використовувати новий DTS Package Transfer Logins Task, що входить до комплекту SQL Server 2000. Для цього потрібно:

1. Підключіться до SQL Server 2000, на який необхідно перенести логіни з паролями, і використовуйте Data Transformation Services, що входить до постачання SQL Server Enterprise Manager, розгорніть папку з таким же ім'ям у дереві нового сервера і клацніть правою кнопкою миші Local Packages , а потім виберіть New Package . 2. Після того, як з'явиться вікно майстра DTS, клацніть Transfer Logins Task з меню Task . Вкажіть необхідну інформацію про сервери у вкладках Source, Destination та Logins. Якщо Ви імпортуєте логіни з SQL сервера, який знаходиться на іншому комп'ютері, необхідно, щоб екземпляр нового SQL сервера запускався з облікового запису домену.

ЗВЕРНІТЬ УВАГУ : Ви можете використовувати метод з DTS або наведений нижче сценарій для передачі логінів між SQL Server 7.0 і SQL Server 2000, або між різними екземплярами SQL Server 2000. Метод DTS може передати паролі, але не може передати оригінальні SID. Якщо логін буде створений з відмінним від оригіналу SID і бази даних користувача також будуть переміщені на новий сервер, зв'язок між користувачами бази даних і її логінами буде втрачена. Для передачі оригіналів SID та запобігання втрати зв'язку між користувачами та логінами, використовуйте наведений нижче сценарій замість методу DTS.

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

1. Виконайте поданий нижче скрипт на SQL сервері, з якого необхідно перенести логіни. Цей скрипт створює дві процедури, що зберігаються з іменами sp_hexadecimal і sp_help_revlogin в системній базі даних master . Після успішного виконання скрипту виконайте операції з пункту 2.

ЗВЕРНІТЬ УВАГУ : Процедури, що створюються в процесі виконання скрипта, безпосередньо оперують із системними таблицями SQL Server. Структура цих таблиць може змінюватися від версії до версії SQL Server, що може спричинити працездатність цього скрипта.

2. Після того, як буде створена процедура sp_help_revlogin, що зберігається, запустіть цю процедуру в Query Analyzer на вихідному сервері:

Sp_help_revlogin, що зберігається, може використовуватися і на SQL Server 7.0 і на SQL Server 2000. Результат, що виводиться sp_help_revlogin, являє собою готовий скрипт, який створює логіни з оригінальними SID і паролями. Збережіть виведений у вікно результатів виконання скрипта текст, а потім виконайте його як скрипт у Query Analyzer на тому SQL сервері, куди необхідно перенести логіни

1. Уважно проаналізуйте створюваний процедурою скрипт перед тим, як запустити його на SQL сервері, куди необхідно передати логіни. Якщо Ви маєте передати NT логіни на SQL сервер в іншому домені, відредагуйте згенерований процедурою sp_help_revlogin скрипт і замініть ім'я старого домену на нове ім'я домену у всіх інструкціях sp_grantlogin. Оскільки логіни в новому домені не матимуть той самий SID як у логінів у старому домені, зв'язок користувачів бази даних з логінами буде порушено. Щоб вирішити цих осиротілих користувачів, див. статті, згадані нижче. Якщо Ви передаєте NT логіни між SQL серверами в одному домені, буде використовуватися той же SID, ікористувач не повинен втратити зв'язок зі своїми логінами. 2. Після того, як логіни будуть переміщені, користувачі не матимуть колишніх дозволів щодо доступу до переміщеної бази даних. Ця проблема відома як "orphaned user". Якщо Ви спробуєте надати логіну доступ до бази даних, це може скінчитися невдачею з повідомленням, що користувача вже існує:

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

Щоб отримати інструкції щодо вирішення проблеми таких "осиротілих" користувачів, вивчіть наведені нижче статті Microsoft Knowledge Base. Для осиротілих SQL і NT логінів, див.

Для отримання інформації про застосування процедури sp_change_users_login, яка зберігається, яка перев'язує до логін осиротілих користувачів (це стосується тільки користувачів, що втратили зв'язок зі стандартними SQL логінами), вивчіть наступну статтю:

3. Такий підхід стає можливим через параметр @encryptopt в системній процедурі sp_addlogin, що зберігається, яка створює логін, використовуючи зашифрований пароль. Для отримання додаткової інформації про цю процедуру див. тему в SQL Server Books Online: "sp_addlogin (T-SQL)". 4. За замовчуванням лише члени серверної ролі sysadmin мають право надавати дозвіл на вибірку з таблиці sysxlogins. Якщо член ролі sysadmin не надасть необхідні дозволи, кінцеві користувачі не зможуть створювати або виконувати ці процедури, що зберігаються. 5. Поданий вище підхід не передає інформацію про задану за умовчанням базу даних для кожного логіна, так як задана за умовчанням база даних може бути іншою на новому сервері. Щоб встановити задану за умовчанням базу даних для логінів, використовуйте системну процедуру, що зберігаєтьсяsp_defaultdb, вказуючи для неї як параметри ім'я логіну та задану для нього за замовчуванням базу даних. Для отримання докладної інформації про використання цієї процедури див. у SQL Server Books Online тему: "sp_defaultdb". 6. У процесі передачі логінів між SQL серверами, якщо порядок сортування вихідного сервера – case-insensitive, а порядок сортування нового сервера – case-sensitive, Вам доведеться вводити на новому сервері всі алфавітні символи в паролях у верхньому регістрі. 7. Якщо порядок сортування вихідного сервера - case-sensitive, а порядок сортування нового сервера - case-insensitive, Ви не зможете зареєструватися під переміщеними логінами після використання процедури, описаної в цій статті, якщо початкові паролі не містили жодних алфавітних символів або якщо всі алфавітні символи Початкові паролі були символами у верхньому регістрі. Якщо обидва сервери – case-sensitive або обидва сервери – case-insensitive, у Вас не повинно виникнути таких проблем. Це побічний ефект механізму, з якого SQL сервер обробляє паролі. Додаткові відомості див. у SQL Server 7.0 Books Online: "Effect on Passwords of Changing Sort Orders" 8. Якщо виконувати сгенерований процедурою sp_help_revlogin скрипт на сервері, де вже заведено логін з такими ж іменами, як у логінів у цьому скрипті, буде отримано помилку:

Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56 The login 'test1' already exists.

Аналогічно, якщо на новому сервері вже існують логін з тим самим SID, Ви отримаєте помилку:

Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93 Supplied parameter @sid is in use.

З цієї причини дуже важливо ретельно вивчити вмістзгенерованого скрипта та таблиці sysxlogins, щоб мати можливість внести до його тексту відповідні зміни. 9. Значення SID для логіну використовується як основа доступу до бази даних SQL Server. Тому, якщо один логін має два різні SID (для двох різних баз даних на одному сервері), цей логін матиме доступ тільки до тієї бази даних, чий SID відповідає значенню в syslogins для цього логіну. Така ситуація може виникнути, якщо ці дві бази даних були об'єднані із двох різних серверів. Щоб вирішити цю проблему, такий логін необхідно видалити з бази даних, щоб позбутися невідповідності SID, використовуючи процедуру sp_dropuser, що зберігається, і додати його знову, використовуючи збережену процедуру sp_adduser.