SQL-Accounts migrieren
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:
- Wir haben zwei SQL-Server: ServerAlt (hier liegt die Datenbank „Dummy“) & ServerNeu (hier soll die Datenbank „Dummy“ hin)
- Je nach Konfiguration kann man sich auf einem SQL-Server mittels zwei Arten anmelden:
- SQL-Authentication: Die Accounts werden je SQL-Server verwaltet
- 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.
- 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“.
- 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“.
- 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:
- Zwei hilfreiche Store Procedures auf „ServerAlt“ einrichten
- Mithilfe der Store Procedure die Accounts von „ServerAlt“ inkl. der SID auslesen und „Account-Erstellungs-SQL-Code“ erzeugen
- 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
GOUSE [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
GO2. 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_revloginIn 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
ENDDie beiden SIDs (oben „ServerAlt“ & unten „ServerNeu“) im direkten Vergleich:
0x169C961D1066E0439EB1E78505B2D8CF
0xBF4D88FA40FE7446A9595C18CCF9825B3. 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