Transferir inicios de sesión y contraseñas entre servidores SQL Server

Versión del producto original: SQL Server
Número de KB original: 918992, 246133

Resumen

En este artículo se muestra cómo transferir SQL Server inicios de sesión y contraseñas entre instancias de Microsoft SQL Server que se ejecutan en Windows. Use estos procedimientos en escenarios de migración, restauración o alta disponibilidad de SQL Server para mantener intacta la autenticación de los usuarios y evitar que queden usuarios huérfanos en la base de datos de la instancia de destino. Las instancias de origen y destino pueden estar en el mismo servidor o en servidores diferentes, y sus versiones pueden diferir.

¿Por qué transferir inicios de sesión entre instancias de SQL Server?

Al mover una base de datos a un nuevo servidor, por ejemplo durante una migración o restauración, los usuarios de la base de datos se mueven con la base de datos, pero es posible que los inicios de sesión de nivel de servidor coincidentes no existan en la nueva instancia. Esa falta de coincidencia produce usuarios huérfanos. La transferencia de inicios de sesión y contraseñas mantiene intacta la autenticación del usuario y evita interrupciones de inicio de sesión después de mover una base de datos.

Después de mover una base de datos de una instancia de SQL Server en el servidor A a una instancia de SQL Server en el servidor B, es posible que los usuarios no puedan iniciar sesión en el servidor de bases de datos en el servidor B. Además, es posible que los usuarios reciban el siguiente mensaje de error:

Error de inicio de sesión del usuario "MiUsuario". (Microsoft SQL Server, Error: 18456)

Este problema se produce porque los inicios de sesión de la instancia de SQL Server en el servidor A no existen en la instancia de SQL Server en el servidor B.

El error 18456 también puede producirse por otros motivos. Para obtener más información sobre las diversas causas y sus resoluciones, consulte MSSQLSERVER_18456.

Métodos para transferir inicios de sesión entre instancias de SQL Server

Para transferir inicios de sesión, use uno de los métodos siguientes, según corresponda para su situación.

Generación de scripts de inicio de sesión en SSMS y restablecimiento de contraseñas en el destino

Puede generar scripts de inicio de sesión en SQL Server Management Studio (SSMS) mediante la opción Generar scripts para una base de datos.

Para generar scripts a través de SSMS en el servidor de origen y restablecer manualmente contraseñas para inicios de sesión de SQL Server en el servidor de destino, siga estos pasos:

  1. Conéctese al servidor A que hospeda el servidor SQL Server de origen.

  2. Expanda el nodo Bases de datos .

  3. Seleccione y mantenga pulsada (o haga clic con el botón derecho sobre) cualquier base de datos de usuario y, a continuación, seleccione Tareas>Generar scripts.

  4. Se abre la página Introducción. Seleccione Siguiente para abrir la página Elegir objetos . Seleccione Crear un script a partir de toda la base de datos y todos los objetos de esta.

  5. Seleccione Siguiente para abrir la página Establecer opciones de scripting.

  6. Seleccione el botón Avanzadas para las opciones de inicio de sesión del script.

  7. En la lista Avanzadas, busque Inicios de sesión de scripts, configure la opción como True y seleccione OK.

  8. Vuelva a Establecer opciones de scripting, en Seleccionar cómo se deben guardar los scripts , seleccione Abrir en la nueva ventana de consulta.

  9. Seleccione Siguiente dos veces y, a continuación, seleccione Finalizar.

  10. Busque la sección en el script que contiene inicios de sesión. Normalmente, el script generado contiene texto con el siguiente comentario al principio de esta sección:

    /* For security reasons the login is created disabled and with a random password. */

    Nota:

    Este comentario indica que los inicios de sesión de autenticación de SQL Server se generan con una contraseña aleatoria y están deshabilitados de forma predeterminada. Debe restablecer la contraseña y volver a habilitar estos inicios de sesión en el servidor de destino.

  11. Aplique el script de inicio de sesión desde el script generado más grande al servidor SQL Server de destino.

  12. Para los inicios de sesión de autenticación de SQL Server, restablezca la contraseña en el servidor SQL Server de destino y vuelva a habilitar esos inicios de sesión.

Transferencia de inicios de sesión y contraseñas mediante sp_help_revlogin

  1. Cree procedimientos almacenados que ayuden a generar los necesarios scripts para transferir credenciales y sus contraseñas. Para ello, conéctese al servidor A mediante SQL Server Management Studio (SSMS) o cualquier otra herramienta cliente y ejecute el siguiente script:

    USE [master]
