locked
SQL Server Logon Trigger problem RRS feed

  • Question

  • Hi All,

    We created the following Sql Server Logon Trigger on our Production server.

    Intention is to restrict the sql logins from accessing the databases through SSMS(restriction not applies for connection through web application).

    It worked fine for 4 hours, after that, it doesn't allowed any logins(including Windows Login).

    Due to this there was a Half-an-hour down in production.

    Is there any problem in the following code. Can anyone guide us in this regard.

    T-sql Code follows:

    CREATE

    TRIGGERTrigger_restrict_sql_logins

    ON

    ALLSERVER

    FOR

    LOGON

    AS

    BEGIN

    if

    (selecttypefromsys.server_principalswherename=ORIGINAL_LOGIN())='S'andORIGINAL_LOGIN()NOTIN('sa')andapp_name()='Microsoft SQL Server Management Studio - Query'

    begin

    ROLLBACK

    end

    END


    Thanks in advance,

    K.P.Senthil Kumar

    Wednesday, July 9, 2014 1:00 PM

Answers

  • Be sure you understand that using APPNAME like this is not providing much security. It will turn back a casual SSMS user but not someone will skills. For example, using Management Studio, in the Connect to Database Engine dialog box, click Options. On the additional Connection Parameters tab, type ;app='fake'

    Now check SELECT APP_NAME(); and you will get fake as the app. This parameter is a volunteered attribute and not enforced in any way. Useful for an application, but not as a security method.

    Also, by looking for 'Microsoft SQL Server Management Studio - Query' you are not blocking connections from sqlcmd, or Microsoft Access, or Excel.

    All of this might be OK if you are just trying to keep out people who are mistakenly connecting with SSMS but it won't slow down your developers much.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Sofiya Li Thursday, July 10, 2014 9:23 AM
    • Marked as answer by Sofiya Li Wednesday, July 16, 2014 9:51 AM
    Wednesday, July 9, 2014 4:31 PM
  • I would like to emphasize Rick’s answer. The application name property is easily spoof-able, and it is not a very good security practice to depend on it for any security mechanisms. It may be a good defense in depth to keep honest people honest (i.e. avoid mistakes), but you cannot rely on it as a security mechanism.

    I would recommend adding auditing to your system (http://msdn.microsoft.com/en-us/library/cc280386.aspx) and keep an eye for any activity that may look out of the ordinary.

    -Raul Garcia

      SQL Server Security

    -------------------------------Implies no warranty


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by Sofiya Li Thursday, July 10, 2014 9:23 AM
    • Marked as answer by Sofiya Li Wednesday, July 16, 2014 9:51 AM
    Wednesday, July 9, 2014 7:30 PM

All replies

  • Are you sure that it was the logon triggers that caused outage in production?

    How did you come out of the situation? Do you see any messages in sqlerrorlog?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, July 9, 2014 1:15 PM
  • Yes Ashwin, messages are found in sqlerrorlog.

    I dropped the Logon trigger. Then only it allowed all the web application to connect the databases.

    Wednesday, July 9, 2014 1:17 PM
  • Could you post the error messages during the time of downtime?

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, July 9, 2014 1:45 PM
  • Be sure you understand that using APPNAME like this is not providing much security. It will turn back a casual SSMS user but not someone will skills. For example, using Management Studio, in the Connect to Database Engine dialog box, click Options. On the additional Connection Parameters tab, type ;app='fake'

    Now check SELECT APP_NAME(); and you will get fake as the app. This parameter is a volunteered attribute and not enforced in any way. Useful for an application, but not as a security method.

    Also, by looking for 'Microsoft SQL Server Management Studio - Query' you are not blocking connections from sqlcmd, or Microsoft Access, or Excel.

    All of this might be OK if you are just trying to keep out people who are mistakenly connecting with SSMS but it won't slow down your developers much.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Sofiya Li Thursday, July 10, 2014 9:23 AM
    • Marked as answer by Sofiya Li Wednesday, July 16, 2014 9:51 AM
    Wednesday, July 9, 2014 4:31 PM
  • >Intention is to restrict the sql logins from accessing the databases through SSMS(restriction not applies for connection through web application).

    You should implement administrative control over the production database.  Simplest: anyone not authorized accessing the production database, 5% salary cut.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Wednesday, July 9, 2014 4:35 PM
  • I would like to emphasize Rick’s answer. The application name property is easily spoof-able, and it is not a very good security practice to depend on it for any security mechanisms. It may be a good defense in depth to keep honest people honest (i.e. avoid mistakes), but you cannot rely on it as a security mechanism.

    I would recommend adding auditing to your system (http://msdn.microsoft.com/en-us/library/cc280386.aspx) and keep an eye for any activity that may look out of the ordinary.

    -Raul Garcia

      SQL Server Security

    -------------------------------Implies no warranty


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by Sofiya Li Thursday, July 10, 2014 9:23 AM
    • Marked as answer by Sofiya Li Wednesday, July 16, 2014 9:51 AM
    Wednesday, July 9, 2014 7:30 PM