Microsoft SQL Server Logo

SQL-Accounts migrieren

Anwendungen Microsoft

Letztens musste ich im Büro eine SQL-Datenbank umziehen. Die Applikation, die diese SQL-Datenbank verwendet, hat sich bisher mittels eines SQL-Accounts auf der Datenbank angemeldet. Nun kann man zwar auf dem neuen Server einen neuen Account anlegen und diese auch identisch benennen (Bspw. „Hugo“), allerdings ist das auf beiden Servern unterschiedliche Accounts, denn die automatisch generierte SID (= eindeutige BenutzerID) ist unterschiedlich und damit kann selbst bei selbem Namen der „Hugo“ von Server B nicht auf die Datenbank zugreifen, obwohl dort „Hugo“ durch den Datenbank-Umzug hinterlegt ist.

Klingt kompliziert?!

Also die Ausgangslage ist:

  1. Wir haben zwei SQL-Server: ServerAlt (hier liegt die Datenbank „Dummy“) & ServerNeu (hier soll die Datenbank „Dummy“ hin)
  2. Je nach Konfiguration kann man sich auf einem SQL-Server mittels zwei Arten anmelden:
    1. SQL-Authentication: Die Accounts werden je SQL-Server verwaltet
    2. Windows-Authentication: Die Accounts werden auf dem Windows-Betriebssystem oder im Active Directory (bessere Wahl) verwaltet

Bei der SQL-Authentication erhält jeder Account auf dem SQL-Server eine eindeutige Kennung (die sog. „SID“), bei der Windows-Authentication gibt es diese SID bereits im Betriebssystem bzw. im Active Directory und wird in den SQL-Server übernommen.

  1. Es gibt auf ServerAlt den SQL-Account „Hugo“, welcher auf die Datenbank „Dummy“ berechtigt ist. Da der ServerAlt schon länger in Betrieb ist, hat „Hugo“ bspw. die SID „1248“.
  2. Die Datenbank „Dummy“ wird mittels Backup & Restore auf den ServerNeu übertragen. Somit ist „Hugo“ zwar noch in der Datenbank vorhanden, aber es gibt für ihn kein Loginobjekt (= Account) auf dem SQL-Server „ServerNeu“. Dieses kann angelegt werden, allerdings da „ServerNeu“ erst seit kurzem in Betrieb ist, bekommt „Hugo“ dort bspw. die SID „384“.
  3. Will man nun „Hugo“ von „ServerNeu“ auf „Dummy“ von „ServerNeu“ berechtigen, erhält man die Fehlermeldung, dass der User dort schon existiert. Aber alle Anmeldeversuchen schlagen dennoch fehl. Das kommt daher, dass „Hugo“ in „Dummy“ die SID „1248“ hat und nicht SID „384“.

Lösung: Man muss exakt den „Hugo“ von „ServerAlt“ rüber nach „ServerNeu“ bekommen.

Lösungsweg

Auf die Lösung bin ich durch Zuhilfe der Microsoft Docs gekommen:

  1. Zwei hilfreiche Store Procedures auf „ServerAlt“ einrichten
  2. Mithilfe der Store Procedure die Accounts von „ServerAlt“ inkl. der SID auslesen und „Account-Erstellungs-SQL-Code“ erzeugen
  3. Accounts auf „ServerNeu“ anhand des generierten SQL-Codes anlegen

1. Hilfreiche Store Procedures

Im SSMS (= SQL Management Studio) eine neue Verbindung zum „ServerAlt“ aufbauen und diesen SQL-Coder ausführen:

USE [master]
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE [dbo].[sp_hexadecimal]
(
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
)
AS
BEGIN
    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
END
GO
USE [master]
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin]  
(
    @login_name sysname = NULL
)
AS
BEGIN
    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 @Prefix                   VARCHAR(255)
    DECLARE @defaultdb                SYSNAME
    DECLARE @tmpstrRole               VARCHAR (1024)
 
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
        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'
        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
            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
            ORDER BY p.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
 
                SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
                BEGIN'
                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
 
        Set @Prefix = '
        exec master.dbo.sp_addsrvrolemember @loginame='''
 
        Set @tmpstrRole=''
 
        Select @tmpstrRole = @tmpstrRole
            + Case When sysadmin        = 1 Then @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        Else '' End
            + Case When securityadmin   = 1 Then @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   Else '' End
            + Case When serveradmin     = 1 Then @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     Else '' End
            + Case When setupadmin      = 1 Then @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      Else '' End
            + Case When processadmin    = 1 Then @Prefix + [LoginName] + ''', @rolename=''processadmin'''    Else '' End
            + Case When diskadmin       = 1 Then @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       Else '' End
            + Case When dbcreator       = 1 Then @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       Else '' End
            + Case When bulkadmin       = 1 Then @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       Else '' End
          From (
                    select convert(varchar(100),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
 
            PRINT @tmpstr
            Print @tmpstrRole
            Print 'END'
        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
END
GO

2. Accounts auslesen

Eine neue Query öffnen und Verbindung zu ServerAlt herstellen, danach muss im SSMS der Ergebnismodus umgestellt werden. Hierzu im Menü auf „Query > Results To“ klicken und dort „Results to Text“ auswählen:

Anschließend mithilfe der Stored Procedure „EXEC sp_help_revlogin“ die Accounts auslesen:

EXEC sp_help_revlogin

In meinem Beispiel habe ich vorher auf beiden Servern einen „Hugo“ angelegt, um jetzt die beiden unterschiedlichen SIDs darzustellen:

-- Login: Hugo
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Hugo')
                BEGIN
CREATE LOGIN [Hugo] WITH PASSWORD = 0x020031B716A8F484BDF3E316F9E0C5125AC1227625A4D0B8723B9ECCFF2D00614CF22678C05714BFB2C3EEF8D65F20938B391D7CF9842B0C9034620F01B636149841DB19321F HASHED, SID = 0x169C961D1066E0439EB1E78505B2D8CF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
 
END
-- Login: Hugo
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Hugo')
                BEGIN
CREATE LOGIN [Hugo] WITH PASSWORD = 0x0200DFDAC971497F6326DEBB7A8D1CD1F30D3CEFDC9504BE086099B8B0FC5DA10C08C66FACE3AD1B2A21D99A7B99930B90CAE7203DE98750493C5D328A36E9DAA4D46F56131A HASHED, SID = 0xBF4D88FA40FE7446A9595C18CCF9825B, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
 
END

Die beiden SIDs (oben „ServerAlt“ & unten „ServerNeu“) im direkten Vergleich:

0x169C961D1066E0439EB1E78505B2D8CF
0xBF4D88FA40FE7446A9595C18CCF9825B

3. Account anlegen

Nun nimmt man den Bereich des Benutzers „Hugo“ und führt ihn auf „ServerNeu“ aus. Achtung: Es darf natürlich keinen Account mit dem selben Namen geben, d.h. ich muss den „Hugo“ von „ServerNeu“ aus meinem Beispiel erstmal wieder löschen, bevor ich ihn mit dem auf „ServerAlt“ generierten SQL-Code erneut anlegen kann:

-- Login: Hugo
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Hugo')
                BEGIN
CREATE LOGIN [Hugo] WITH PASSWORD = 0x020031B716A8F484BDF3E316F9E0C5125AC1227625A4D0B8723B9ECCFF2D00614CF22678C05714BFB2C3EEF8D65F20938B391D7CF9842B0C9034620F01B636149841DB19321F HASHED, SID = 0x169C961D1066E0439EB1E78505B2D8CF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
 
END

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.