Клонирование объектов безопасности между серверами

Привычная задача администратора базы данных — реплицировать имена учетных записей для регистрации между несколькими экземплярами SQL Server. Возможно, вы строите отдельную среду, имитирующую производственную (Dev, UAT и т. д.) или готовите сменный сервер или несколько узлов, которые составят группу доступности. В таком случае вам потребуются идентично настроенные объекты безопасности (имена учетных записей для регистрации, принадлежность к роли, пользователи). Часто возникают различные проблемы, из-за которых этот вопрос становится гораздо более сложным, чем кажется на первый взгляд. Различия в идентификаторе безопасности (SID) между экземплярами для имен учетных записей SQL Server, несовпадающие пароли и потерянные учетные записи пользователей — лишь некоторые из них.

Я придерживаюсь правила: если нужно многократно выполнить функцию, то следует подготовить сценарий, повторное использование которого упрощает жизнь администратора и освобождает время для работы с другими задачами или позволяет добиться желаемых результатов ценой меньших усилий. Много лет назад я написал сценарий, которым хочу поделиться в данной статье. С его помощью можно создать следующие объекты безопасности на одном исходном экземпляре SQL для распространения на другие экземпляры по мере необходимости:

имена учетных записей SQL Server;
имена учетных записей доверенной проверки подлинности (Active Directory или AD);
группы доверенной проверки подлинности (AD);
пользователи базы данных по умолчанию;
принадлежность к роли сервера.

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

Необходимые условия

Уже в течение десяти с лишним лет в сообществе SQL Server широко распространены два сценария, необходимые для корректного выполнения моего сценария: sp_help_revlogin и sp_hexadecimal. sp_help_revlogin генерирует программный код, который воссоздает имя учетной записи для входа и принудительно формирует SID, чтобы уменьшить вероятность рассогласования SID между именами входа и пользователями, копируемыми между экземплярами. sp_hexadecimal необходим для sp_help_revlogin, чтобы преобразовать хеш-код пароля в текстовую форму, которая используется в сценарии. Программный код обеих хранимых процедур приводится в листингах 1 и 2. Выполните его, прежде чем продолжать.

Сценарий клонирования имени входа

После того как выполнены предварительные условия, пришло время рассмотреть сценарий клонирования. Он разделен на отдельные секции, соответствующие объектам безопасности. В целях удобства чтения каждая секция разделена на описания программного кода, результатов и действий, производимых с результатами, чтобы применить их к целевому «клону».

Секция 1: имена входа SQL

В этой секции (листинг 3) две хранимые процедуры используются для формирования пяти столбцов, три из которых предназначены только для идентификации, а на два последних (script_command и user_command) следует обратить особое внимание. script_command формируется с помощью вызовов к sp_help_revlogin, чтобы создать программный код, при выполнении которого предоставляются команды CREATE LOGIN, выполняемые на целевом клоне. User_command — динамически формируемый программный код, который создаст объект пользователя в базе данных master (по умолчанию, но при необходимости программный код легко изменить).

Как показано на экране 1, формируются два столбца кода. Результаты script_command можно скопировать как столбец и одновременно выполнить на исходном сервере, на котором выполнялся код данной секции. Я всегда использую в таком случае второе окно запроса, так как полезно иметь под рукой результаты начального запроса.

Экран 1. Результаты работы листинга 3

На основе приведенного выше примера вновь выполняется запрос к экземпляру SOURCE, приведенный в листинге 4.

В результате будут получены выходные данные, показанные на экране 2.

Экран 2. Результаты работы листинга 4

Каждое имя входа SQL имеет блок кода, предоставляющий необходимые параметры для репликации имени входа экземпляра SOURCE на экземпляр TARGET: хешированный пароль, принудительно созданный SID и последующее назначение базе данных master по умолчанию; политика пароля и проверка окончания срока действия отключены. Найти и заменить эти значения в сценарии при необходимости можно с помощью простого нажатия клавиш Control+H.

На данном этапе я создаю окно запроса, связанное с экземпляром TARGET, и вставляю приведенный в листинге 4 программный код. За ним следует программный код из столбца user_command (листинг 5).

При выполнении на экземпляре TARGET вы передаете клонированные имена входа для проверки подлинности SQL из SOURCE в TARGET с совпадающими идентификаторами SID.

Это единственная секция, в которой требуется открыть второе окно для экземпляра SOURCE. Закройте окно запроса, открытое для выполнения сформированных команд sp_help_revlogin, и можно переходить к секции 2.

Секция 2: имена учетных записей AD

Создать имена учетных записей Active Directory гораздо проще, так как не существует препятствия в виде несоответствия идентификаторов SID. Программный код этой секции, в сущности, представляет собой динамический SQL для доступа к именам учетных записей AD в master.sys.server_principals для построения сценария CREATE LOGIN …FROM WINDOWS для любого имени учетной записи AD, фильтрованного, чтобы устранить зависимые от компьютера локальные имена учетных записей AD (листинг 6).

