Connect to SQL Server Availability Group replica with SSMS when Readable Secondary is Read-intent only

By:   |   Comments (5)   |   Related: > Availability Groups


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)

AlwaysOn replica error message

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.


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.

connect to sql server

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

connect to sql server using application intent

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]

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:

alwayson replica secondary settings

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Vitor Montalvao Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, May 17, 2018 - 10:12:36 AM - Deyvid William Angioletti Evaristo Back To Top (75966)

Hello Vitor, thanks for your article, very useful tips.

You just forgot to mention that when using SQL Server Management Studio, besides entering "ApplicationIntent=ReadOnly" in the Additional Connection Parameters box, you must specify the name of the database in the second tab (Connection Properties).

Hope it helps,

Deyvid William Angioletti Evaristo

Friday, December 8, 2017 - 3:28:11 PM - Shawn Melton Back To Top (73790)

A note on use of SSMS. Just adding the ApplicationIntent is not going to be sufficient enough if the login you are using has a default database of master. You will always hit the primary replica and think your read-only routing is not working properly.


In order for read-only routing to be performed SQL Server has to know you are connecting to the database that is associated to that listener name. So you have to also set the default database to that AG database along with using the ApplicationIntent option. It will then validate the listener to the database and work through the routing list for read-only access.

Friday, October 13, 2017 - 7:25:32 AM - Govindaraju Back To Top (67266)


This article is very helpful and I have fixed lot if issues in SQL through "MSSQLTIPS" artcles.


Thanks a lot....

Monday, March 13, 2017 - 5:04:10 AM - Vitor Montalvão Back To Top (50960)

You can run the following query. It will return the role for the SQL Server instance that you are connected to:

SELECT r.replica_server_name, s.role_desc

FROM sys.dm_hadr_availability_replica_cluster_states AS r

INNER JOIN sys.dm_hadr_availability_replica_states AS s ON s.replica_id = r.replica_id

WHERE r.replica_server_name = @@SERVERNAME

Friday, March 10, 2017 - 6:18:19 PM - Bob Amy Back To Top (48785)

How can you confirm if You are actually reading the Secondary in SSMS?  Is there a property to check?

get free sql tips
agree to terms