From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

Question

I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings.

From the SQL Server via DMVs (or Extended Events or whatever), can I tell if a user connected with ApplicationIntent=ReadOnly in their connection string?

Please don't answer with how to PREVENT connections - that's not what this question is about. I can't simply go stopping connections, because we have existing applications that are connecting without the right string, and I need to know which ones they are so I can work with the developers and users to get it fixed gradually over time.

Assume that users have multiple applications. For example, Bob connects with SQL Server Management Studio, and with Excel. He connects with SSMS when he needs to do updates, and Excel when he needs to do reads. I need to make sure he's using ApplicationIntent=ReadOnly when he connects with Excel. (That's not the exact scenario, but it's close enough to illustrate.)

Answer 1

Picking up on the sqlserver.read_only_route_complete Extended Event mentioned by Kin and Remus, it's a nice Debug event, but it doesn't carry a great deal of information with it - just route_port (eg 1433) and route_server_name (eg sqlserver-0.contoso.com) by default. This would also only help determine when a read-only intent connection was successful. There is a read_only_route_fail event but I couldn't get it to fire, maybe if there was a problem with the routing URL, it didn't seem to fire when the secondary instance was unavailable / shutdown as far as I could tell.

I have however had some success joining that up with the sqlserver.login event and causality tracking enabled, along with some actions (like sqlserver.username ) to make it useful.

Steps to Reproduce

Create an Extended Events session to track relevant events, plus useful actions and track causality:

CREATE EVENT SESSION [xe_watchLoginIntent] ON SERVER 
ADD EVENT sqlserver.login
    ( ACTION ( sqlserver.username ) ),
ADD EVENT sqlserver.read_only_route_complete
    ( ACTION ( 
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username 
        ) ),
ADD EVENT sqlserver.read_only_route_fail
    ( ACTION ( 
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username 
        ) )
ADD TARGET package0.event_file( SET filename = N'xe_watchLoginIntent' )
WITH ( 
    MAX_MEMORY = 4096 KB, 
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB, 
    MEMORY_PARTITION_MODE = NONE, 
    TRACK_CAUSALITY = ON,   --<-- relate events
    STARTUP_STATE = ON      --<-- ensure sessions starts after failover
)

Run the XE session (consider sampling as this is a Debug event), and collect some logins:

Note here sqlserver-0 is my readable secondary and sqlserver-1 the primary. Here I'm using the -K switch of sqlcmd to simulate read-only application intent logins and some SQL logins. The readonly event fires on a successful read-only intent login.

On pausing or stopping the session I can query it and attempt to link up the two events, eg:

DROP TABLE IF EXISTS #tmp

SELECT IDENTITY( INT, 1, 1 ) rowId, file_offset, CAST( event_data AS XML ) AS event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file( 'xe_watchLoginIntent*.xel', NULL, NULL, NULL )

ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( event_data );


-- Pair up the login and read_only_route_complete events via xxx
DROP TABLE IF EXISTS #users

SELECT
    rowId,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #users
FROM #tmp l
WHERE l.event_data.exist('event[@name="login"]') = 1
  AND l.event_data.exist('(event/action[@name="username"]/value/text())[. = "SqlUserShouldBeReadOnly"]') = 1


DROP TABLE IF EXISTS #readonly

SELECT *,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/data[@name="route_port"]/value/text())[1]', 'INT' ) AS route_port,
    event_data.value('(event/data[@name="route_server_name"]/value/text())[1]', 'VARCHAR(100)' ) AS route_server_name,
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="client_app_name"]/value/text())[1]', 'VARCHAR(100)' ) AS client_app_name,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #readonly
FROM #tmp
WHERE event_data.exist('event[@name="read_only_route_complete"]') = 1


SELECT *
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer

SELECT u.username, COUNT(*) AS logins, COUNT( DISTINCT r.rowId ) AS records
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer
GROUP BY u.username

The query should show the logins with and without application read-only intent:

• read_only_route_complete is a Debug event so use sparingly. Consider sampling for example.
• the two events together with track causality offer the potential to fulfil your requirement - further testing necessary on this simple rig
• I did notice if the database name was not specified in the connection, things didn't seem to work

• I tried to get pair_matching target to work but ran out of time. There is some potential for development here, something like:

  ALTER EVENT SESSION [xe_watchLoginIntent] ON SERVER
  ADD TARGET package0.pair_matching ( 
      SET begin_event = N'sqlserver.login',
          begin_matching_actions = N'sqlserver.username',
          end_event = N'sqlserver.read_only_route_complete',
          end_matching_actions = N'sqlserver.username'
      )
  

