Sql-server – Sql server/azure connection string parameter for readonly access

azure-sql-databaseread-only-databaseSecuritysql server

Is there a specific parameter that can be used in a SQL server/azure connection string to specify that the connection should be readonly?

In other words, only SELECT queries should work but INSERT/UPDATE/DELETE and DDL statements should return an error.

I'm aware of the ApplicationIntent=ReadOnly parameter, but my understanding is that its purpose is to connect to read replicas.
In my case, the target server might not have any read replica. In this case, would ApplicationIntent=ReadOnly be enough to make the conneciton read only even though it connects to the primary?

Best Answer

No, there is unfortunately no such connection string parameter. The "ApplicationIntent=ReadOnly" option is just used by the SQL driver to attempt to route your connection to a read only replica.

I'd suggest that, if there are users or processes that should only have read access to this database, you accomplish this using the built-in SQL Server permissions. In other words, only add those users / processes to the db_datareader database role if they are accessing tables and view directly.

If they are accessing data through stored procedures, be careful to only grant them EXECUTE permissions on the read procedures.

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

Sql-server – SQL SERVER – “ApplicationIntent=ReadOnly”Listener redirect to fully qualified domain name

My initial suspicion is that the initial setup didn't include the fully qualified name, but check for certain with the following statement:

SELECT ag.NAME AS "Availability Group"
    ,ar.replica_server_name AS "When Primary Replica Is"
    ,rl.routing_priority AS "Routing Priority"
    ,ar2.replica_server_name AS "RO Routed To"
    ,ar.secondary_role_allow_connections_desc
    ,ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
INNER JOIN sys.availability_replicas ar ON rl.replica_id = ar.replica_id
INNER JOIN sys.availability_replicas ar2 ON rl.read_only_replica_id = ar2.replica_id
INNER JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
ORDER BY ag.NAME
    ,ar.replica_server_name
    ,rl.routing_priority

The query was gratuitously lifted from this MSDN blog: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/22/modifying-alwayson-read-only-routing-lists/

In addition, make sure your JDBC driver supports the ApplicationIntent=ReadOnly parameter.

Best Answer

Related Solutions

Sql-server – From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly

Steps to Reproduce

Sql-server – SQL SERVER – “ApplicationIntent=ReadOnly”Listener redirect to fully qualified domain name

Related Question