Связанные столбцы script_command и user_command выглядят таким образом после вставки в запрос к моему экземпляру TARGET, как в листинге 7.

Секция 3: группы AD

Группы AD обрабатываются почти как имена учетных записей AD. Единственное отличие между двумя категориями — значение типа столбца в master.sys.server_principles («U» для имен учетных записей AD, «G» для групп AD). Я разделил группы AD и имена учетных записей AD, так как в SQL Server 2005 не разрешалось назначать схему по умолчанию для группы AD. Но SQL Server 2005 более не поддерживается, поэтому необходимости в этом больше нет. Вы можете без труда изменить сценарий для своей среды (введите = «G» или введите = «U»), как показано в листинге 8.

Секция 4: принадлежность к роли сервера

В последней секции рассматривается принадлежность к роли сервера. При переносе баз данных между экземплярами, например для проверки заполнения, обучения или сред UAT, ни одно из прав роли уровня сервера не перемещается вместе с базами данных. Эта секция общего сценария обеспечивает сохранение разрешений уровня сервера на экземпляре TARGET (клонированном) (листинг 9).

В отличие от других секций этот программный код формирует только разовый специальный текст SQL для вызова команды sp_addrolemember. Результат выглядит примерно следующим образом:

EXEC master..sp_addsrvrolemember
N’SQLCRUISE\timf’, N’sysadmin’;

Просто выполните этот программный код на экземпляре TARGET, и все готово.

Итак, с помощью данного сценария можно без особого труда перемещать объекты безопасности между экземплярами. В следующей статье мы попытаемся выяснить, насколько изменился мир с появлением Powershell и каким образом с помощью Powershell можно еще больше упростить решение задачи.

Для удобства чтения полный текст сценария приведен в листинге 10.

Листинг 1. Создание sp_hexadecimal

--=============================
-- СОЗДАНИЕ sp_hexadecimal
--=============================
USE [master]
GO

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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

Листинг 2. СОЗДАНИЕ sp_help_revlogin

--=============================
--СОЗДАНИЕ sp_help_revlogin
--=============================
USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs  FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs  FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Листинг 3. Формирование пяти столбцов

--=================================================================
-- КОМАНДЫ SP_HELP_REVLOGIN И КОМАНДЫ СОЗДАНИЯ ПОЛЬЗОВАТЕЛЯ
БАЗЫ ДАННЫХ ПО УМОЛЧАНИЮ
--=================================================================

SELECT SP.[principal_id]
        , SP.[name]
        , SP.[default_database_name]
        , 'EXEC [sp_help_revlogin] ' + '''' + SP.name + '''' + ';' AS script_command
        , 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '];' AS user_command
FROM master.sys.[server_principals] SP
WHERE SP.[type] = 'S'
        AND SP.name != 'sa'
        AND SP.name NOT LIKE ('#%')
ORDER BY SP.[name];

Листинг 4. Запрос к экземпляру SOURCE

EXEC [sp_help_revlogin] 'austenford';
EXEC [sp_help_revlogin] 'chriscornell';
EXEC [sp_help_revlogin] 'davidbowie';
EXEC [sp_help_revlogin] 'prince';
EXEC [sp_help_revlogin] 'timford';
EXEC [sp_help_revlogin] 'trevorford';

Листинг 5. Программный код из столбца user_command

USE [master]; CREATE USER [austenford] FROM LOGIN [austenford];
USE [master]; CREATE USER [chriscornell] FROM LOGIN [chriscornell];
USE [master]; CREATE USER [davidbowie] FROM LOGIN [davidbowie];
USE [master]; CREATE USER [prince] FROM LOGIN [prince];
USE [master]; CREATE USER [timford] FROM LOGIN [timford];
USE [master]; CREATE USER [trevorford] FROM LOGIN [trevorford];

Листинг 6. Создание имен учетных записей Active Directory

--=================================================================
-- ИМЕНА ВХОДА AD КОМАНДЫ СОЗДАНИЯ ИМЕН ВХОДА И ПОЛЬЗОВАТЕЛЯ БАЗЫ ДАННЫХ ПО УМОЛЧАНИЮ
--=================================================================

SELECT SP.[principal_id]
        , SP.[name]
        , SP.[default_database_name]
        , 'CREATE LOGIN [' + SP.name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [master];' AS login_command
        , 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '] WITH DEFAULT_SCHEMA=[dbo];' AS user_command
FROM master.sys.[server_principals] SP
WHERE SP.[type] = 'U'
        AND SP.name NOT LIKE 'NT %'
ORDER BY SP.[name];

Листинг 7. Связанные столбцы script_command и user_command

CREATE LOGIN [SQLCRUISE\timf] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
CREATE LOGIN [SQLCRUISE\app_service] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
CREATE LOGIN [SQLCRUISE\comms] FROM WINDOWS WITH DEFAULT_DATABASE = [master];

