SQL Server Authentication Troubleshooter
I am posting this article on behalf of my teammate Lyudmila.
A new tool to help investigate ‘Login Failed’ errors in SQL Server has been recently implemented and published on CodePlex: https://ssat.codeplex.com/
The tool is implemented in C# and uses xEvents to capture “Login Failed” errors. It also uses security ring buffer information (from sys.dm_os_ring_buffers) to retrieve error related information and do the analysis.
It is published under the Microsoft Public License (more details here: https://ssat.codeplex.com/license).
Currently it is implemented as a simple command line tool. There is still room for improving this tool to make it more useful in general, so any contribution or feedback you have is welcome!
You can download sources and binary, play with the tool, submit your changes (if you want to contribute to the tool) or just give your feedback and suggestions on discussion page ( https://ssat.codeplex.com/Thread/List.aspx ).
More details about the tool:
There could be number of reasons for ‘Login Failed’ error in SQL Server– from insufficient permissions and policy problem to a Win API failure. This tool will help an Administrator of the SQL Server to find out why some particular login is failing to get authenticated. All the knowledge used in the tool is actually available to the customers, but unfortunately because of lack of documentation and the logic complexity it often becomes difficult to find the exact cause. The goal of this tool is to help analyze all available information and give a suggestion about the cause of the failure.
Limitations:
- The tool can work with SQL Server 2008 and later versions;
- You have to be able to connect to SQL Server as an Administrator (Control Server permissions required).
- This tool only currently investigates issues on the SQL server side, and if there are issues in Windows (e.g. Kerberos authentication issues), it will not be able to pinpoint. Hopefully, we can improve that in future versions.
Usage:
There are two modes the tool can operate in: monitoring mode and analyzing mode.
In analyzing mode the tool will analyze a single login error and return suggestions about possible cause of the error (like: ‘this login was denied connect to the endpoint’, for example or ‘this database is offline’ etc.).
In monitoring mode the tool will just collect the statistics about 'Login failed' errors (statistics will be grouped by error#, client name, application name etc.).
ATSDriver.exe connection_string [-M ]
connection_string Valid SQL Server connection string of a user with Control Server permission in order for tool to capture error information.
-M If started with this flag, the tool will work in monitoring mode, collecting statistics about 'Login failed' errors.
Example:
ATSDriver.exe "server=SQLServer01;Trusted_Connection=true" -M
Instructions:
Start the tool and wait for the prompt (error analyzer mode):
Ready. Try to connect to SQL Server now.
Press 'A' after you hit 'Login failed' error or 'C' for exit.
or (monitoring mode):
Monitoring has been started.
Press 'A' when you are ready to get the statistic or 'C' for exit.
If in 'Error analyzer' mode, try your failing login attempt, then press 'A'. It will take several seconds for the tool to retrieve data and analyze it.
If in 'Monitor' mode, continue your normal workflow; press 'A' when you are ready to get statistics on the 'Login failed' errors. It will take several seconds for the tool to retrieve data and calculate statistics.
Note:
- In Error analyzer mode (without -M flag) the tool analyzes a single error. If multiple 'Login failed' errors
occurred while the tool is in analyzer mode, the report could still be helpful, but it can’t guarantee correctness in this case due to the multiple login errors that are generated.
- In Monitoring mode the tool gets the statistics on all the 'Login failed' errors occurred without further analysis.
- A side effect of this tool (in this version) - the xEvents files will be left in SQL Server Data directory. It is up to the admin (or user of this tool) to delete these files.