Identifying The Source Of ERRORS Authorization SQL Server (ERROR 18456)

All of us occasionally have to determine the source of errors authorization (login failures), and observing the growth of the number of topics on the forums asking for help in this matter, I decided to start with a quick way to find a precise definition of these errors. On the assumption that the topic had been sucked many times and closed in other forums or blogs, but was surprised to find that although there are many articles about what is “authorization error” and that means all the codes that are returned in the error message, I could not find step by step description of what should make the DBA to determine where the request came to authorize the original error.

This record – my attempt to eliminate this gap and show how to select the process that causes this problem.

The technology is not highly dependent on the version, so it is not critical to the package that you will do this, but I guess that you know how to use SQL Server Profiler to trace requests.

Login Error 18456

Login failed for an error 18456 and is accompanied by an entry in the log SQL Server (SQL Server 2000 does not display the IP address):
2011-02-05 09:40:24.55 Logon Error: 18456, Severity: 14, State: 8.
2011-02-05 09:40:24.55 Logon Login failed for user ‘Domain \ User’. [CLIENT: xxx.xxx.xxx.xxx]
2011-02-05 09:40:24.55 Logon Error: 18456, Severity: 14, State: 8.2011-02-05 09:40:24.55 Logon Login failed for user ‘Domain \ User’. [CLIENT: xxx.xxx.xxx.xxx]

Severity in the log indicates the severity of the error. Severity: 14 means that the error can be corrected by the user, which is quite logical for errors authorization.

The next number indicates the number severity. Most of the errors have associated state number, which indicates additional information is usually unique for each error. Authorization for the error condition 8, as shown in the example above, indicates that an incorrect password was used.

Consequently, the number of the state provides information about the authorization and it is often sufficient to determine the cause of the error 18456.

The table below shows the decoding of certain values:

State Error Description

1 = Account locked
2 = User ID is not valid
5 = User ID is not valid
7 = Used login is disabled
8 = Wrong password
9 = Inappropriate password
11-12 = Authorization is true, but access to the server can not
16 = authorization is true, but access to the selected database is not allowed
18 = Expired password
27 = The original database was not found
38 = Authorization is true, but the database is not available (or not permit)

The next information – login (SQL Server or Windows Cloud Hosting), then the IP address of the host attempted authentication, which provides useful information for cross-searching, to confirm that we investigate the host to select an authorization error.

Definition: An error has occurred.

If the information contained in the error log is not sufficient to determine the source of the error, then the next step is to start tracing the SQL Server for more information.

The shortest way to detect errors authorization is through using SQL Server Profiler (SSP).

If you are using SQL Server 2005 or higher, you still enabled router to the default (which is enabled by default in the installation of the box), then you do not want to start a new trace. Check instead my record, The SQL Server default Trace .

If you have an earlier version of SQL Server or tracing is disabled by default – read on.

Start the SSP, and using either your favorite template, or create a new (File> Templates> New Templates …), make sure that you have the following columns:

ClientProcessID
Hostname
LoginName
NTUserName
NTDomainName
ApplicationName

These columns can be found in the Trace Properties dialog box to tab Events Selection. If they are not visible, select “Show all columns”. Please note that the SPID column is selected by default and cannot be removed.

In the column, select the Events Field Audit Login, under Security Audit. Since we are only interested in authorization error, it will be only the selected event, and we are confident that our intervention give minimum load on the system. In the “battle” systems are not desirable (IMHO) run SSP trace on the server, it is always advisable to use a trace on the server side .

The figure below about Trace Template:

Search pattern error has occurred

It may look a bit sad, but we are only interested in finding specific errors.

Step 1

Save the modified template trace and start a new trace, defining the stored template as a template for the new trace and wait for the error has occurred. Stop the trace after the error has occurred.

Step 2

Hostname column should contain an entry name of the server on which there has been an invalid username and ClientProcessID must contain the ID of the problematic process (PID) (or processes if there are several related processes).

Step 3

We go to a server error occurred and peruse the appropriate processes with Task Manager (Task Manager). To view the PID from Task Manager, run the manager (Shift + Ctrl + Esc), go to View-> Select Columns … and choose the option PID (Process Identifier) and click Ok.

Click on the Processes tab to view the processes running on the server (make sure that is checked with Show all processes from all users) and click on the column heading PID, to sort descending or ascending order.

Step 4

Once you determine proess on its PID, is all-just a matter of determining where the process stores the authorization settings SQL Server and check them Typically, this process is a service, so this is running through the Services snap-in Control Panel, or Start- > Run-> services.msc

So, happy hunting!