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

En este artículo se describe cómo transferir los inicios de sesión y las contraseñas entre las distintas instancias de SQL Server que se ejecutan en Windows.

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

Introducción

En este artículo se describe cómo transferir los inicios de sesión y las contraseñas entre las distintas instancias de Microsoft SQL Server.

Nota

Las instancias pueden estar en el mismo servidor o en servidores diferentes, y sus versiones pueden ser diferentes.

Más información

En este artículo, el servidor A y el servidor B son servidores diferentes.

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

Error de inicio de sesión del usuario ' mi usuario'. (Microsoft SQL Server, error: 18456)

Este problema se produce porque no ha transferido los inicios de sesión y las contraseñas de la instancia de SQL Server en el servidor a a la instancia de SQL Server en el servidor B.

Nota

El mensaje de error 18456 también se produce debido a otros motivos. Para obtener más información sobre estas causas y MSSQLSERVER_18456revisión de posibles soluciones.

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

  • Método 1: restablecer la contraseña en el equipo con SQL Server de destino (servidor B)

    Para resolver este problema, restablezca la contraseña en el equipo de SQL Server y, a continuación, haga una secuencia de comandos para el inicio de sesión.

    Nota

    El algoritmo de hash de contraseña se usa al restablecer la contraseña.

  • Método 2: transferir inicios de sesión y contraseñas al servidor de destino (servidor B) mediante scripts generados en el servidor de origen (servidor A)

    1. Crear procedimientos almacenados que le ayuden a generar los scripts necesarios para transferir inicios de sesión y sus contraseñas. Para ello, conéctese al servidor a con SQL Server Management Studio (SSMS) o a cualquier otra herramienta de cliente y ejecute el siguiente script:

      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 (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
      
      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 @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 CURSOR 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 CURSOR 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
      

      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 a texto .

    3. Ejecute la siguiente instrucción en la misma ventana de consulta o en una nueva:

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

Importante

Revise la información de la sección comentarios que aparece a continuación antes de continuar con la implementación de los pasos en el servidor de destino.

Pasos en el servidor de destino (servidor B)

Conéctese al servidor B con una herramienta de cliente (como SSMS) y, a continuación, ejecute el script generado en el paso 4 (resultado del sp_helprevlogin ) desde el servidor A.

Comentarios

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

  • Se puede aplicar un algoritmo hash A una contraseña de las siguientes maneras:

    • VERSION_SHA1: Este hash se genera mediante el algoritmo SHA1 y se usa en SQL Server 2000 a través de SQL Server 2008 R2.
    • VERSION_SHA2: Este hash se genera mediante el algoritmo SHA2 512 y se usa en SQL Server 2012 y versiones posteriores.
  • Revise cuidadosamente el script de salida. Si el servidor A y el servidor B están en dominios diferentes, debe cambiar el script de salida. A continuación, tiene que reemplazar el nombre de dominio original con el nuevo nombre de dominio en las instrucciones CREATE LOGIN. Los inicios de sesión integrados que tienen concedido el acceso en el nuevo dominio no tienen el mismo SID que los inicios de sesión en el dominio original. Por lo tanto, los usuarios quedan huérfanos de estos inicios de sesión. Para obtener más información acerca de cómo resolver estos usuarios huérfanos, vea Cómo resolver problemas de permisos cuando se mueve una base de datos entre servidores que ejecutan SQL Server.

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

  • En el script de salida, los inicios de sesión se crean con la contraseña cifrada. Esto se debe al argumento de HASH de la CREATE LOGIN instrucción. Este argumento especifica que la contraseña que se escribe después del argumento de contraseña ya tiene un algoritmo hash.

  • De forma predeterminada, solo un miembro de la función fija de servidor sysadmin puede ejecutar una SELECT instrucción de la sys.server_principals vista. A menos que un miembro del rol fijo de servidor sysadmin conceda los permisos necesarios a los usuarios, los usuarios no pueden crear ni ejecutar el script de salida.

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

  • Criterios de ordenación en los servidores de origen y de destino:

    • Servidor b sin distinción entre mayúsculas y minúsculas y servidor b: el criterio de ordenación del servidor A puede no distinguir mayúsculas de minúsculas, y el criterio de ordenación del servidor B puede distinguir entre mayúsculas y minúsculas. En este caso, los usuarios deben escribir las contraseñas en letras mayúsculas después de transferir los inicios de sesión y las contraseñas a la instancia del servidor B.

    • Servidor B con distinción entre mayúsculas y minúsculas a y sin distinción de mayúsculas y minúsculas: El criterio de ordenación del servidor A puede distinguir mayúsculas de minúsculas, y el criterio de ordenación del servidor B puede no distinguir mayúsculas de minú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 del servidor B, a menos que se cumpla una de las siguientes condiciones:

      • Las contraseñas originales no contienen letras.
      • Todas las letras de las contraseñas originales son mayúsculas.
    • Distinción de 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 puede distinguir mayúsculas de minúsculas, o bien, el criterio de ordenación del servidor a y del servidor b no distingue mayúsculas de minúsculas. En estos casos, los usuarios no experimentan ningún problema.

  • Un inicio de sesión que ya está en la instancia del servidor B puede tener un nombre que sea el mismo que el de la secuencia de comandos de salida. En este caso, recibirá el siguiente mensaje de error cuando ejecute el script de salida en la instancia del servidor B:

    Msj 15025, nivel 16, estado 1, línea 1
    La entidad de servidor ' mi Inicio de sesión' ya existe.

    De forma similar, un inicio de sesión que ya se encuentra en la instancia del servidor B puede tener un SID que sea igual a un SID en el script de salida. En este caso, recibirá el siguiente mensaje de error cuando ejecute el script de salida en la instancia del servidor B:

    MSG 15433, nivel 16, estado 1, línea 1 el SID del parámetro proporcionado está en uso.

    Por lo tanto, debe hacer lo siguiente:

    1. Revise cuidadosamente el script de salida.

    2. Examine el contenido de la vista de sys.server_principals en la instancia del servidor B.

    3. Resuelva estos mensajes de error según corresponda.

      En SQL Server 2005, el SID de un inicio de sesión se usa para implementar el acceso a nivel de base de datos. Un inicio de sesión puede tener diferentes SID en bases de datos diferentes en un servidor. En este caso, el inicio de sesión solo puede tener acceso a la base de datos que tiene el SID que coincide con el SID de la sys.server_principals vista. Este problema puede producirse si las dos bases de datos se combinan desde distintos servidores. Para resolver este problema, quite manualmente el inicio de sesión de la base de datos que tiene un SID que no coincide con la instrucción DROP USER. A continuación, vuelva a agregar el inicio de sesión con la CREATE USER instrucción.

Referencias