23

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.)

  • I think read-only is decided at TDS routing time. Once is routed to a readable secondary, info is no longer needed so probably it does not make it into the engine. – Remus Rusanu Jun 23 '16 at 21:29
  • 2
    "read only routing first connects to the primary and then looks for the best available readable secondary" it appears the secondary would see it as an ordinary connection. If there is any XEvent triggered, it would be on the primary. I don't know what I'm talking about, but I'm speculating. – Remus Rusanu Jun 23 '16 at 21:33
  • 1
    @RemusRusanu are you talking about sqlserver.read_only_route_complete as it is triggered on primary only. – Kin Shah Jun 23 '16 at 21:35
  • @Kin there you go, exactly as I would had code it ;) – Remus Rusanu Jun 23 '16 at 21:35
  • 2
    @RemusRusanu I was playing with it and I guess its the closest that you can get with gotchas - the readonly URL is configured correctly & there are no connectivity problems. Under both of those cases, that event will succeed. – Kin Shah Jun 23 '16 at 21:39
10

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:

sqlcmd connections

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:

Query Results

  • 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'
        )
    
5

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 ;-).

  • Unfortunately, I can't deny them - the other readable replicas could be down. I still need the read queries to work on the primary - I just need to know when they're happening. – Brent Ozar Jun 23 '16 at 22:27
  • @BrentOzar I have updated my answer to include a new Step 3 that will check for that condition and if there are no available Secondaries, then it will allow the Connection. Also, if the intent is still to just "know when thy're happening", then the same setup can be used, just change the ROLLBACK in the Login Trigger to an INSERT into a log table :-) – Solomon Rutzky Jun 24 '16 at 3:29
  • 1
    this is a great answer, but it's not for this question. I don't need to stop users, I need to monitor when it's happening. We have existing apps that we need to gradually pinpoint and fix. If I stopped users from logging in, it would cause an immediate revolt. If you'd like to create a separate question for this, and post your answer there, that'd be great - but please focus your answer here on my actual question. Thanks. – Brent Ozar Jun 24 '16 at 11:46
  • @BrentOzar Sorry, I misunderstood your comment to Tom as meaning something a bit stronger than just tracking/logging. I have removed the portion of my answer that dealt with preventing access. – Solomon Rutzky Jun 24 '16 at 17:44
  • @BrentOzar I added some notes below the line (in the P.S. section) that was close to being a solution, but thwarted at the very end. I posted those notes in case it sparks an idea in you (or someone else) to come up with the missing piece, or even something entirely different, that might solve this puzzle. – Solomon Rutzky Jun 24 '16 at 18:56
2

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. :)

  • That's definitely more sick than I want to be, but thanks, hahaha. – Brent Ozar Jun 24 '16 at 11:47
-1

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.

  • 1
    Tom - 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.) – Brent Ozar Jun 23 '16 at 20:48
  • I also have people connecting to my production server with Excel with very limited access. They connect with their rights. I hope I will be able to see them. We will be bringing our Always On up shortly. – ArmorDba Jun 24 '16 at 2:03
-1

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.

  • 1
    This wouldn't work. The SQLCLR code doesn't have access to the current Connection, it has access to the current Session via the Context Connection. The SqlConnection object in the .NET code is not tapping into the actual connection made from the original client software into SQL Server. Those are two separate things. – Solomon Rutzky Jun 24 '16 at 5:24
  • Oh well, never mind then. – Michael Green Jun 24 '16 at 5:55
  • No, this doesn't work. – Brent Ozar Jun 24 '16 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.