In a previous article i discussed that how to restrict SQL Server login to connect from out side the application. Logon trigger was utilize to achieve this task. The puprose behind it was to make sure that permission rich logins used in application should not be allowed to access SQL Server by any way other than managed application code.
However it is possible that any one may use that login through application for unit testing or to execute ad-hoc code from inside the application code. I was asked to also restrict such access that is adhoc or for unit testing from developers desktops. So the requirement is that login used in application could only access SQL Server after fulfilling two conditions
- Login is requesting to connect from inside the application code
- Login is requesting from no where else but web server (application server)
My previous article covers the first requirement but it lacks to check the second requirement. Following code (modified version of previous article) would make sure to check both the conditions prior to granting login access
CREATE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER WITH EXECUTE AS 'AppUser' FOR LOGON AS BEGIN DECLARE @ip VARCHAR(16) SELECT @ip = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID IF ORIGINAL_LOGIN()= 'AppUser' AND ((SELECT TOP 1 Program_Name FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'AppUser' Order By Session_Id Desc) <>'.Net SqlClient Data Provider' or @ip <> '192.168.1.202') -- Provide IP address of web server here ROLLBACK; END GO ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER GO
In this way the login used for application is restricted to access only from web server through application code only.