GO
IF OBJECT_ID('dbo.sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE dbo.sp_hexadecimal
GO
CREATE PROCEDURE dbo.sp_hexadecimal
    @binvalue [varbinary](256)
    ,@hexvalue [nvarchar] (514) OUTPUT
AS
BEGIN
    DECLARE @i [smallint]
    DECLARE @length [smallint]
    DECLARE @hexstring [nchar](16)
    SELECT @hexvalue = N'0x'
    SELECT @i = 1
    SELECT @length = DATALENGTH(@binvalue)
    SELECT @hexstring = N'0123456789ABCDEF'
    WHILE (@i < =  @length)
    BEGIN
        DECLARE @tempint   [smallint]
        DECLARE @firstint  [smallint]
        DECLARE @secondint [smallint]
        SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
        SELECT @firstint = FLOOR(@tempint / 16)
        SELECT @secondint = @tempint - (@firstint * 16)
        SELECT @hexvalue = @hexvalue
            + SUBSTRING(@hexstring, @firstint  + 1, 1)
            + SUBSTRING(@hexstring, @secondint + 1, 1)
        SELECT @i = @i + 1
    END
END
GO
IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE dbo.sp_help_revlogin
GO
CREATE PROCEDURE dbo.sp_help_revlogin
    @login_name [sysname] = NULL
AS
BEGIN
    DECLARE @name                  [sysname]
    DECLARE @type                  [nvarchar](1)
    DECLARE @hasaccess             [int]
    DECLARE @denylogin             [int]
    DECLARE @is_disabled           [int]
    DECLARE @PWD_varbinary         [varbinary](256)
    DECLARE @PWD_string            [nvarchar](514)
    DECLARE @SID_varbinary         [varbinary](85)
    DECLARE @SID_string            [nvarchar](514)
    DECLARE @tmpstr                [nvarchar](4000)
    DECLARE @is_policy_checked     [nvarchar](3)
    DECLARE @is_expiration_checked [nvarchar](3)
    DECLARE @Prefix                [nvarchar](4000)
    DECLARE @defaultdb             [sysname]
    DECLARE @defaultlanguage       [sysname]
    DECLARE @tmpstrRole            [nvarchar](4000)
    IF @login_name IS NULL
    BEGIN
        DECLARE login_curs CURSOR
        FOR
        SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] not like '##%'
        ORDER BY p.[name]
    END
    ELSE
        DECLARE login_curs CURSOR
        FOR
        SELECT p.[sid],p.[name],p.[type],p.is_disabled,p.default_database_name,l.hasaccess,l.denylogin,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
        FROM sys.server_principals p
        LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
        WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
            AND p.[name] <> 'sa'
            AND p.[name] NOT LIKE '##%'
            AND p.[name] = @login_name
        ORDER BY p.[name]
    OPEN login_curs
    FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
    IF (@@fetch_status = - 1)
    BEGIN
        PRINT '/* No login(s) found for ' + QUOTENAME(@login_name) + N'. */'
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN - 1
    END
    SET @tmpstr = N'/* sp_help_revlogin script
** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
*/'
    PRINT @tmpstr
    WHILE (@@fetch_status <> - 1)
    BEGIN
        IF (@@fetch_status <> - 2)
        BEGIN
            PRINT ''
            SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
            PRINT @tmpstr
            SET @tmpstr = N'IF NOT EXISTS (
    SELECT 1
    FROM sys.server_principals
    WHERE [name] = N''' + @name + N'''
    )
