SSAT Overview


No matter how experienced you are in SQL Server but sooner or later you may face a trouble with authentication and spend a lot of time to figure out what’s wrong with it. I’m going to present you the tool which can greatly simplify your life in such a case. It is SSAT, which is SQL Server Authentication Troubleshooter. This article provides you an overview of SSAT.

First of all you should download the latest version of SSAT from the project’s site http://ssat.codeplex.com/. There are two versions of SSAT: console and GUI. The difference between them is that the GUI is more handy and more user friendly than console. In this article we will consider GUI version.

Documentation for the console version can be found here: http://ssat.codeplex.com/documentation?version=2 

Requirements

1.                  SQL Server 2008 or higher;

2.                  You have to be able to connect to SQL Server as an Administrator (Control Server permissions required);

3.                  .NET Framework 2.0.

 

Quick Start

After you failed to login to the SQL Server do the following:

1.                  Run SSAT. You should see the following

 

It is pretty simple UI. In the field Server you should type name of the server to which you cannot connect (also you can press Refresh button – it’ll enumerate all SQL Servers in the network). In order to use this tool you must login under account with administrator rights (Control Server Permissions required). If it is a Windows account just check “Windows Identity”. Otherwise type Login and Password of account. Press Connect. If connection succeeded then you should see the following:

As you can see there is information about connection process in the console field. Also if connection established there will be green light and words Connection: Open in the left bottom corner.

2.                  Try to login again to the SQL Server and wait until your receive failure error. In the example below I provided “Invalid Login” as a Login name. In Management Studio I’ve got this dialog window:

 

 

3.                  Now go to the SSAT and press green arrow (or Analysis->Troubleshooter from menu or F5)

 

It can take some time to retrieve info about failure.So as you can see from the screenshot it took 3 seconds to execute on my computer. So now you can see an info about the failure:

 

 

It says that there is no login Invalid Login and enumerates all logins.

 

Let’s see another example. I have a login Developer on my server, and I accidently wrote Develope (I missed last letter). So my great assistant SSAT tells me the following:

 

 

As you can see it shows you all logins on the server and tells: Did you mean this login: Developer! It’s great!

 

Curious programmer may ask: what if there are no failures or  more than one failure?

If no “Login failed” errors occurred in a timeframe between you connected to the Server using SSAT and you started analyzing, SSAT will spend some time trying to retrieve some error and after that it will say that there are no failures.

If there are several failures SSAT will try to retrieve all of them and separate by red lines like in the picture below.


 

Note, that the tool will try to do it’s best to analyze each error, but if the nature of the failures are different (like one login failed because of misspelled login name while another one failed because it is a local windows accont connecting from a remote machine) there may be some confusion in the analysis. So, it is always better to analyze each error separately. If you need statistics for numerous login failures, use monitoring mode (see below in this document).

 

 After analysis is done you can save this report (not all content of Console Field, only report) in text file. Just press File -> Save Report.

 

Advanced Topics

 

There are two modes the tool can operate in: monitoring mode and analyzing mode (Above we saw 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.). 

 

So now let’s see the monitoring mode.

Here I’m trying to login with an invalid login name again. I’m opening SSAT and pressing  “Run Monitor” button (the one with a diagram) (or Analysis->Monitor or F6).

 

Some black magic occurred behind the scenes and the magnificent fancy creature of developer’s minds produces the results

 

 

That’s all!

 

Limitations

 

So as everything good it has several limitations: 

1.                  It works only with SQL Server 2008 or higher version;

2.                  You have to be able to connect to SQL Server as an Administrator (Control Server permissions required);

3.                   This tool only currently investigates issues on the SQL server side, and if there are some connectivity issues in Windows (e.g. Kerberos authentication issues), the tool will not be able to pinpoint them. Hopefully, we can improve that in future versions;

4.                  It requires .NET Framework 2.0 or higher version.

 

Last Word


We will highly appreciate your kind assistance, please give us your feedback about SSAT at project’s site http://ssat.codeplex.com/. We are glad to hear about your experience with SSAT.


Development team:

Andrey Artemiev

Denis Reznik

Lyudmila Fokina

Last edited Aug 2, 2010 at 7:26 PM by Lyudmila, version 10