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’.
ADDITIONAL INFORMATION:
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.

Error 976

Solution

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.

click on connect button in SSMS

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.

connect to database in ssms

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.

Change connect to database to master in ssms

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.

login using different account

Click on “Run as different user” option. It will display below login screen to enter your new login and password. Enter the credentials.

Enter 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.

connect using different account in ssms

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.

login property

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.

login property

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.

Related Articles:

Please comment us to give your feedback. I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *