Trasferire gli account di accesso e le password tra istanze di SQL Server

In questo articolo viene descritto come trasferire gli account di accesso e le password tra istanze diverse di SQL Server in esecuzione su Windows.

Versione originale del prodotto:   SQL Server
Numero KB originale:   918992

Introduzione

In questo articolo viene descritto come trasferire gli account di accesso e le password tra istanze diverse di Microsoft SQL Server.

Nota

Le istanze possono trovarsi sullo stesso server o su server diversi e le relative versioni potrebbero essere diverse.

Ulteriori informazioni

In questo articolo, il server A e il server B sono server diversi.

Dopo aver spostato un database dall'istanza di SQL Server nel server a all'istanza di SQL Server nel server B, è possibile che gli utenti non siano in grado di accedere al database nel server B. Inoltre, gli utenti possono ricevere il seguente messaggio di errore:

Accesso non riuscito per l'utente "utente". (Microsoft SQL Server, errore: 18456)

Questo problema si verifica perché non sono stati trasferiti gli account di accesso e le password dall'istanza di SQL Server nel server a all'istanza di SQL Server nel server B.

Nota

Il messaggio di errore 18456 si verifica anche a causa di altri motivi. Per ulteriori informazioni su queste cause e sulla possibile revisione delle risoluzioni MSSQLSERVER_18456.

Per trasferire gli account di accesso, utilizzare uno dei metodi seguenti, in base alle proprie esigenze.

  • Metodo 1: reimpostare la password nel computer SQL Server di destinazione (server B)

    Per risolvere il problema, reimpostare la password nel computer SQL Server e quindi eseguire lo script su account di accesso.

    Nota

    L'algoritmo di hashing della password viene utilizzato quando si reimposta la password.

  • Metodo 2: trasferire gli account di accesso e le password nel server di destinazione (server B) utilizzando gli script generati nel server di origine (server A)

    1. Creare stored procedure che consentano di generare script necessari per trasferire gli account di accesso e le rispettive password. A tale scopo, connettersi al server A mediante SQL Server Management Studio (SSMS) o a qualsiasi altro strumento client ed eseguire lo script seguente:

      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

      Questo script consente di creare due stored procedure nel database master. Le procedure sono denominate sp_hexadecimal e sp_help_revlogin .

    2. Nell'editor di query di SSMS selezionare l'opzione Risultati in testo .

    3. Eseguire l'istruzione seguente nello stesso o in una nuova finestra di query:

      EXEC sp_help_revlogin
      
    4. Lo script di output che la sp_help_revlogin stored procedure genera è lo script di accesso. Questo script di accesso consente di creare gli account di accesso con l'ID di sicurezza (SID) originale e la password originale.

Importante

Leggere le informazioni riportate nella sezione osservazioni seguente prima di procedere con l'implementazione dei passaggi nel server di destinazione.

Passaggi sul server di destinazione (server B)

Connettersi al server B utilizzando uno strumento client (come SSMS) e quindi eseguire lo script generato nel passaggio 4 (output of sp_helprevlogin ) dal server A.

Osservazioni

