Create geo replica Azure SQL with read permissions

Create geo replica Azure SQL with read permissions header image

Sometimes you start a chore only to find out it takes way more time than anticipated 🤷. Recently we wanted to setup replication for a SQL cluster we were working on. However, we required read access to the replica so we could run reporting. The quickest way in Azure SQL was to use replication, something that you can setup in a few clicks.

SQL Replication and read access 🔓

The copy that you get when using replication is a read only copy, but for reporting purposes that is sufficient. All we needed was an account to get read access. In our case the SQL admin account was out of the picture, and we couldn’t rely on Azure Active Directory access. However when you sync or replicate a database the user that you get passed on becomes orphaned.

An orphaned user in this case means that the user is created in the database, but it is not matched to any login on the server. It took me hours to figure that one out, but it does make some sense. You replicate a database and thus you only get the user synced. Not the actual login account.

So if you find yourself having a requirement of creating a read-only account for a replica you need to do a few steps:

  1. Create the user on the original database and hand out desired permissions. In our case db_datareader.
  2. Add that same user to the server that is running the replica, but make sure it has the same SID.
  3. Hand out ##MS_DatabaseConnector## permissions so that your user can sign in.

Step one is most likely something you have done before, create a login, user and make sure it has the required permissions.

CREATE LOGIN [reportinguser}] WITH PASSWORD=N'{password}'
GO

USE [mydatabase]
GO

CREATE USER [reportinguser] FOR LOGIN [reportinguser] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember 'db_datareader', 'reportinguser'

Then connect to the replication SQL server and replicated database to execute the following statement to retrieve the SID of users. This should return a reportinguser with a sid that we need. Without that ID we can’t fix the account, so make sure to write it down.

SELECT name, sid, principal_id FROM sys.database_principals
WHERE type = 'S'
AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
AND authentication_type_desc = 'INSTANCE';

Finally with that SID you can create a new login with the same username and password (or different password if you want). By putting the same SID you are ensured that it uses the same permissions as the source database. In our case a db_datareader. Adding the server role ##MS_DatabaseConnector## you are making sure that you can connect with that user.

CREATE LOGIN [reportinguser] WITH PASSWORD = N'{password}', SID = {SIDRetrievedFromPrevious};

<!-- make sure we can sign in to the db -->
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [reportinguser]

🔓 With these steps you can finally connect to the read-only copy created by replication.

Loading comments…