Fix SQL Server Error 976: Cannot connect to Secondary Replica of AlwaysON Availability Group
Today an application user has reported that he is not able to connect to the secondary replica of AlwaysOn Availability Group configuration. The details about error Microsoft SQL Server 976 that he was getting is given below.
Cannot connect to ‘Secondary Replica Server’.
The target database, ‘SDGC’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)
The screenshot of this error is given below.
Before going ahead let me give you a background about SQL Server configurations.
- We have two SQL Servers Server A & Server B and there is an user database SDGC is hosted on both SQL Server Instances.
- AOAG is configured between both servers in Automatic failover mode for database SDGC.
- Availability database SDGC hosted on instance B is inaccessible because it was restored in no recovery mode and not set in read only mode for AOAG configuration.
- User’s login was created on both SQL Server instances with having default database as SDGC that are participating in AOAG.
Now, the problem is whenever user tries to connect to the secondary replica whether AOAG is active from instance A or instance B, he is not able to connect to the SQL Server Instance and getting given SQL Server error 976. I analyzed this issue and found two solutions to fix this issue:
- Change Connect to database value in SSMS while establishing the connection.
- Change default database value of your Login in Login Property window.
Change Connect to database value in SSMS
Launch SQL Server Management Studio. Click on Options tab of connection details window in SSMS as shown in below image.
You can see, Connect to database value showing as default that means it is connecting to the database that is set as default database for user’s login. Remember, default database for user’s login is set as SDGC that is running in norecovery mode.
Now, we just need to change the connect to database value from default to master or some other database. As default database for user’s login was set to SDGC, so change it master to make database engine to read an accessible database.
Once you make changes, hit the connect button and establish a database connection. This time you will be able to establish database connection to secondary replica.
Change default database value of your Login
If default database of your login is not mandatory to set to availability database then you can change it to master and remove this issue permanently. Otherwise, you need to change default database every time you make a connection to secondary replica as discussed in above section.
You can connect to your secondary replica or to the server where you are facing this issue using different admin or security admin account. If you have such account and password, you don’t need to logoff and relogin. You can just right click on SSMS icon while pressing SHIFT button to logged in using different user to the SQL Server instance. You can see that option in below image.
Click on “Run as different user” option. It will display below login screen to enter your new login and password. Enter the credentials.
Once your new account will be authenticated, SSMS will be launched and show you the details as shown in below image. Click on Connect button to establish the connection.
Expand Security folder. Identify your login for which you have to change the default database setting. Right click and choose properties on this login to launch properties window. You can double click on this login as well to launch this properties window. Below window will come to your screen where you can see the default database set for this login.
You can see default database is set as SDGC in above screenshot. Now we need to change it to master or any accessible database. Do the changes, you can see i have changed it to master in our screenshot. Once you make changes, click on Ok button to proceed.
NOTE: If you need to change default database back to availability database due to some application dependencies then you need to first make your availability database accessible. You can perform failover to make secondary replica as primary then you can make changes and then you can failback. If you don’t want to perform failover/failback, then you can do this by changing the availability database from norecovery to read only mode.
Now your issue is fixed. You would be able to connect to the SQL Server Instance that are running as secondary replica in AOAG configuration. You can see, i have connected to secondary replica using same account with whom i was facing issue.
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018