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! 🖖