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.