Answer 2

No, it does not appear that there is any DMV-exposed connection property (either in sys.dm_exec_connections or sys.dm_exec_sessions) or even CONNECTIONPROPERTY that relates to the ApplicationIntent ConnectionString keyword.

However, it might be worth requesting, via Microsoft Connect, that this property be added to the sys.dm_exec_connections DMV as it appears to be a property of the connection that is stored somewhere in SQL Server's memory, based on the following info found in the MSDN page for SqlClient Support for High Availability, Disaster Recovery (italicized emphasis mine):

Specifying Application Intent

When ApplicationIntent=ReadOnly, the client requests a read workload when connecting to an AlwaysOn enabled database. The server will enforce the intent at connection time and during a USE database statement but only to an Always On enabled database.

If a USE statement can be verified, then the ApplicationIntent needs to be exist beyond the initial connection attempt. However, I have not personally verified this behavior.

---

P.S. I had been thinking that we could make use of the facts that:

• a Primary Replica can be set to disallow ReadOnly access to one or more Databases, and
• the "intent" will be enforced when a USE statement is executed.

The idea was to create a new Database solely for the purpose of testing and tracking this setting. The new DB would be used in a new Availability Group that would be set to only allow READ_WRITE connections. The theory was that inside of a Logon Trigger, an EXEC(N'USE [ReadWriteOnly]; INSERT INTO LogTable...;'); within a TRY...CATCH construct, with essentially nothing in the CATCH block, would either produce no error for ReadWrite connections (which would log themselves in the new DB), or the USE would error on ReadOnly connections, but then nothing would happen since the error is being caught and disregarded (and the INSERT statement would never be reached). In either case, the actual Logon event would not be prevented / denied. The Logon Trigger code would effectively be:

BEGIN TRY
    EXEC(N'
        USE [ApplicationIntentTracking];
        INSERT INTO dbo.ReadWriteLog (column_list)
          SELECT sess.some_columns, conn.other_columns
          FROM   sys.dm_exec_connections conn
          INNER JOIN sys.dm_exec_sessions sess
                  ON sess.[session_id] = conn.[session_id]
          WHERE   conn.[session_id] = @@SPID;
        ');
END TRY
BEGIN CATCH
    DECLARE @DoNothing INT;
END CATCH;

Unfortunately, when testing the effect of issuing a USE statement within an EXEC() within a TRY...CATCH inside of a Transaction, I found that the access violation was a batch-level abort, not a statement-level abort. And setting XACT_ABORT OFF did not change anything. I even created a simple SQLCLR Stored Procedure to use Context Connection = true; and then called SqlConnection.ChangeDatabase() within a try...catch and the Transaction was still aborted. And you can't use Enlist=false on the Context Connection. And using a regular / external connection in SQLCLR to step outside of the Transaction wouldn't help as it would be a whole new Connection.

There is a very, very slim possibility that HAS_DBACCESS could be use instead of the USE statement, but I really don't have high hopes for it being able to incorporate current Connection info into its checks. But I have no way of testing it either.

Of course, if there is a Trace Flag that can cause the access violation to not be batch-aborting, then the plan mentioned above should work ;-).

Answer 3

How sick do you want to be? The TDS stream isn't that hard to proxy, we did it for our SaaS app. The bit you're looking for (literally a bit) is in the login7 message. You could have your users connect via a proxy and log/enforce the bit there. Hell, you could even turn it on for them. :)

Answer 4

Does your application use a service account or maybe multiple service accounts? If so, use Extended Event to monitor your login traffic but exclude your service accounts on your primary always-on server. You should now be able to see who is logging into the primary always-on server and not using the readonly secondary connection string. I am getting ready to install Always-On and this is what I am going to do unless you tell me this will not work.

Answer 5

Unfortunately I don't have the environment to test the following, and there are undoubtedly several points at which it may fail, but I'll throw it out there for what it's worth.

A CLR stored procedure has access to the current connection through the new SqlConnection("context connection=true") construct (taken from here). The SqlConnection type exposes a ConnectionString property. Since ApplicationIntent is in the initial connection string I'm supposing it will be available in this property and can be parsed out. There's a lot of hand-offs in that chain, of course, so plenty of opportunities for it all to go pear-shaped.

This would run from a Logon Trigger and the required values persisted as needed.