7 Microsoft SQL Server Troubleshooting Tips

This chapter describes troubleshooting tips for Microsoft SQL Server.

During installation of Mobile Security Administrative Console, Microsoft SQL Server may be chosen as a database and depending on the SQL configuration multiple authentication and connection errors can occur.

Open a command line window and enter a runas command using the service account. When a new command window opens, enter:

sqlcmd /?

In this example, the Mobile Security Administrative Console did not launch due to a database error. Mobile Security Administrative Console tries to access the SQL instance based upon what was entered in the installer for the MS SQL Server. Execute the runas command, which opens a new command line window, as follows:

runas /env /user:yourserviceaccount@example.com cmd

sqlcmd -S devsql\sql2,1433 
Msg 18456, Level 14, State 1, Server DEVSQL, Line 1 
Login failed for user 'BITZERDEV\acpservice'. 
sqlcmd -S devsql\sql2,1433 -d lattice 
Msg 18456, Level 14, State 1, Server DEVSQL, Line 1 
Login failed for user 'BITZERDEV\acpservice'.

To increase security, the error message that is returned to the client by SQL Server deliberately hides the nature of the authentication error. However, in the SQL Server error log the correct reason can be found. In this type of error, check the instance logs first. If the error is not found then check the default or base SQL management logs to find the error. Here is an example log.

08/12/2012 13:08:54,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11. 
08/12/2012 13:08:38,Logon,Unknown,Login failed for user 'BITZERDEV\acpservice'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.100.73]

The key to the message is the State which the server will accurately set to reflect the source of the problem. In the example above, State 11 indicates the wrong port is used to access the database, which was caused by the wrong port being specified during setup.

The common error states and their descriptions are provided in the following table:

Error State	Description
1	Error information is not available. This state usually means you do not have permission to receive the error details
2 and 5	Invalid userid
6	Attempt to use a Windows login name with SQL Authentication
7	Login disabled and password mismatch
8	Password mismatch
9	Invalid password
11 and 12	Valid login but server access failure
13	SQL Server service paused
18	Change password required