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.
Firstly, please connect to your primary server using admin credentials. This is required as we need to access
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:
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.
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.
Author Tomasz Gawlik