BEGIN'
            PRINT @tmpstr
            IF @type IN ('G','U') -- NT-authenticated Group/User
            BEGIN -- NT authenticated account/group 
                SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
    FROM WINDOWS
    WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
            END
            ELSE
            BEGIN -- SQL Server authentication
                -- obtain password and sid
                SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
    WITH PASSWORD = ' + @PWD_string + N' HASHED
        ,SID = ' + @SID_string + N'
        ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
        ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)

                IF @is_policy_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_POLICY = ' + @is_policy_checked
                END

                IF @is_expiration_checked IS NOT NULL
                BEGIN
                    SET @tmpstr = @tmpstr + N'
        ,CHECK_EXPIRATION = ' + @is_expiration_checked
                END
            END
            IF (@denylogin = 1)
            BEGIN -- login is denied access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
            END
            ELSE IF (@hasaccess = 0)
            BEGIN -- login exists but does not have access
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
            END
            IF (@is_disabled = 1)
            BEGIN -- login is disabled
                SET @tmpstr = @tmpstr
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                    + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
            END
            SET @Prefix =
                NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
            SET @tmpstrRole = N''
            SELECT @tmpstrRole = @tmpstrRole
                + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
            FROM (
                SELECT
                    SUSER_SNAME([sid])AS LoginName
                    ,sysadmin
                    ,securityadmin
                    ,serveradmin
                    ,setupadmin
                    ,processadmin
                    ,diskadmin
                    ,dbcreator
                    ,bulkadmin
                FROM sys.syslogins
                WHERE (    sysadmin <> 0
                        OR securityadmin <> 0
                        OR serveradmin <> 0
                        OR setupadmin <> 0
                        OR processadmin <> 0
                        OR diskadmin <> 0
                        OR dbcreator <> 0
                        OR bulkadmin <> 0
                        )
                    AND [name] = @name
                ) L
            IF @tmpstr <> '' PRINT @tmpstr
            IF @tmpstrRole <> '' PRINT @tmpstrRole
            PRINT 'END'
        END
        FETCH NEXT FROM login_curs INTO @SID_varbinary,@name,@type,@is_disabled,@defaultdb,@hasaccess,@denylogin,@defaultlanguage
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
END

    Nota:

    Este script crea dos procedimientos almacenados en la base de datos maestra. Los procedimientos se denominan sp_hexadecimal y sp_help_revlogin.

  2. En el editor de consultas de SSMS, seleccione la opción Resultados en texto .

  3. Ejecute la siguiente instrucción en la misma ventana de consulta o en otra diferente:

    EXEC sp_help_revlogin

  4. El script de salida que genera el procedimiento almacenado sp_help_revlogin es el script de inicio de sesión. Este script de inicio de sesión crea los inicios de sesión que contienen el identificador de seguridad (SID) y la contraseña originales.

  5. Revise y siga la información de la sección Consideraciones adicionales al transferir inicios de sesión de SQL Server antes de continuar con los pasos de implementación en el servidor de destino.

  6. Una vez completados los pasos aplicables de la sección Consideraciones adicionales al transferir SQL Server inicios de sesión, conéctese al servidor de destino B mediante cualquier herramienta de cliente (como SSMS).

  7. Ejecute el script generado como resultado de sp_helprevlogin desde el servidor A.

Consideraciones adicionales al transferir inicios de sesión de SQL Server

Revise la siguiente información antes de ejecutar el script de salida en la instancia del servidor B:

Comprender el hash de contraseñas en traspasos de inicios de sesión de SQL Server

SQL Server aplica funciones hash a las contraseñas de las siguientes maneras:

  • VERSION_SHA1: usa el algoritmo SHA1. SQL Server 2000 hasta SQL Server 2008 R2 usan este hash. Estas versiones están fuera de la compatibilidad estándar y extendida, por lo que debe encontrar VERSION_SHA1 hashes solo al migrar de instancias heredadas.
  • VERSION_SHA2: usa el algoritmo SHA2-512. SQL Server 2012 y versiones posteriores, incluidas las versiones admitidas actualmente, usan este hash.

El script de salida crea los inicios de sesión mediante la contraseña cifrada. El HASHED argumento de la instrucción CREATE LOGIN provoca este comportamiento. Este argumento indica que la contraseña especificada después del argumento PASSWORD ya está hasheada.

Controlar los cambios de dominio durante las transferencias de inicio de sesión de SQL Server

Si los servidores de origen y destino están en dominios diferentes, revise cuidadosamente el script de salida. Cambie el script para reemplazar el nombre de dominio original por el nuevo nombre de dominio en las CREATE LOGIN instrucciones . Los inicios de sesión integrados concedidos al nuevo dominio no comparten el mismo SID que los inicios de sesión en el dominio original, por lo que los usuarios se vuelven huérfanos de estos inicios de sesión. Para corregir usuarios huérfanos, consulte Solución de problemas de usuarios huérfanos (SQL Server) y ALTER USER.

Si el servidor A y el servidor B están en el mismo dominio, se usa el mismo SID. Por lo tanto, los usuarios no están huérfanos.

Permisos necesarios para ver y seleccionar inicios de sesión de SQL Server

De forma predeterminada, solo los miembros del rol fijo de servidor sysadmin pueden ejecutar una instrucción SELECT sobre la vista sys.server_principals. A menos que un administrador del sistema conceda los permisos necesarios a otros usuarios, esos usuarios no pueden crear ni ejecutar el script de salida.

