SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

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

ПУБЛИКАЦИИ  

По материалам статьи Microsoft: INF: How To Transfer Logins and Passwords Between SQL Servers (Q246133)

СОДЕРЖАНИЕ

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

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

[Содержание]

Передача логинов и паролей между серверами SQL Server 7.0 и SQL Server 2000 или между SQL Server 2000 серверами

Чтобы передать логины и пароли SQL Server 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 серверами

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

  1. Выполните представленный ниже скрипт на SQL сервере с которого необходимо перенести логины. Этот скрипт создаёт два хранимых процедуры с именами sp_hexadecimal и sp_help_revlogin в системной базе данных master. После успешного исполнения скрипта, выполните операции из пункта 2.

    ОБРАТИТЕ ВНИМАНИЕ: Создаваемые в процессе исполнения скрипта процедуры напрямую оперируют с системными таблицами SQL Server. Структура этих таблиц может изменяться от версии к версии SQL Server, что может повлиять на работоспособность этого скрипта.

    
    ----- Begin Script, Create sp_help_revlogin procedure -----
    
    USE master
    GO
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
      DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
        @binvalue varbinary(256),
        @hexvalue varchar(256) OUTPUT
    AS
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = '0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = '0123456789ABCDEF'
    WHILE (@i <= @length)
    BEGIN
      DECLARE @tempint int
      DECLARE @firstint int
      DECLARE @secondint int
      SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
      SELECT @firstint = FLOOR(@tempint/16)
      SELECT @secondint = @tempint - (@firstint*16)
      SELECT @charvalue = @charvalue +
        SUBSTRING(@hexstring, @firstint+1, 1) +
        SUBSTRING(@hexstring, @secondint+1, 1)
      SELECT @i = @i + 1
    END
    SELECT @hexvalue = @charvalue
    GO
    
    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
      DROP PROCEDURE sp_help_revlogin 
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name    sysname
    DECLARE @xstatus int
    DECLARE @binpwd  varbinary (256)
    DECLARE @txtpwd  sysname
    DECLARE @tmpstr  varchar (256)
    DECLARE @SID_varbinary varbinary(85)
    DECLARE @SID_string varchar(256)
    
    IF (@login_name IS NULL)
      DECLARE login_curs CURSOR FOR 
        SELECT sid, name, xstatus, password FROM master..sysxlogins 
        WHERE srvid IS NULL AND name <> 'sa'
    ELSE
      DECLARE login_curs CURSOR FOR 
        SELECT sid, name, xstatus, password FROM master..sysxlogins 
        WHERE srvid IS NULL AND name = @login_name
    OPEN login_curs 
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
    IF (@@fetch_status = -1)
    BEGIN
      PRINT 'No login(s) found.'
      CLOSE login_curs 
      DEALLOCATE login_curs 
      RETURN -1
    END
    SET @tmpstr = '/* sp_help_revlogin script ' 
    PRINT @tmpstr
    SET @tmpstr = '** Generated ' 
      + CONVERT (varchar, GETDATE()) + ' on ' + @@ServerNAME + ' */'
    PRINT @tmpstr
    PRINT ''
    PRINT 'DECLARE @pwd sysname'
    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
        PRINT ''
        SET @tmpstr = '-- Login: ' + @name
        PRINT @tmpstr 
        IF (@xstatus & 4) = 4
        BEGIN -- NT authenticated account/group
          IF (@xstatus & 1) = 1
          BEGIN -- NT login is denied access
            SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
            PRINT @tmpstr 
          END
          ELSE BEGIN -- NT login has access
            SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
            PRINT @tmpstr 
          END
        END
        ELSE BEGIN -- SQL Server authentication
          IF (@binpwd IS NOT NULL)
          BEGIN -- Non-null password
            EXEC sp_hexadecimal @binpwd, @txtpwd OUT
            IF (@xstatus & 2048) = 2048
              SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
            ELSE
              SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
            PRINT @tmpstr
    	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
              + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
          END
          ELSE BEGIN 
            -- Null password
    	EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
            SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
              + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
          END
          IF (@xstatus & 2048) = 2048
            -- login upgraded from 6.5
            SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
          ELSE 
            SET @tmpstr = @tmpstr + '''skip_encryption'''
          PRINT @tmpstr 
        END
      END
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
      END
    CLOSE login_curs 
    DEALLOCATE login_curs 
    RETURN 0
    GO
    
    ----- End Script -----

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

    
       EXEC master..sp_help_revlogin

    Хранимая процедура 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 логинов, см. статью:

       INF: How to Resolve Permission Issues When a Database is Moved (Q240872)

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

        PRB: Troubleshooting Orphaned Users Topic in BOL Incomplete (Q274188)

  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.

[Содержание]

Перевод: Александра Гладченко  2002г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013