Connect to SQL Server Availability Group replica with SSMS

Problem

When connecting to a SQL Server AlwaysOn database replica where the Readable Secondary is set to Read-intent only you get the following error message:

With SQL Server Management Studio (SSMS)

With a query

Msg 978, Level 14, State 1, Line 1

The target database (‘DatabaseName’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

Solution

First we need to understand why this error is being raised.

When connecting to a database through SQL Server Management Studio (SSMS) the error message isn’t clear, but the error thrown by a query is quite clear on why this is happening.

When configuring Read-Only Access on an Availability Replica you have 3 options:

1. No – No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.
2. Yes – All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.
3. Read-intent only – Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

A Read-intent only replica means that the Secondary Replica only accepts connections that are explicitly configured for that purpose and the problem occurs when you are trying to connect to an AG Secondary Replica database configured for Read-Intent only without explicitly using the proper parameter.

There are two options to solve this problem:

Connect to the replica(s) with Application Intent=Read-only

When connecting to the AG Secondary Replica instance use the ApplicationIntent=ReadOnly parameter.

To do this from SSMS, in the connection windows press the “Options >>” button before connecting.

Then go to “Additional Connection Parameters” tab and enter ApplicationIntent=ReadOnly in the text box. You should be able to connect now.

For applications you’ll need to add the proper parameter in the connection string. Here is an example:

("Driver={SQL Server Native Client 11.0};server=AG_Listener;Database=AdventureWorks;trusted_connection=yes;ApplicationIntent=readonly")

When connecting with sqlcmd utility it will also need to provide the correct parameter (-K)

sqlcmd -SAG_Listener -E -dDatabaseName -Kreadonly

Configure the replica(s) Readable Secondary option to Yes

This option should be only followed if you are sure that will not impact any of your applications since it will not redirect them automatically to a Read Only replica after you change this configuration.

NOTE: The following configuration needs to be performed on the Primary Replica server.

USE [master]
GO
ALTER AVAILABILITY GROUP [AG_Name]
MODIFY REPLICA ON N'ReplicaInstance' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO

Alternatively you can use SSMS to change the configuration by editing the AG properties (AlwaysOn High Availability / Availability Groups / <“AG name”>) and then change the Readable Secondary value as shown below:

Other Notes

It’s always a good policy to connect to an AG database using the AG Listener instead of the SQL Server instance name. The listener will guarantee that you are always connecting to the Primary Replica or to the Read-Intent Only Secondary Replica depending on the connection parameter.

Next Steps

• Check out these other AlwaysOn Availability tips