Using Logon Triggers to Block Remote SSMS Connections

security

If you have some specific business requirements that force you to alter remote logins to your database instance, you might have wondered what easy way might work. Using a technique that is fairly common with web applications connecting to SQL Server instances, to can only allow conditional remote connections. Using this technique you can not allow any other application, such as SQL Server Management Studio, to connect to the database remotely.

Since SQL Server 2005, you can use Logon Triggers to meet this business/compliance requirement. This is an example of using Logon Triggers:

-- This logon trigger will block all remote 
-- connections from SQL Server Management Studio

USE master;
GO
CREATE TRIGGER remote_ssms_block
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS 
    (SELECT * FROM sys.dm_exec_sessions AS es
     WHERE es.login_name = ORIGINAL_LOGIN() -- current login
     AND
     es.host_name <> 'ServerName' -- use your server name
     AND
     es.program_name LIKE 'Microsoft SQL Server Management Studio%')
ROLLBACK;
END;

Once you add this trigger, any remote connection made from SQL Server Management Studio will be blocked. This will not impact local connections to the server, which is a good thing. You might also change this trigger to block connections from specific computers, or to only allow connections from specific computers.

Make sure you test this before you put it into production.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s