Overview

While configuring SQL databases in the cloud, it’s common to create an infrastructure where there are two SQL servers (most likely in two different geographical locations) with one primary (read and write) and one secondary (read) replica.

Along with this, it’s common to prepare two sets of credentials:

  • read and write users with access to the primary database
  • read users with access to the replica

In this article, we will assume you already have two sql servers and one database geo-replicated across them. We will focus on that secondary task: how to prepare authentication credentials for those databases.

Although the process can be similar, this article is assuming you’re using SQL server running on Azure platform.

Primary server

Firstly, please connect to your primary server using admin credentials. This is required as we need to access master database.

Let’s assume we want to create sqlwriteuser user on the primary server.

USE master
CREATE LOGIN sqlwriteuser WITH PASSWORD='somePassword'

USE yourDatabase
CREATE USER sqlwriteuser FROM LOGIN sqlwriteuser;
EXEC sp_addrolemember 'db_datareader', 'sqlwriteuser';
EXEC sp_addrolemember 'db_datawriter', 'sqlwriteuser';

This should be enough for our first credentials. Now we need to create read-only user. It needs to be created on primary server too - then will get synchronized and appear on replica database.

USE master
CREATE LOGIN sqlreaduser WITH PASSWORD='somePassword'

USE yourDatabase
CREATE USER sqlreaduser FROM LOGIN sqlreaduser ;
EXEC sp_addrolemember 'db_datareader', 'sqlreaduser ';

Once doing that we need to query the database in order to get SID for that login. It will be needed on the secondary server:

USE yourDatabase
SELECT [sid] FROM [sys].[database_principals] WHERE [type_desc] = 'SQL_USER' AND [name] = 'sqlreaduser'

Example value would be:

0x326A7A97E75A9F4190304FA6D7298277

Secondary server

There’s less work on the secondary server, we only need to create a new login and provide SID which we’ve just queried on a primary server.

USE master
CREATE LOGIN sqlreaduser WITH PASSWORD = 'somePassword2', SID = <SID from primary database>

Please note that you don’t need to create a login with the same password as on the primary server.

Summary

Once done it’s quite easy to manage credentials for databases and its replicas on Azure. Key points to remember are to only create users on the primary database and remember to use login SID on the secondary server while creating new logins.