Leggere le informazioni seguenti prima di eseguire lo script di output nell'istanza nel server B:

  • È possibile eseguire l'hashing di una password nei modi seguenti:

    • VERSION_SHA1: Questo hash viene generato utilizzando l'algoritmo SHA1 e viene utilizzato in SQL Server 2000 tramite SQL Server 2008 R2.
    • VERSION_SHA2: Questo hash viene generato utilizzando l'algoritmo SHA2 512 e viene utilizzato in SQL Server 2012 e versioni successive.
  • Esaminare attentamente lo script di output. Se il server A e il server B sono in domini diversi, è necessario modificare lo script di output. Successivamente, è necessario sostituire il nome di dominio originale utilizzando il nuovo nome di dominio nelle istruzioni CREATE LOGIN. Gli account di accesso integrati a cui viene concesso l'accesso nel nuovo dominio non dispongono dello stesso SID degli account di accesso nel dominio originale. Gli utenti sono pertanto orfani da questi account di accesso. Per ulteriori informazioni su come risolvere tali utenti orfani, vedere come risolvere i problemi di autorizzazione quando si sposta un database tra i server che eseguono SQL Server.

    Se il server A e il server B sono nello stesso dominio, viene utilizzato lo stesso SID. Pertanto, è improbabile che gli utenti siano orfani.

  • Nello script di output, gli account di accesso vengono creati utilizzando la password crittografata. Ciò si verifica a causa dell'argomento HASH nell' CREATE LOGIN istruzione. Questo argomento specifica che la password immessa dopo l'argomento PASSWORD è già stata sottoposta a hash.

  • Per impostazione predefinita, solo un membro del ruolo predefinito del server sysadmin può eseguire un' SELECT istruzione dalla sys.server_principals visualizzazione. A meno che un membro del ruolo predefinito del server sysadmin conceda le autorizzazioni necessarie per gli utenti, gli utenti non possono creare o eseguire lo script di output.

  • La procedura descritta in questo articolo non trasferisce le informazioni predefinite del database per un determinato account di accesso. Ciò è dovuto al fatto che il database predefinito potrebbe non esistere sempre nel server B. Per definire il database predefinito per un account di accesso, utilizzare l' ALTER LOGIN istruzione passando il nome dell'account di accesso e il database predefinito come argomenti.

  • Ordinare gli ordini nei server di origine e di destinazione:

    • Server senza distinzione tra maiuscole e minuscole server B: il tipo di ordinamento del server A può essere senza distinzione tra maiuscole e minuscole e il tipo di ordinamento del server B può essere con distinzione tra maiuscole e minuscole. In questo caso, gli utenti devono digitare le password in tutte le lettere maiuscole dopo aver trasferito gli account di accesso e le password all'istanza nel server B.

    • Server con distinzione tra maiuscole e minuscole server a e senza distinzione tra maiuscole e minuscole: L'ordinamento del server A può essere con distinzione tra maiuscole e minuscole e il tipo di ordinamento del server B può essere senza distinzione tra maiuscole e minuscole. In questo caso, gli utenti non possono accedere utilizzando gli account di accesso e le password che si trasferiscono nell'istanza nel server B, a meno che non si verifica una delle condizioni seguenti:

      • Le password originali non contengono lettere.
      • Tutte le lettere nelle password originali sono lettere maiuscole.
    • Distinzione tra maiuscole e minuscole in entrambi i server: l'ordinamento del server a e del server b può essere distinzione tra maiuscole e minuscole oppure il tipo di ordinamento del server a e del server b può essere senza distinzione tra maiuscole e minuscole. In questi casi, gli utenti non avvertono un problema.

  • Un account di accesso già presente nell'istanza sul server B può avere un nome identico a quello di un nome nello script di output. In questo caso, quando si esegue lo script di output sull'istanza nel server B, viene visualizzato il messaggio di errore seguente:

    Msg 15025, livello 16, stato 1, linea 1
    L'entità server 'login' esiste già.

    Analogamente, un account di accesso già presente nell'istanza sul server B può avere un SID identico a quello di un SID nello script di output. In questo caso, quando si esegue lo script di output sull'istanza nel server B, viene visualizzato il messaggio di errore seguente:

    Msg 15433, Level 16, state 1, è in uso il SID del parametro line 1 fornito.

    Pertanto, è necessario eseguire le operazioni seguenti:

    1. Esaminare attentamente lo script di output.

    2. Esaminare il contenuto della visualizzazione sys.server_principals nell'istanza nel server B.

    3. Indirizzare i messaggi di errore in base alle esigenze.

      In SQL Server 2005, il SID per un account di accesso viene utilizzato per implementare l'accesso a livello di database. Un account di accesso può avere SID diversi in database diversi in un server. In questo caso, l'account di accesso può accedere solo al database con il SID che corrisponde al SID nella sys.server_principals visualizzazione. Questo problema può verificarsi se i due database vengono combinati da server diversi. Per risolvere il problema, rimuovere manualmente l'account di accesso dal database con una mancata corrispondenza di SID utilizzando l'istruzione DROP USER. Quindi, aggiungere di nuovo l'account di accesso utilizzando l' CREATE USER istruzione.

Riferimenti