How should logins and users be created on a geo-redundant database in Azure?

Question

I need to create a login and user on a geo-replicated database. The user will be granted the role of 'db_datareader' on this database.

The attempted approach has been to use the SID (same identity) parameter, as follows:

-- 1. PRIMARY_DATABASE: Run on master database of Primary Database to create login
CREATE LOGIN [LOGINUSER] WITH password='XYZ'
GO

-- 2. Select details for created SQL login
SELECT [name], [sid] 
FROM [sys].[sql_logins] 
WHERE [type_desc] = 'SQL_Login'
AND [name] = 'LOGINUSER'
GO

-- 3. SECONDARY_DATABASE: Run on master database of Secondary Database to create login with associated SID
CREATE LOGIN [LOGINUSER]
WITH PASSWORD = 'XYZ',
SID = <SID from Step 2>
GO

While the login creation works fine on both databases, I was allowed access on the PRIMARY_DATABASE, and denied access on the SECONDARY_DATABASE with the following error:

The server principal "LOGINUSER" is not able to access the database "master" under the current security context.
Cannot open user default database. Login failed.
Login failed for user 'LOGINUSER'. (Microsoft SQL Server, Error: 916)

1. Is this the correct approach for setting up a login in geo-replicated databases?
2. How do I set up a user on these databases?

Answer 1

You should create logins on the other server too from the SID taken from the primary. Otherwise you can use contained users feature in SQL server / db so that users gets automatically replicated to the read only copy.

Answer 2

I faced the same issue and the following worked for me. I have Azure SQL Server on which the Active geo-replication is enabled.

1. Ensure that your IP Address is added to the firewall of the existing and the new Server.

On the master database of the Primary server:

2. Create a login

CREATE LOGIN geo_user WITH PASSWORD = 'secure-password'

3. Create a corresponding user and assign it to the dbmanager role (you can assign an appropriate role as per your requirement)

CREATE USER geo_user FOR LOGIN geo_user

ALTER ROLE dbmanager ADD MEMBER geo_user 

4. Note the SID of the new login:

SELECT sid FROM sys.sql_logins WHERE name = 'geo_user'

On the source database of the Primary server:

5. Create user for the same login:

CREATE USER geo_user FOR LOGIN geo_user

6. Add the user to the db_owner role:

ALTER ROLE db_owner add MEMBER geo_user

On the master of the Secondary server:

7. Create the same login as on the Primary server (using the same username, password and SID from step-4 ):

CREATE LOGIN geo_user with password = 'secure-password', sid=0x01060000000000640000000000000000A0D03563024DA846ADBCF33D31B6C026

8. Create a corresponding user and assign it to the dbmanager role:

CREATE USER geo_user FOR LOGIN geo_user

ALTER ROLE dbmanager ADD MEMBER geo_user 

Now you should be able to login to the new Server using this user and query the database.

Answer 3

It appears you don have local access to master db. Have you tried accessing master via a different login? If not you may want to create a local user in master and connect using it.

Answer 4

You should use contained users instead. These users are contained within the database, are synced along geo replications and do not need an extra login from master table.

Create a contained user with password as db_owner:

USE mydatabase;
CREATE USER myuser WITH PASSWORD = 'secret';
EXEC sp_addrolemember 'db_owner', 'myuser'

more information: https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver15