none
SQL Security - Logon Failures repeating RRS feed

  • Question

  • Fellow SQLers,

    I am reviewing some auditing on the Sql Log.  So, I can try to connect to an instance with invalid password. It records one set of records in the log showing records - good. An error number record and a message record.

    But, If I log into the instance and db correctly, then try to jump to another db on that server which my id DOES NOT have permissions, it generates a set of failed logins every 10 seconds up to one minute. Is this some type of loop back processing causing this. I was expecting just one set of records and ONLY want one set of records for reporting.

    Thoughts.

    Thanks,

    MG

    Friday, July 5, 2019 7:18 PM

Answers

  • But, If I log into the instance and db correctly, then try to jump to another db on that server which my id DOES NOT have permissions, it generates a set of failed logins every 10 seconds up to one minute. Is this some type of loop back processing causing this. I was expecting just one set of records and ONLY want one set of records for reporting.

    Sounds like Intellisense is pulling your legs. Try the same operation from SQLCMD. I would not expect the extra logging in this case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mg101 Monday, July 8, 2019 3:11 PM
    Friday, July 5, 2019 8:56 PM

All replies

  • But, If I log into the instance and db correctly, then try to jump to another db on that server which my id DOES NOT have permissions, it generates a set of failed logins every 10 seconds up to one minute. Is this some type of loop back processing causing this. I was expecting just one set of records and ONLY want one set of records for reporting.

    Sounds like Intellisense is pulling your legs. Try the same operation from SQLCMD. I would not expect the extra logging in this case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mg101 Monday, July 8, 2019 3:11 PM
    Friday, July 5, 2019 8:56 PM
  • Hi mg101,

     

    >>But, If I log into the instance and db correctly, then try to jump to another db on that server which my id DOES NOT have permissions, it generates a set of failed logins every 10 seconds up to one minute. Is this some type of loop back processing causing this. I was expecting just one set of records and ONLY want one set of records for reporting.

     

    Would you please show us the screenshot of the error log if it is convenient?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 8, 2019 9:21 AM
  • Thanks Erland. I can confirm it is Intellisense. What Intellisense seems to do is continuing/attempting to connect/open with the USE statement even without you running anything. As long as you have typed it in before hitting RUN, it will try to open the db for the USE. In my case, as soon as I typed in USE with a db name,  intellisense was actually trying to open it without me running it. But, it will keep trying for about 60 seconds  even when you remove the USE in the same query window or close the query window!!!

    So here is the test – using SSMS:

    Using SQL 2016

    Make sure Intellisense is enabled for this test. In SSMS, Options|Text Editor|Transact-SQL|Intellisens. the Enable Intellisense checkbox should be checked.

    1 – Open ssms with your id (admin level).

    2 – Make a dummy db.

    3 – Open current sql log to view the window – you will need this to view the log for the test.

    4 – Create a SQL Login (i.e. testsecurity) that has access to the dummy db. Make the dummy db the default db  for that user.

    5 -  Using SSMS, Connect to dummy db with that user and enter a bad password.  Using new connection.

    6 – Check you SQL Logs, you will find it registers the error just once like it should – one record for the error description and one record for the error detail number, severity, and state. This has nothing to do with Intellisense but I just want one to see a general Login Failure record.

                    Message was: Login failed for user 'testsecurity'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    Error: 18456, Severity: 14, State: 8.

    7 -  Using SSMS, Connect to the dummy db with that user and a valid password.

    8 -  Open a query window for that user in the dummy db. It will show it is in the default dummy db.

    9 – Type in USE and some other db it has NO permission to. Type nothing else. Do not execute.

    10 – Check your error log immediately. You do not have to execute the Use statement. Refresh error log.

    11 - In the sql log,  you will see the below message. It will show the two record pair about every 10 seconds for about one minute. Keep refreshing log display to view the pattern.

    Message was: Login failed for user 'testsecurity'. Reason: Failed to open the explicitly specified database 'yourdbname'. [CLIENT: <local machine>]

                    Error: 18456, Severity: 14, State: 38.

    So, let’s see if it is intellisense.

    12 – Turn off intellisense. In SSMS, Options|Text Editor|Transact-SQL|Intellisens. Uncheck the Enable Intellisense checkbox at the top.

    13 – Close down SSMS. Not necessary but let’s just do this so we start clean. Launch ssms with your id,

    14 – Open up SQL Log window.

    15 -  Connect to SQL again with TestSecurity and valid password.

    16 – Open query window with TestSecurity connection.

    17 – Type in USE and some other db that testsecurity has no permission to. Type nothing else. Do not execute. Check your SQL Log – no NEW errors will show. Refresh log display numerous times and check. No new errors will show.

    18 -  Go ahead and Execute the use statement. The query window will generate the below message.

                    “The server principal "testsecurity" is not able to access the database "yourdbname" under the current security context.

    19 – Check/refresh  your error log immediately.  There is NO ERROR for the above action.  I was expecting an Open Failure log records to be generated as it did with Intellisense.

    In summary, sql logging with Intellisense on or off delivers a couple of different logging patterns with this scenario.

    20 - Using SQLCMD, the USE testing did not generate anything in the error log either.

    This information/cause also is tied to another item I put in the forums labeled "SQL Audit - Failed
    Logins - not collecting failure from sqlcmd".

    Also the SSMS 2016 version I am using is 13.0.16106.4

    Thanks,

    MG



    • Edited by mg101 Monday, July 8, 2019 4:15 PM addition words
    Monday, July 8, 2019 3:11 PM
  • HI Dedmon, Please read my reply to Erland. I have the full test and also output messages.

    MG

    Monday, July 8, 2019 3:12 PM