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.