The Target Database Is In An Availability Group And Is Currently Accessible For Connections When The Application Intent Is Set To Read Only

News Knowledge Base
1

Got error message "The target database ('UiPath') is in an availability group and is currently accessible for connections when the application intent is set to read only." in the Event Viewer log and Orchestrator cannot connect to the database.

Issue: 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:

"System.Data.Entity.Core.EntityException: The underlying provider failed on Open. --->

System.Data.SqlClient.SqlException: The target database ('UiPath') 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."

Root Cause: The error message tries to connect to is in an availability group and is currently only accessible for connections with the application intent set to read only. This means that it is not possible to connect to the database with the application intent set to read write.

Resolution:

  1. Make sure that the application intent is set to read only on the connection string. Do this by adding the following property to the connection string: ApplicationIntent=ReadOnly
  2. Configure the replica(s) Readable Secondary option to Yes. This option should be only followed if sure that will not impact any of applications since it will not redirect them automatically to a Read Only replica after changing this configuration. Perform the query 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

  1. Also change the configuration directly in SSMS by editing AG(AlwaysOn High Availability/Availability Groups/<"AG name">) and then change the property 'Readable Secondary' to 'Yes'.