Home > dba, sql, sql triggers > Prevent SQL Logins from using SSMS … or any other app

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.

Categories: dba, sql, sql triggers Tags:
  1. January 10th, 2015 at 08:23 | #1

    Dear Eric,
    I am trying to secure my sql database by preventing loggins through SQL management studio and I am trying to run this script and I am getting this error message.
    kindly advise on perquisite and what I am not doing righht

    Msg 2760, Level 16, State 1, Line 2
    The specified schema name “SQLTrace” either does not exist or you do not have permission to use it.
    Msg 208, Level 16, State 1, Procedure loginDataView, Line 18
    Invalid object name ‘SQLTrace.loginData’.
    Msg 4145, Level 15, State 1, Procedure Deny_SQLLogin_SSMS_Trigger, Line 21
    An expression of non-boolean type specified in a context where a condition is expected, near ‘;’.

  2. Killam
    August 17th, 2015 at 16:57 | #3

    nice post. I was exactly in need of this kind of implementation. I will use this as a base and develop meaningful script to suit my environment

  1. No trackbacks yet.

%d bloggers like this: