Using Logon Trigger to block remote SQL Server connections from SSMS or other applications

Due to some business requirements/rules,  some SQL Server instances only allow conditional remote connections such as only a internal web application that can make remote connections, but not allow any other application such as SQL Server Management Studio to do that.

In SQL Server 2005 onwards, we can utilize Logon Triggers to address such business requirement. For instance,  we have the following business rule in the company: It does not allow remote connections from SQL Server Management Studio to this server instance. To do this, we could create the following Logon Trigger:

-- This server trigger will block all remote connections from
-- "Microsoft SQL Server Management Studio" or "Microsoft SQL Server Management Stuido - Query"
USE master;
GO
CREATE TRIGGER ssms_remote_block
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
            FROM sys.dm_exec_sessions AS es
            WHERE
                es.login_name = ORIGINAL_LOGIN() AND -- The current login
                es.host_name <> 'ServerNameOfSQLServer' AND -- Make sure it works only on remote connections
                es.program_name LIKE 'Microsoft SQL Server Management Studio%')
    ROLLBACK;
END;

After creating the above server trigger, any connection made remotely from SQL Server Management Studio will be blocked, which will not affect local connections, however.

About these ads

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing

Posted on April 11, 2011, in Uncategorized. Bookmark the permalink. Leave a comment.

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: