How do I create and synchronize SQL and Windows logins on Amazon RDS for SQL Server read replica?

Resolution

Use the following steps to create, confirm, and synchronize the SQL Server and Windows logins on the master instance:

SQL logins

SQL server logins are authenticated by database engine instances and the login passwords are stored in the master database. SQL logins can be mapped to a database user in one database or different users in different databases. Logins and users generate a unique statement ID (SID). This means that when synchronizing logins on read replica, you must make sure that the Login SID matches the SID on the master instance.

Create SQL login on master instance and synchronize with read replica

1.    On the master instance create a SQL Server login named "testlogin".

USE [master]
GO
CREATE LOGIN [testlogin] WITH PASSWORD=N'Pa$$word';

2.    Confirm that the login "testlogin" is created on the master instance.

SELECT name FROM sys.sql_logins WHERE name = 'testlogin';

3.    On the master instance, find the SID mapped to the login "testlogin".

select name, sid  FROM sys.sql_logins WHERE name = 'testlogin'
name                                         sid
-------------------------------------------------------------------------------
testlogin                      0xEBF23823BDEAED42823C76C8706F4B6B

4.    On the read replica, create a new login "testlogin" mapped to the SID from the master instance.

CREATE Login testlogin  WITH password = ' Pa$$word', SID = 0xEBF23823BDEAED42823C76C8706F4B6B

5.    Confirm that the new login is created with matching SID.

SELECT name, sid  FROM sys.sql_logins WHERE name = 'testlogin'

Windows logins

Windows-authenticated SQL server logins are instance-level logins where the operating system handles the authentication. Windows logins can be mapped to a user account, local security group, or a domain account.

Create Windows login on master instance and read replica

To create a Windows login on the master instance and read replica, do the following:

1.    On the master instance, create a windows login ‘[SQLAD\user1]’.

CREATE LOGIN [SQLAD\user1] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

2.    On read replica, create login ‘[SQLAD\user1]’ using the same command. Unlike a SQL login, Windows-authenticated SQL logins don't require a SID to be mapped.

CREATE LOGIN [SQLAD\user1] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

On Amazon RDS, it's not possible to use Microsoft-provided store procedures "sp_help_revlogin" and "sp_hexadecimal" to generate logins. This is because Amazon RDS doesn't provide elevated privileges on a master database.

---