0

We have a server trigger used to prevent some logins from accessing SQL Server via SSMS in our DEV environment.

CREATE TRIGGER [Deny_SQLLogin_SSMS_Trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @login    SYSNAME = ORIGINAL_LOGIN()
    DECLARE @app      SYSNAME = APP_NAME()
    DECLARE @hostname SYSNAME = HOST_NAME()

    -- is this login in our prohibited list.
    IF @login IN ('some_user', 'some_user2', 'some_user3') AND @app LIKE N'%Management Studio%'
    BEGIN
        -- yes so send back an error
        --ROLLBACK;

        -- .. and record the attempt.
        INSERT msdb.dbo.AuditLog([Login],App,HostName,EventTime)
        VALUES (@login, @app, @hostname, SYSUTCDATETIME());

        THROW 51000, 'Connection not allowed.', 1;  
    END
END

GO

ENABLE TRIGGER [Deny_SQLLogin_SSMS_Trigger] ON ALL SERVER
GO

This works just great when tempdb isn't full but when it is, all logins get rejected.

Message
Logon failed for login 'some_user' due to trigger execution. 


Message
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

No one can login from anywhere and we are forced to restart the instance. This is not going to be possible for our production instances when we start using the login trigger there as well.

So how can we design the trigger so that logins don't get blocked in case tempdb fills up unexpectedly?

  • 1
    Have you tried to figure out why tempdb is getting full ? Address that problem. you are trying to address 2 different problems at the same time. – Kin Shah Nov 14 '16 at 15:49
  • Hi @kin. Yes definitely the tempdb full problem was resolved. – Craig Efrein Nov 14 '16 at 15:50
  • 4
    This trigger really doesn't protect you from anything. You can change connections so that they pass a different string for the app. This is trivial with SSMS. Just choose the "Additional Connection Parameters" tab when connecting and add "Application Name=NotSSMS". It will bypass your trigger, and full access is granted. You can validate by checking sys.dm_exec_sessions for the given spid and checking the program_name. – Nic Nov 14 '16 at 16:12
  • My thoughts if you create sql Logins that you try to get away from your servers maybe you should look at using managed Service accounts to allow your Application Servers to access the dbs instead of sql Logins? – Magier Nov 14 '16 at 21:45
  • Hello @Nic, thanks for your answer. The problem is that all logins get rejected regardless of whether or not they come from SSMS, dotnet, everything gets blocked. If the trigger isn't there, logins are still allowed even if tempdb is full. – Craig Efrein Nov 15 '16 at 5:55
1

I would like to point out an another way to do this instead of the trigger. As @Nic pointed out in the comment, your solution does not prevent users from connecting through SSMS (as you can change the app name).

You should create SQL-logins for the application or use service accounts if you are using Active Directory and create windows logins for them. This is only solution that does not allow your denied logins to connect at all and it does not matter if tempdb is full.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.