SQL Server – How to Deny Access to Certain Logins Over SSMS but Allow Over .Net SqlClient

access-controlconnectionsloginspermissionssql server

We have a situation where Developers do not have any UPDATE permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).

Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1 (that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.

DBA can not change password for SQLLogin1 without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1 is maintained by Developer.

Question:

Is there a way to deny access to SQLLogin1 SQL login, but only if it is connecting over SSMS?

At the same time if SQLLogin1 is connecting over .Net SqlClient Data Provider (program_name in the sys.dm_exec_sessions), it must be allowed to login.

This way we want to not let Developer connect over SSMS using SQLLogin1, while the application that is using SQLLogin1, would still be able to connect.

Best Answer

You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using SSMS.

For example:

CREATE TRIGGER strRejectSSMSConnectionForSQLLogin1
ON ALL SERVER FOR LOGON
AS
BEGIN

    IF ORIGINAL_LOGIN() = N'SQLLogin1' AND PROGRAM_NAME() LIKE N'Microsoft SQL Server Management Studio%'
    BEGIN
        RAISERROR('Direct connection by SSMS refused.', 16, 1)
        ROLLBACK
    END

END

The ROLLBACK inside the trigger will reject the connection (there's an implicit transaction wrapping the call to the trigger on the logon event).

Be careful when implementing logon triggers, if not coded properly you will be rejecting logins that should be able to login (including your own!). Make sure to test on test/dev environments first.

Keep in mind that this code is executed before the session is created, so system views that rely on the session id (SPID) won't contain the currently checked login until the triggers ends without rollback or high enough failure.

Related Question