jmazzolini avatar image
jmazzolini asked

Verify ApplicationIntent value

Is it possible to validate a connection is using ApplicationIntent=ReadOnly? The application code shows that ApplicationIntent=ReadOnly is added to the connection string. However, an error is being thrown when accessing a view against a readonly DB. I would like to validate that an active connection is actually using ApplicationIntent. Is there way to view this setting for a live connection? I tried sp_whoiactive, sp_who, sp_who2 and all connection\request DMV's with no luck.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thesqlguyatl avatar image thesqlguyatl commented ·
Does the login have SELECT access to the view? Have you seen this? [][1] The application intent connection string property expresses the client application’s request to be directed either to a read-write or read-only version of an availability group database. To use read-only routing, a client must use an application intent of read-only in the connection string when connecting to the availability group listener. Without the read-only application intent, connections to the availability group listener are directed to the database on the primary replica. The application intent attribute is stored in the client’s session during login and the instance of SQL Server will then process this intent and determine what to do according to the configuration of the availability group and the current read-write state of the target database in the secondary replica. [1]:
0 Likes 0 ·
jmazzolini avatar image jmazzolini commented ·
Yes, The user has rights to all objects. I have read this @msdn and understand ApplicationIntent and how it is set. However, as a DBA I do not manage the code for the connection string, our developers do. Therefore I am relying on what they are telling about setting the connection string properly. What I'm trying to do is validate that a live connection is truly using the ApplicationIntent configuration. Ideally, I would like capture the connection properties as it's in use to validate the config is being set properly
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.