USE [master]; CREATE USER [SQLCRUISE\timf] FROM LOGIN [SQLCRUISE\timf] WITH DEFAULT_SCHEMA=[dbo];
USE [master]; CREATE USER [SQLCRUISE\app_service] FROM LOGIN [SQLCRUISE\app_service] WITH DEFAULT_SCHEMA=[dbo];
USE [master]; CREATE USER [SQLCRUISE\comms] FROM LOGIN [SQLCRUISE\comms] WITH DEFAULT_SCHEMA=[dbo];

Листинг 8. Создание групп AD

--=================================================================
-- ГРУППЫ AD КОМАНДЫ СОЗДАНИЯ ИМЕН ВХОДА И ПОЛЬЗОВАТЕЛЯ БАЗЫ
ДАННЫХ ПО УМОЛЧАНИЮ
--=================================================================
SELECT SP.[principal_id]
        , SP.[name]
        , SP.[default_database_name]
        , 'CREATE LOGIN [' + SP.name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [master];' AS login_command
        , 'USE [master]; CREATE USER [' + SP.name + '] FROM LOGIN [' + SP.name + '] WITH DEFAULT_SCHEMA=[dbo];' AS user_command
FROM master.sys.[server_principals] SP
WHERE SP.[type] = 'G'
        AND SP.name NOT LIKE 'NT %'
ORDER BY SP.[name];

Листинг 9. Принадлежность к роли сервера

--=================================================================
-- ЧЛЕНЫ РОЛИ СЕРВЕРЫ
--=================================================================
SELECT R.name AS server_role
        , P.name AS role_member
        , 'EXEC master..sp_addsrvrolemember N' + '''' + P.name + '''' + ', N' + '''' + R.name + '''' + ';' AS command
FROM sys.server_role_members RM
        INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id
        INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R
                ON RM.role_principal_id = R.principal_id
WHERE P.name NOT LIKE '#%'
        AND P.name NOT LIKE 'NT %'
        AND P.type_desc <> 'SERVER_ROLE'
        AND P.name NOT IN ('sa')
ORDER BY R.[name], P.[name];

Листинг 10. Полный текст сценария

--======================================================
-- SP_HELP_REVLOGIN COMMANDS AND DEFAULT DB CREATE USER COMMANDS
--======================================================

SELECT SP.[principal_id]
        , SP.[name]
        , SP.[default_database_name]
        , ‘EXEC [sp_help_revlogin] ‘ + ‘’’’ + SP.name + ‘’’’ + ‘;’ AS script_command
        , ‘USE [master]; CREATE USER [‘ + SP.name + ‘] FROM LOGIN [‘ + SP.name + ‘];’ AS user_command
FROM master.sys.[server_principals] SP
WHERE SP.[type] = ‘S’
        AND SP.name != ‘sa’
        AND SP.name NOT LIKE (‘#%’)
ORDER BY SP.[name];

--======================================================
-- AD LOGINS CREATE LOGINS AND DEFAULT DB USER COMMANDS
--======================================================
SELECT SP.[principal_id]
        , SP.[name]
        , SP.[default_database_name]
        , ‘CREATE LOGIN [‘ + SP.name + ‘] FROM WINDOWS WITH DEFAULT_DATABASE = [master];’ AS login_command
        , ‘USE [master]; CREATE USER [‘ + SP.name + ‘] FROM LOGIN [‘ + SP.name + ‘] WITH DEFAULT_SCHEMA=[dbo];’ AS user_command
FROM master.sys.[server_principals] SP
WHERE SP.[type] = ‘U’
        AND SP.name NOT LIKE ‘NT %’
ORDER BY SP.[name];
--======================================================
-- AD GROUPS CREATE LOGINS AND DEFAULT DB USER COMMANDS
--======================================================
SELECT SP.[principal_id]
        , SP.[name]
        , SP.[default_database_name]
        , ‘CREATE LOGIN [‘ + SP.name + ‘] FROM WINDOWS WITH DEFAULT_DATABASE = [master];’ AS login_command
        , ‘USE [master]; CREATE USER [‘ + SP.name + ‘] FROM LOGIN [‘ + SP.name + ‘] WITH DEFAULT_SCHEMA=[dbo];’ AS user_command
FROM master.sys.[server_principals] SP
WHERE SP.[type] = ‘G’
        AND SP.name NOT LIKE ‘NT %’
ORDER BY SP.[name];
--======================================================
-- SERVER ROLE MEMBERS
--======================================================
SELECT R.name AS server_role
        , P.name AS role_member
        , ‘EXEC master..sp_addsrvrolemember N’ + ‘’’’ + P.name + ‘’’’ + ‘, N’ + ‘’’’ + R.name + ‘’’’ + ‘;’ AS command
FROM sys.server_role_members RM
        INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id
        INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = ‘SERVER_ROLE’) R
                ON RM.role_principal_id = R.principal_id
WHERE P.name NOT LIKE ‘#%’
        AND P.name NOT LIKE ‘NT %’
        AND P.type_desc <> ‘SERVER_ROLE’
        AND P.name NOT IN (‘sa’)
ORDER BY R.[name], P.[name];