La configuración predeterminada de la base de datos no se convierte en script ni se transfiere

Los pasos de este artículo no transfieren la información de base de datos predeterminada para un inicio de sesión determinado. Esta limitación existe porque es posible que la base de datos predeterminada no exista siempre en el servidor B. Para definir la base de datos predeterminada para un inicio de sesión, use la instrucción ALTER LOGIN pasando el nombre de inicio de sesión y la base de datos predeterminada como argumentos.

Administrar las diferencias en el orden de clasificación durante las transferencias de inicios de sesión en SQL Server

Los servidores de origen y destino pueden tener diferentes órdenes de ordenación o pueden usar el mismo criterio de ordenación. Este es el modo en que puede abordar cada escenario:

  • Servidor A insensible a mayúsculas y servidor B sensible a mayúsculas: el criterio de ordenación del servidor A es insensible a mayúsculas y el criterio de ordenación del servidor B es sensible a mayúsculas. En este caso, los usuarios deben escribir todas las letras de las contraseñas en mayúsculas una vez que se hayan transferido los inicios de sesión y las contraseñas a la instancia del servidor B.

  • Servidor sensible a mayúsculas A y servidor insensible a mayúsculas B: El criterio de ordenación del servidor A es sensible a mayúsculas, y el criterio de ordenación del servidor B es insensible a mayúsculas. En este caso, los usuarios no pueden iniciar sesión con los inicios de sesión y las contraseñas que se transfieren a la instancia en el servidor B a menos que se cumpla una de las condiciones siguientes:

    • Las contraseñas originales no contienen letras.
    • Todas las letras de las contraseñas originales son mayúsculas.

  • Distingue entre mayúsculas y minúsculas o no distingue entre mayúsculas y minúsculas en ambos servidores: el criterio de ordenación del servidor A y del servidor B distingue entre mayúsculas y minúsculas, o el criterio de ordenación del servidor A y del servidor B no distingue entre mayúsculas y minúsculas. En estos casos, los usuarios no experimentan un problema.

Corrección de conflictos con inicios de sesión existentes en el servidor de destino

El script comprueba si el inicio de sesión existe en el servidor de destino y crea un inicio de sesión solo si no existe. Sin embargo, si recibe el siguiente mensaje de error al ejecutar el script de salida en la instancia del servidor B, debe resolver manualmente el conflicto siguiendo los pasos de esta sección.

Msj 15025, Nivel 16, Estado 1, Línea 1
La entidad de seguridad de servidor 'MyLogin' ya existe.

Del mismo modo, un inicio de sesión que ya está en la instancia del servidor B podría tener un SID que sea el mismo que un SID en el script de salida. En este caso, recibirá el mensaje de error siguiente al ejecutar el script de salida en la instancia del servidor B:

Msj 15433, Nivel 16, Estado 1, Línea 1 El parámetro sid proporcionado está en uso.

Para corregir el conflicto manualmente, siga estos pasos:

  1. Revise atentamente el script de salida.
  2. Examine el contenido de la sys.server_principals vista en la instancia del servidor B.
  3. Realice la acción adecuada para cada error, como quitar o cambiar el nombre del inicio de sesión en conflicto en el servidor B o quitar la instrucción duplicada CREATE LOGIN del script de salida antes de volver a ejecutarlo.

En SQL Server, el SID de un inicio de sesión rige el acceso de nivel de base de datos. Un inicio de sesión puede tener diferentes SID cuando se asigna a los usuarios de distintas bases de datos, lo que puede ocurrir si combina manualmente bases de datos de distintos servidores. En ese caso, el inicio de sesión solo podrá usar la base de datos en la que el SID de la entidad de seguridad de la base de datos coincida con el SID en la vista sys.server_principals. Para solucionar este problema, quite el usuario de la base de datos que tiene el SID no coincidente mediante la instrucción DROP USER . A continuación, vuelva a agregar el usuario con la instrucción CREATE USER y asócielo al inicio de sesión correcto (elemento de seguridad del servidor).

Para obtener más información sobre las entidades de seguridad de servidor y base de datos, vea CREATE USER y CREATE LOGIN.

Escenarios avanzados y solución de problemas

Si sigue viendo errores de inicio de sesión después de transferir inicios de sesión, compruebe los siguientes elementos:

Contenido relacionado

  • Last updated on