How To: Use SQL Profiler to Monitor Login Failures on The SQL Server

Version 3

    Purpose

    Sometimes you may notice login errors on the SQL server, such as "Login failed for user 'UserName'. Reason: Failed to open the explicitly specified database 'DatabaseName'." in the event logs. If you have multiple programs using the same account to access the database, you may want to use SQL Profiler to help monitor if an Ivanti product or any other products are causing the continuous login error.

     

    Step by Step

    1. Login to SQL Management Studio.

    2. Go to Tools>SQL Profiler.

    3. Trace Properties will open. Under the General tab, input Trace Name, and select Save to file if needed.

    4. Go to Events Selection tab, select Show all events and Show all columns.

    5. Under Events column, expand Error and Warningsf, and find User Error Messages. Then select at least these columns: ApplicationName, HostName, LoginName, SPID, StartTime, TextData, Severity, State, ClientProcessID, and Error.

    6. Under Events column, expand Security Audit, and find Audit Login Failed. Then select the same columns as above.

    7. Clear the checkbox before Show all events and Show all columns, make sure no other events will be captured. The final selection should look something like the one below:

    event selection.png

    8. Click on Run.

    9. Wait until the error is reproduced, then click on Stop selected trace. You can view the results now.

    stop.png

    10. TextData column will show you the error message you see in the Windows Application event logs. ApplicationName and ClientProcessID will tell you the name and PID of the program making the login requests. HostName column tells you where this login error is coming from.

    PROFILER 1.png

    PROFILER 2.png

     

    11. Now you can login to the server making the login request, and find the PID and corresponding program that is responsible for making these login failures.

     

    Relevant Articles

    How To Gather a SQL Trace & Simple Query Suggestions

    How to set up a simple or advanced SQL trace for trouble shooting database related issues