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

Resolution

Create an SQL login on the primary instance and synchronize the login with a read replica

Database engine instances authenticate SQL server logins and store the login passwords in the primary database. You can map SQL logins to a database user in one database or to different users in different databases. Logins and users generate a unique statement ID (SID). When you synchronize logins on the read replica, make sure that the login SID matches the SID on the primary instance.

To create an SQL login on the primary instance and synchronize the login with a read replica, complete the following steps:

1. Run the following query on the primary instance to create an SQL Server login that's named testlogin:

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

2. Run the following query on the primary instance to confirm that you created testlogin on the primary instance:

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

3. Run the following query on the primary instance to find the SID that maps to testlogin:

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

   Example output:

   name                           sid  
   -------------------------------------------------------------------------------  
   testlogin                      0xEBF23823BDEAED42823C76C8706F4B6B

4. Run the following query on the read replica to create a new login that's named testlogin that maps to the SID from the primary instance:

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

5. Run the following query on the read replica to confirm that you created the new login with the same SID as the primary instance:

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

Create a Windows login on a primary instance and read replica

Windows authenticated SQL server logins are instance-level logins where the operating system (OS) manages the authentication. You can map Windows logins to a user's AWS account, a local security group, or a domain account.

To create a Windows login on the primary instance and read replica, complete the following steps:

1. Run the following query on the primary instance to create a windows login that's named SQLAD\user1:

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

2. Run the following command on the read replica to create a login that's named SQLAD\user1:

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

Note: Amazon RDS doesn't provide high-level permissions on a primary database. So, you can't use the sp_help_revlogin or sp_hexadecimal store procedures that Microsoft provides to generate logins in Amazon RDS.