Category Archives: sql triggers

Prevent SQL Logins from using SSMS … or any other app

In a continuation of my previous LOGON TRIGGER post.

A downside to SQL logins is that anyone with the username / password combination can login as that user. This prevents us from knowing who truly logged in and performed some action. It is possible to trace and log everything by IP address and correlate back to that.

Another downside is these accounts are usually service accounts, at least in our environment. These SQL logins may have more permissions than they should because they may be part of a vendors product, and the vendor’s app requires them to have sysadmin on the server.¬†We have an issue with some of our users logging in as these SQL logins and running queries or manipulating data that they shouldn’t be.

So how can we allow the login to still exist, but deny normal users from logging in under those credentials? There’s a few different ways, one could be isolating these users to specific endpoints that would allow only that user from a given ip range. Another option, and the one that made sense here, was to use a LOGON TRIGGER. This allows us a great amount of flexibility on deciding when to prevent a connection from happening.

In this case, we wanted any SQL login using Management Studio to be denied access. Any of the other apps that log in with this account will still work. This includes SqlCmd, Access, etc. Our main problem, though, were devs coming in under SSMS. We also wanted to log any login attempts using this method.

This script creates a logging table in our management database (T1001), creates a view to make things nicer then creates the logon trigger to reject any connections that match our criteria.

--------------------------------------------------------------------------------
-- Create table to hold rejected login attempts
--------------------------------------------------------------------------------
 
USE T1001;
GO
 
CREATE TABLE T1001.SQLTrace.loginData (
	id INT IDENTITY PRIMARY KEY,
	data XML,
	program_name sysname
)
GO
 
--------------------------------------------------------------------------------
-- Create view to make querying the table a little nicer
--------------------------------------------------------------------------------
CREATE VIEW SQLTrace.loginDataView
AS
SELECT id
      ,data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname') AS EventType
      ,data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS PostTime
      ,data.value('(/EVENT_INSTANCE/SPID)[1]', 'int') AS SPID
      ,data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(257)') AS ServerName
      ,data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') AS LoginName
      ,data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname') AS LoginType
      ,data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(85)') AS SID
      ,data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname') AS ClientHost
      ,data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit') AS IsPooled
      ,program_name
FROM SQLTrace.loginData
GO
 
--------------------------------------------------------------------------------
-- Create logon trigger
--------------------------------------------------------------------------------
USE [master];
GO
 
/****** Object: DdlTrigger [Deny_SQLLogin_SSMS_Trigger] ******/
IF EXISTS(
	SELECT * FROM master.sys.server_triggers
	WHERE parent_class_desc = 'SERVER'
		AND name = N'Deny_SQLLogin_SSMS_Trigger')
DROP TRIGGER [Deny_SQLLogin_SSMS_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER Deny_SQLLogin_SSMS_Trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @data XML
SET @data = EVENTDATA()
 
DECLARE @AppName sysname
       ,@LoginName sysname
       ,@LoginType sysname
SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
 
SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
      ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
 
IF @AppName LIKE ('Microsoft SQL Server Management Studio%') --If it's SSMS
   AND @LoginName <> 'sa'
   AND @LoginType = 'SQL Login' --('SQL Login' | 'Windows (NT) Login')
BEGIN
    ROLLBACK; --Disconnect the session
 
    --Log the exception to our table
    INSERT INTO T1001.SQLTrace.loginData(data, program_name)
	VALUES(@data, @AppName)
END
 
END;
GO

Don’t forget to create a job to purge historical log data at sufficient intervals for you.

Have SQL Demand Application Identification

How many times have you run Profiler on your SQL Servers to see this?

With all of those apps identifying themselves as .Net SqlClient Data Provider, how can you tell which app is really making those queries? Unless each app uses a distinct login, you would never know. And many times devs will reuse logins for their own ease.

I ran into this problem while I was doing some migration planning. I was profiling the server to see what apps were making queries to a specific database we were migrating over to another server. I wanted to make sure that anything making a connection was properly configured after the move. I found something similar to the shot above; connections from different machines making different queries at different, albeit regular intervals, all identified as “.Net SqlClient Data Provider.” With some other detective work, I was able to figure out where they were coming from.

How can this be avoided? When developers are setting up their connection strings, they need to specify the Application Name parameter. An example is below.

<add name="LOCAL"
    connectionString="Data Source=(local);Initial Catalog=master;Integrated Security=True;Application Name=LoginTriggerDemoApp"/>

Using LOGON TRIGGERS in SQL 2008 and up, you can go one step further and start auditing logons and watching for any connection made that uses a generic Application Name. There is a limited amount of information available in the EVENTDATA() from a LOGON EVENT. You can get more information about the connection by joining to the sys.dm_exec_sessions DMV.

...
DECLARE @data XML, @ApplicationName NVARCHAR(128)
SET @data = EVENTDATA()
SELECT @ApplicationName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
...

In the code above, I first declare an XML variable to hold the data that comes from the EVENTDATA() function. Then use the SPID to join to the sys.dm_exec_sessions DMV to get the program_name, which becomes our Application Name. From this, you can log information about the connection. You can start researching anything that doesn’t have a specific application name and start encouraging your developers to include that portion of the connection string.

You can go even further and block any generic connections. Using the trigger below, any connection that comes in under “.Net SqlClient Data Provider” gets disconnected via ROLLBACK. You can add to or alter the IN clause to prevent generic Java, PHP, etc. connections. You can check for blanks, or even prevent certain apps from logging in all together, regardless of credentials.

USE [master];
GO
 
 
/****** Object: DdlTrigger [Deny_Generic_ConnString_Trigger] ******/
IF EXISTS(
	SELECT * FROM master.sys.server_triggers
	WHERE parent_class_desc = 'SERVER'
		AND name = N'Deny_Generic_ConnString_Trigger')
DROP TRIGGER [Deny_Generic_ConnString_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER Deny_Generic_ConnString_Trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @AppName nvarchar(128)
SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = EVENTDATA().value(
    '(/EVENT_INSTANCE/SPID)[1]', 'int')
 
 
IF @AppName IN ('.Net SqlClient Data Provider')
    ROLLBACK; --Disconnect the session
 
END;

Notes for Deployment

If you have dev & QA servers, test these out there first. Give your application developers time to make changes and test against these new triggers. Also, you may not be able to change 3rd party apps, so you can make exceptions based on host names or anything else available in the EVENTDATA() or sys.dm_exec_sessions. I did find out that Reporting Services does not like either of these triggers in place. Even the logging trigger will prevent the Report Manager from running.

Blocking generic applications may have consequences on a server that is already serving data to unidentified apps, so be careful. You may want to put logging in for a time and track down any offending applications and give them time to change. For a server coming online though, blocking generic apps may be a good way of preventing a lot of unnecessary detective work, and make profiling or migrating your servers easier.

I have included my scripts as well as sample C# project to demonstrate the technique. Download now.

A special thanks to Brent Ozar for giving me his opinion on this post and inflating my head somewhat.