First published on MSDN on May 21, 2018
Sometimes we found it is tricky to setup SQL Login account other than default built-in SQL Admin and using them to allow SQL application access to login to Azure Server and Databases members in Auto-Failover Group!
First I would like to briefly summarize Auto-Failover Group feature introduced to Azure SQL DB as a part of business continuity features.
Simply Auto-Failover group provide abstraction of active Geo-Replication automatically supporting replication and failover between two servers in different regions (Primary and Secondary Server). one or more auto-failover groups include one or many databases recovered in case some primary databases become unavailable due to outage on primary region.
Auto-Failover contains:
1-
Primary server
: server hosts one or more primary databases member in failover group.
2-
Secondary server
: server hosts read-only replica of primary-databases in failover group (secondary cannot be in the same region as the primary server).
3-
Failover group read-write listener
: DNS CNAME record <f
ailover-group-name.database.windows.net
> point to current primary server. and it allows read-write SQL applications to transparently reconnect to primary database when the primary changes after failover.
4-
Failover group read-only listener
: DNS CNAME record <
failover-group-name.secondary.database.windows.net
> point to secondary server. and it allows read-only SQL applications to co connect to secondary databases for reporting purpose.
5-
Automatic failover policy
: by default Auto-Failover configured with automatic failover configuration. if not configured then manual failover is required to recover databases in Failover group.
You may find more reading about Auto-Failover Group can be found
here
Specifically in this post, I want to share with you how to create and configure SQL Login other than built-in Admin to allow access for SQL applications to access and manage Primary/Secondary databases in Auto-Failover Group.
Find some users follow traditional way to create SQL Login at Master DB first then create Database users from that login in Primary Database, later repeated the same steps on the Secondary Database. simply they follow steps similar to below ones:
-- Create login at master database
CREATE LOGIN login_name WITH PASSWORD = 'strong_password';
-- Create user from that login at user database
CREATE USER 'user_name' FOR LOGIN 'login_name';
ALTER ROLE db_owner ADD MEMBER [user_name];
Manual failover to read-only database!!
Repeat the above steps to create new login and new user in the new primary replica after failover!
Once above steps done, update SQL application (connection string) to include newly created user credentials, and test connecting to primary database normally works fine, however, after failover and trying connect to new primary it fails and show this error message,
SQL application show 500 Internal Error !!
What`s happened after failover, actually, once first login and user created in primary database, then manually failover, and create same login and user to secondary database. in reality you have created 2 different logins and users on Primary / Secondary DBs!!
The best way to achieve this, is to create a Contained DB user directly on primary failover database, wait for next replication to take place, then automatically created user change will replicate to secondary database. manual failover and test connect using SSMS or update SQL application connection string credentials using Contained DB user should works.
-
On primary database create Contained DB user execute this command
-
Wait till next replication take place and manually failover
-
test login use user created at primary database and this should works
-- Connect to user database and execute to create Contained user directly to user database
CREATE USER user_name WITH PASSWORD = 'strong_password';
ALTER ROLE db_owner ADD MEMBER [user_name];
For more information about Contained Database User can be found
here
Hope that you enjoyed today tip & trick!
Please share with us your feedback and we are welcome for any suggestion for the next tips & tricks blog !
