Deny User Logins

Imagine that you live in a world where you need an applicational user with god-like powers because, in fact, it needs to. If you want to make sure that applicational users are only used by the application you’ll have to create strong passwords, keep’em secret, change’em often, all that good stuff, sure. But you know what they say – when there’s a will, there’s a way – and there are people who simply like to leave in Santa’s naughty list. So today we’ll talk about an old trick that can be used to help prevent logins from a user, under specific cirumstances – Logon Triggers:

“Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.”

Firstly, we’ll try to deny any SSMS login attempt with the user “appUser”. To accomplish that we’ll create a new trigger, as presented below:

CREATE OR ALTER TRIGGER DenySSMSLogin
ON ALL SERVER 
FOR LOGON
AS
BEGIN
  DECLARE @AppName varchar(max)
  DECLARE @LoginName sysname
  DECLARE @LoginType sysname
  
  SET @AppName = APP_NAME()
  SET @LoginName = ORIGINAL_LOGIN()
 
  IF(@LoginName = 'appUser' AND @AppName LIKE 'Microsoft SQL Server Management Studio%')
  BEGIN
	RAISERROR('Call the Federales',16,1)
    ROLLBACK;
  END
END

Now, we won’t be able to login this user on SSMS, as the following error message indicates:

Most importantly, queries done by this user through the application’s services will be processed as intended. Now just sit and wait for people to start asking what’s happening and then beat them with the nearest heavy object, calmly explain them why this is beeing done.

Ok, so now I have to come clean. The bad news is that I lied and it’s still possible to login appUser on SSMS. If you are a really evil user, you can change the application name under the “Additional Connection Parameters” on connection Options.

Furthermore, even if you deny connections on SSMS you might want to keep an eye on things like SQLCMD.EXE. Nevertheless, if all you need is love a soft remainder that using the applicational user is not cool, that might do the trick. Otherwise, sorry for wasting your time. But there is still hope.

The good news is that we can use alternative approaches, if blacklisting application names is not an option. For example, we can restrict logins to a range of authorized IPs, as suggested by Solomon Rutzky in this post.

--(...)
IF (ORIGINAL_LOGIN() = N'appUser' AND 
 (
   CONVERT(VARCHAR(10), CONNECTIONPROPERTY('net_transport')) <> 'TCP' OR 
   CONVERT(VARCHAR(10), CONNECTIONPROPERTY('client_net_address')) <> '10.10.1.1'
 )
)
BEGIN
  RAISERROR('Call the Federales',16,1)
  ROLLBACK;
END;

This method implies less security risks as it would be necessary to be logged onto the production server, or spoof its IP, in order to overcome this restriction.

Finally, if you need to remove the trigger, just execute the following command. Be sure to specify “ON ALL SERVER” if the same was specified upon creation, otherwise the trigger won’t be removed.

DROP TRIGGER DenySSMSLogin ON ALL SERVER;

And that’s it! 🖖

Slow Query Store

I’m in a SQL Server 2016 database trying to view the Top Resource Consuming Queries. Since this database has Query Store enabled, I went to Query Store > Top Resource Consuming Queries and the usual screen pops-up, as the data is loading:

After a few seconds waiting, I gave up on Query Store and went to see random videos on the internet do other very professional task, that took me about ten minutes. When I switched back to check the result, the query responsible to retrieve this information was still running.

At this stage, I was wondering that maybe the Query Store got too fat to be fast. The results shown that it was occupying about 1.5 GB. That’s not very impressive, isn’t it?! But that’s something, I guess.

Initially, I thought using DBCC CLONEDATABASE. This command creates a schema-only copy of a database, to the same server as the source database and allows us to include Query Store data. After that, it would be safe to clear the Query Store without losing those statistics, and it would make a fresh start, collecting enough data to present the recent results, without the weight of historical data. Nevertheless, I would have to go through the same pain to analyze older data, so I decided to leave this path and adapt the query that the Query Store was running instead:

SELECT TOP 25 
	q.query_id,
	OBJECT_NAME(q.object_id),
	rs.avg_duration,
	rs.avg_cpu_time,
	rs.avg_logical_io_reads,
	rs.avg_logical_io_writes,
        rs.last_execution_time
FROM 
	sys.query_store_query AS q
JOIN 
	sys.query_store_plan  AS p ON q.query_id = p.query_id
JOIN 
	sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE 
	q.object_id > 0
	AND rs.last_execution_time > DATEADD(HOUR, 1, GETUTCDATE())
ORDER BY rs.avg_duration DESC

By including Live Query Statistics, I was able to see that something was going off with the amount of data on the TVF used by the Query Store, that was posteriorly going through a Filter operator, a Row Count Spool and ended up being joined using Nested Loops.

As quick workaround, I hinted the query with OPTION(HASH JOIN) to change the plan and…

It ran in about 2.5 seconds! As we can see, the plan changed completely – No nasty Filters, no Spools, nor Nested Loops, obviously.

We can use sp_create_plan_guide to apply this principle in the query running behind the Top Resource Consuming Queries in the Query Store GUI, but removing the power of choice from SQL Server is dangerous and often ends up in tears. So, proceed carefully!