Migrating SQL Server logins and user from RDS instance to another server

Blogs

MongoDB – Sharding
July 17, 2024
Web Scraping with Python and Beautiful Soup
July 22, 2024

Migrating SQL Server logins and user from RDS instance to another server

Problem statement

Migrate all the logins present in RDS instance to another instance with same privileges and access for every user in the database.

Introduction

Businesses are constantly adopting new technology to improve their performance and to streamline their operations. Data migration can increase an organization’s competitiveness and overall performance. One of our customers wanted to migrate their database from RDS to EC2 instance (AWS VM). We took the backup and restored it in the destination and the incremental data was replicated from source to destination continuously on the other hand we started to migrate the logins form RDS to EC2 instance.

The Steps we followed to migrate the logins are:

Step 1:

Create the Microsoft SP sp_hexadecimal in master database to generate the create logins script with same password, when we tried to create the SP in the master database in RDS instance we got the below error because we can’t create any objects in the system databases.

"Msg 262, Level 14, State 18, Procedure sp_hexadecimal, Line 1 [Batch Start Line 5]CREATE PROCEDURE permission denied in database 'master'"

Step 2:
As we can’t create any objects in the system databases, we created this SP in one of the users created database and generated the create login script by executing the SP. Below this is the sample create login script.

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'user1')
BEGIN
CREATE LOGIN [user1] WITH PASSWORD = '', SID = 0xFDBAF4220EF3AC43B7E7C195505A80BA,
DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
END

The password is encrypted in the RDS instance we can’t retrieve the password for the users.

Step 3:
We created a function that generates a random password.
if exists(select 1 from sys.views where name='Password' and type='v')
drop view Password;
go
CREATE VIEW Password
AS
SELECT randval = CRYPT_GEN_RANDOM (8)
GO
IF object_id(N'[dbo].[Pass]', N'FN') IS NOT NULL
DROP FUNCTION [dbo].[Pass] GO
CREATE FUNCTION [dbo].[Pass]()
RETURNS VARCHAR(8)
AS
BEGIN
-- Declare the variables here
DECLARE @Result VARCHAR(8)
DECLARE @BinaryData VARBINARY(8)
DECLARE @CharacterData VARCHAR(8)

SELECT @BinaryData = randval
FROM Password

Set @CharacterData=cast ('' as xml).value ('xs:base64Binary(sql:variable("@BinaryData"))',
'varchar (max)')

SET @Result = @CharacterData

-- Return the result of the function
RETURN @Result
END

declare @password varchar(200)
DECLARE @passwordPass VARCHAR(8)
SELECT @passwordPass = dbo.Pass()

set @password = LEFT(@passwordpass,len(@passwordpass)-2) + '*' + RIGHT(@passwordpass,2)
select @password as password

Step 4:
We have merged the sp_hexadecimal and password generator to generate the create login script for every user with new random password
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
if exists(select 1 from sys.views where name='Password' and type='v')
drop view Password;
go
CREATE VIEW Password
AS
SELECT randval = CRYPT_GEN_RANDOM (8)
GO
IF object_id(N'[dbo].[Pass]', N'FN') IS NOT NULL
DROP FUNCTION [dbo].[Pass] GO
CREATE FUNCTION [dbo].[Pass]()
RETURNS VARCHAR(8)
AS
BEGIN
-- Declare the variables here
DECLARE @Result VARCHAR(8)
DECLARE @BinaryData VARBINARY(8)
DECLARE @CharacterData VARCHAR(8)

SELECT @BinaryData = randval
FROM Password

Set @CharacterData=cast ('' as xml).value ('xs:base64Binary(sql:variable("@BinaryData"))',
'varchar (max)')

SET @Result = @CharacterData

-- Return the result of the function
RETURN @Result
END
GO

IF OBJECT_ID ('sp_login') IS NOT NULL
DROP PROCEDURE sp_login
GO
CREATE PROCEDURE [dbo].[sp_login] (
@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 @defaultlanguage 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, p.default_language_name
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, p.default_language_name
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, @defaultlanguage
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END

SET @tmpstr = '/* 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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
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

declare @password varchar(200)
DECLARE @passwordPass VARCHAR(8)
SELECT @passwordPass = dbo.GenPass()

set @password = LEFT(@passwordpass,len(@passwordpass)-2) + '*' + RIGHT(@passwordpass,2)
--select @password

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ''' +@password+''', SID = '
+ @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'

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 ( sysadmin0
OR securityadmin0
OR serveradmin0
OR setupadmin 0
OR processadmin 0
OR diskadmin0
OR dbcreator0
OR bulkadmin0
)
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, @defaultlanguage
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
END

exec sp_login

Step 5:
Execute the above created SP and copy the output and run it on the destination. The user’s login will be created and mapped to the database. Users will have the same permissions as per the source database.


Pramodh P

Leave a Reply

Your email address will not be published. Required fields are marked *