If you are using Azure SQL Database with geo-replication or auto failover group and if you are using logins and users rather than contained users you can end up with the following error when you try to connect to the secondary/failover database after failover occured.
If you already created a login on the failover server using SQL command
The SID of the login in the master database will not match with SID in the user database, due to the user will not be able to access that database.
You can use the following SQL Command to set up user access to the secondary database.
Firstly you need to determine which logins you need on failover SQL server. To determine that you will need to query master DB, to be able to that you will need to have proper permissions.
Run the following statement on the primary server:
Depending on the login names you have on your server results of the query should be similar to this
After that run following SQL statement on the user database, again you will need proper permissions to be able to do that.
Results of the query should be similar to this
After you determine what is the proper SID for that user on Primary server you need to create a login on the secondary server using that same SID value, for example:
Also, you can use PowerShell to do all that in one step.
First, you need to connect to your Azure account and check if you using right subscription
Then you can use the following PowerShell script to create users on both Primary and Secondary server with the same SID.
You can find more information about how to configure and manage Azure SQL Database security for geo-restore or failover on the following MS article
Leave a Reply