Archive

Archive for the ‘Security’ Category

How to get orphaned logins for all databases?

April 4, 2016 2 comments

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name.  This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO
 
CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
GO
 
sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DP.sid
      , SP.name
      , DP.name
      , DP.type_desc
  FROM sys.database_principals DP
  LEFT JOIN sys.server_principals SP
    ON DP.sid = SP.sid
 WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
   AND DP.sid IS NOT NULL'
GO
 
  SELECT *
    FROM #Output
   WHERE ServerLoginName IS NULL
      OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName
GO

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Categories: Administration, Security Tags:

How get all users and their role mappings from all databases?

April 2, 2016 5 comments
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO

CREATE TABLE #Output (DatabaseName VARCHAR(255), RoleName VARCHAR(255), UserName VARCHAR(255))
GO

sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DR.name
      , DP.name
FROM [?].sys.database_principals  AS DR
JOIN [?].sys.database_role_members AS DRM
   ON DR.principal_id = DRM.role_principal_id
JOIN [?].sys.database_principals AS DP
   ON DP.principal_id = DRM.member_principal_id
WHERE (DP.name <> ''dbo'')'
GO

SELECT *
  FROM #Output
GO
Categories: Administration, Security Tags:

Service Principal Names for SQL Server

June 16, 2014 1 comment

Service Principal Names (SPN) get used for Kerberos authentication.  SQL Server require that all instances have SPNs configured, if not Kerberos authentication fails.  By default starting with Windows 2008 all communication between clients and SQL Server is first attempted on Kerberos.  If it fails, it falls back to NTLM.  Often if you have Kerberos issues you might also have SSPI error messages in your SQL Server errorlog.

So question can be asked, if it falls back to NTLM and I can get my work done.  Why do I care?  Simple answer is delegation.  With incorrect configured Kerberos, delegation cannot be completed and logins fail.  For example on delegation, read my post on Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ with SQL Server Reporting Services.

So what SPNs we need? Simple answer two SPN per SQL Server instance.

  • MSSQLSvc/SQLServerName[:Port] Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN[:Port] Domain\SQLServerServiceAccount

Depending on your configuration, SPNs can look different.  We can manage SPNs two ways; 1) using SETSPN utility 2) using Activity Tool call EDITADIS.  Most people rely on SETSPN, as other tool is for Active Directory administrators.  And they (AD Admins) don’t like it when we mess around with their stuff; like we don’t like it when they mess around with our stuff ;-).  So lets talk about various forms of SPNs you can create.

Example #1: Default Instance, Default Port (1433), Using Domain Service Account

  • MSSQLSvc/SQLServerName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN Domain\SQLServerServiceAccount

Example #2: Default Instance, Non-Default Port, Using Domain Service Account
Example #3: Named Instance, Static Non-Default Port, Using Domain Service Account

  • MSSQLSvc/SQLServerName:Port Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\SQLServerServiceAccount

Example #4: Default Instance, Default Port (1433), Using Computer Account *

  • MSSQLSvc/SQLServerName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN Domain\ServerName$

Example #5: Default Instance, Non-Default Port, Using Computer Account *
Example #6: Named Instance, Static Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:Port Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\ServerName$

Example #7: Named Instance, Dynamic Non-Default Port, Using Domain Service Account **

  • MSSQLSvc/SQLServerName:InstanceName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\SQLServerServiceAccount

Example #8: Named Instance, Dynamic Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:InstanceName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\ServerName$

* An instance is consider using computer account when it is running under built in accounts, such as Network Services.
** Generally we do not recommend dynamic port as managing SPN manually can be difficult.  Also for us to use InstanceName, the SQL Server Browser service must be running.

So after reading this you are going that’s TOO MUCH TO REMEMBER!

I agree, that is why SQL Server can create and destroy this SPNs by itself without user interaction. However in order to do this, we must grant SQL Server Service Account or Computer Account permissions to Read/Write SPN Property on itself (Reference).

Now there is one scenario where we do not want to grant this permission and mange the SPN manually.  Please reference KB2443457. Copied the issue from KB for completeness.

  1. The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
  2. The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
  3. The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
  4. The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
  5. When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B. Since, the SPN still exists Node B does not register the SPN.
  6. After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.

Blog post cross posted on …

  • SQLCAN (WordPress), Link.
  • SQLCAN (MSDN), Link.

Cross-Domain Authentication for SQL Server

July 29, 2013 Leave a comment

Let me set the stage, we have two SQL Servers, SQLA and SQLB.  SQLA is in DomainA and SQLB is in DomainB.  I would like to access resources on DomainB.SQLB using login DomainA\UserA via SQL Server Management Studio.

There are many articles online that talk about doing this kind of work using impersonation and such.  However all those cases are for untrusted domain or non-domain.  However in my case I am working on Two-Way Restricted Forest-Level trust.  Even with this configured the SQL Servers could not communicate with each other.

I am not Active Directory guys (probably figured that out with my trouble over the years); anyhow, so I will not discuss how to set up two-way trust.  I leave that to you or your Active Directory Gurus.

Given you have two-way trust configured, everything work smoothly right? No!

Far from it, this has been one of the most frustrating issues to troubleshoot.  As there is almost no documentation I could find on the issue.  So what happened?

  • I can log on to DomainA using DomainB\UserB, no problems.
  • I can log on to DomainB using DomainA\UserA, no problems.
  • I can access File Resources on DomainB.SQLB\C$ from DomainA using DomainA\UserA no issues.
  • I can access File Resources on DomainA.SQLA\C$ from DomainB using DomainB\UserB no issues.
  • I can log in to SQL Server on DomainB.SQLB using DomainA\UserA from DomainB.
  • I can log in to SQL Server on DomainA.SQLA using DomainB\UserB from DomainA.

So all that works, what gives?  Well, when I tried logging into DomainB.SQLB using DomainA\UserA from DomainA.  Things broke down.

I started getting “Cannot Generate SSPI Context” error messages.  And if you have been as fortunate as me with Kerberos this is a fairly difficult message to troubleshoot.  But, don’t lose hope!  We will continue troubleshooting.

So naturally when the issue happened, i.e. failed login, I decided to check all the logs I can get my hands on.

  • SQL Server Error Logs
  • Event Viewer Security Logs on SQLA, SQLB

However none of the logs had anything.  So this meant two things 1) since I did not see anything in SQL Server Errorlog, the login was not being decided from the SQL engine. 2) Since I did not see any thing in Event viewer logs, the login was not being denied from Windows layer.

Okay! I am officially confused?  Right? Who else is involved in authentication process?  Aha! Domain Controller I though, but at last there is nothing on its logs.  I was running out of options fast, but with all my experience of troubleshooting SSPI issues.  I knew it had do something with SPNs.  I verified all the SPN, all the security, everything correct.  But it failed!

Then, what is going on?  So this is new one for me, I … umm … decided to look at the .NET Stack Trace data that is provided with error log.  No I am not .NET Developer, so I might be showing my limited knowledge in that field.  However … Lets look …

Procedure: GenClientContext
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
   at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[] sendBuff, UInt32& sendLength)
at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec)
at System.Data.SqlClient.SqlInternalConnectionTds.Login(Int64 timerExpire, String newPassword)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

So reading it from bottom up, all seems pretty foreign langauge to me.  Until I get to SSPIError, aha! Found what fires the error.  So one step below that must be what caused the error?  It was TdsPraser.SNISSPIData.  What the hell is SNISSPIData?  What data could it be reading?

Searching about (Bing’ing It!) seems it is a fairly common function name relating to “SSPI Context”.  So again it seems I am missing some kind of SPN maybe?

While working on the problem with a colleague, he decided to try using Force SPN with in ODBC.  Surprisingly it worked!  What the?  So I am missing an SPN?  BUT, all the required SPN are there, at least … all the required ones within single domain.

Since Forced SPN worked, we decided to create some new SPNs in each domain.

DomainA

  • MSSQLSvc/SQLA.FQDN.DomainA DomainB\SQLServiceAccount
  • MSSQLSvc/SQLA.FQDN.DomainA:Port DomainB\SQLServiceAccount

DomainB

  • MSSQLSvc/SQLB.FQDN.DomainB DomainA\SQLServiceAccount
  • MSSQLSvc/SQLB.FQDN.DomainB:Port DomainA\SQLServiceAccount

Trying connection after this everything works!  So I am calling these Cross-Domain Service Principal Names (CDSPNs).

Very interesting problem, very simple solution, just painful hard to figure out.  I am not Kerberos expert, nor do I know what all SNISSPIData does, however it seems that it was trying to verify the SPN and failing.  I wish the error message had just told me that, but ohh well.

Learning Experience!

And you know it never ends, this solution absolutely works.  However in troubleshooting this issue learned some new things about Kerberos.  Kerberos relies on DNS to be configured correctly.  I know it might be obvious to AD/Network/etc. guru out there, it was new to me.  Because 2-days after I wrote most of the post above I found cross-domain authentication using windows authentication working fine without the CDSPNs.  It turns out I scenario about because I could not resolve DomainB.SQLB from DomainA or DomainA.SQLA from DomainB.  The authentication kept failing, because Kerberos couldn’t find the FQDN for the server.  When I created the SPN, it no longer had to do the look (it seems!).

So short of it (and why I am leaving the above post still), Kerberos is not easy to configure.  Rather the issues are not easy to troubleshoot.  The original solution I found worked, but it was not the root cause of the problem.  Root cause was the DNS not configured correctly.  So if you are having issues here a rough checklist before you start pulling your hairs out.

1. Check you can ping each server by its name (i.e. Ping ServerName)

It should return ServerName.FQDN (IP), if it doesn’t we got any issue.

2. Check you can do reverse look up by its IP (i.e. PING -a xxx.xxx.xxx.xxx)

It should return ServerName.FQDN(IP), note the “-a” HAS to come become the IP to do reverse look up.

3. Have correct Service Principal Names for SQL Server Engine (two-three SPNS, 1) ServerName.FQDN Domain\ServiceAcct 2) ServerName.FQDN:Port Domain\ServiceAcct, and 3) if it’s named instance ServerName.FQDN:InstanceName Domain\ServiceAcct)

Check SPN using SETSPN -L Domain\ServerAccount, if missing add using SETSPN -A MSSQLSvc/ServerName.FQDN Domain\ServiceAcct, etc.

4. Make sure the Service Account for SQL Server is still active in active directory (i.e. password is correct, its not locked, it hasn’t expired, it still exists, etc.)

5. Make sure the Computer Account is still in AD, it has not been tombstones or deleted by AD Explorers (a.k.a. Me!).

Good Luck… if I find another items to add I will later on :).

Windows 2003 Cluster: SQL Node Not able to Join Cluster after Changing MaxTokenSize

July 12, 2009 Leave a comment

As per Cannot Generate SSPI Context article, Link, I had changed the MaxTokenSize to fix the issue. I had read various articles before jumping into this change as being the only proper solution to the problem.

However after changing the setting and rebooting the passive node the node refused to join the cluster; looking at the error logs I have messages like …

  • Unable to get join version data from sponsor xxx.xxx.xxx.xxx using NTLM package, status 5.
  • Unable to connect to any sponsor node.
  • Failed to join cluster, status 53.
  • Physical Disk : [DiskArb] Failed to read (sector 12), error 170.

Researching on net I could not figure out results; first article I ran into was KB886717, [1]. It suggested the issue might be because the C:\Windows\Cluster folder was over size of 10MB; which in this case it was. So I removed the log file and tried to restart services with no luck.

I started reading through the log file and started to pick through error messages and looked and articles [2], [3], [5], [6], and [7]. I verified all the settings each article suggested, Group Policies, Security, firewall, etc. None of it seems to help in making sure the passive node would join the cluster successfully.

I started trouble ticket with Microsoft; they found few settings, on of being NTML Compatibility Level that needed changing as part of their troubleshooting but even after changing these settings we were still getting NTLM, state 5 error messages in Cluster.log file (State 5 means permissions denied). Talking to Microsoft they referenced few more KB articles in addition to what I had found already like [8], [9], [10], & [11] that indicated what “might” be the issue; but none of them seem to help resolve our issue.

This whole time we had not rebooted the active node as it was working successfully; but since we were hitting stone-wall every turn we decided to further troubleshoot the issue node 1 (active node) must be restarted because the errors that were being generated on Quorum disk. After rebooting Active Node, the Passive Node came active and Clustering was working successfully.

I had not read any KB article indicating the issue with MaxTokenSize and Windows Clustering, and neither had the Microsoft guys. So talking to the Kerberos experts we figured issue was similar to [7], in which if you change password or the password length is less then 15 characters of the Cluster Services account permissions or security settings are not properly hashed and generates errors when authenticating the new node to the cluster.

So if you are changing the MaxTokenSize setting on SQL Server and it is a cluster please make sure you change it on EVERY NODE; or you will have lots of strange issues that probably shouldn’t exist.

Reference Links:
[1] KB886717 Issue with Cluster Log file, Link.
[2] Problems with Microsoft Clusters, Link.
[3] How to manually re-create the Cluster service account, Link.
[4] A Windows Server 2003 based-computer that is running the Cluster service may be unable to join a cluster after the computer is first restarted, Link.
[5] Cluster Service May Not Start After You Restrict Available IP Ports for Remote Procedure Call, Link.
[6] Ask Core!, Troubleshooting Cluster Logs 101 Why did the resource failover to other node?, Link.
[7] Cluster service account password must be set to 15 or more characters if the NoLMHash policy is enabled, Link.
[8] You cannot add an additional node to a Windows Server 2003-based server cluster, and error code “0x8007042b” is logged in the ClCfgSrv.log file, Link.
[9] You receive an “Error 0x8007042b” error message when you add or join a node to a cluster if you use NTLM version 2 in Windows Server 2003, Link.
[10] How to enable NTLM 2 authentication, Link.
[11] Cluster service does not start on joining node in Windows 2000 Cluster, Link.

SSPI Context Cannot be generated

July 12, 2009 Leave a comment

I am sure everyone has seen this error there are many articles on MSDN, newsgroups, etc.; but I ran into very interesting problem with these that none of the normal articles helped. Articles like [1], [2], [3], and [4] talk about various settings that can generated that error, from being SPN (Service Principal Name), to SQL Protocol Issues, to TDS packet issue, but none of these helped me resolve the issue. As I was getting this issue with one user only; unable to solve it I kept looking around and ran across article [5]; which indicated this is an issue with Kerberos authentication. Which was indicated by previous articles also, but what was interesting it stated that the Token generated by the user account in question was larger then the default size of 12,000 bytes thus the issue.

I had no way to check how many groups the user was in, so working with Active Directory guy we created copy of account and removed one group at a time until the user was able to login successfully. So in a sense we verified that number of groups the user belonged to was causing us the issue. But I did no have a work around for this, as article [5] suggested I was thinking of changing MaxTokenSize to FFFF (65535). I needed to verify that was the issue for sure; as it was only one user who was reported the issue. I had access to far more groups and resources but did not have any issues. After researching more I found Microsoft Utility, TokenSz [6] & [7] that lets you compute the token size generated for Kerberos authentication.

Running TokenSz utility on users account and my account we got interesting information …

Users account
Name: Kerberos Comment: Microsoft Kerberos V1.0
Current PackageInfo->MaxToken: 65535

Using user to user
QueryKeyInfo:
Signature algorithm =
Encrypt algorithm = RSADSI RC4-HMAC
KeySize = 128
Flags = 2083e
Signature Algorithm = -138
Encrypt Algorithm = 23
Start:7/12/2009 7:47:09
Expiry:7/12/2009 17:47:09
Current Time: 7/12/2009 7:47:09
MaxToken (complete context) 13383

If you notice users max token was above the default allowed of 12,000; so SQL Server failed in the authentication. I checked my token size it was only 7479. Looking at how the token is generated in [5]; it includes information from nested groups. So this had major impact on the number of groups she was really in.

Solution? We decided to change the MaxTokenSize on our SQL Server.

This caused another interesting (another word for annoying :S) issue, Link. But after all issues were resolved user was able to log on successfully.

Reference Links:
[1] How to troubleshoot the “Cannot generate SSPI context” error message, Link.
[2] PRB: Error Message: Cannot Generate SSPI Context, Link.
[3] SQL Protocols: “Cannot generate SSPI Context” Error message, when connecting to local SQL Server, Link & Link.
[4] How to configure an SPN for SQL Server Databases Servers, Link.
[5] New Resolution for Problems with Kerberos Authentication when users belong to many groups, Link.
[6] How do Token Size Affect SQL Server, Link.
[7] TokenSz Utility Download, Link.

Categories: Security, SQL 2005

Authentication Issues on SQL Server Startup

May 20, 2009 Leave a comment

After we finished install SQL Server for clustering (install was excellent nothing failed, all greens, logs good); SQL Server refuses to start up. We keep getting following types of error messages in our ERRORLOG file; this was same for RTM and SP2:

2009-05-09 12:53:16.94 Logon Error: 18456, Severity: 14, State: 11.
2009-05-09 12:53:16.94 Logon Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: xxx.xxx.xxx.xxx]
2009-05-09 12:53:39.53 spid7s SQL Server is terminating in response to a ‘stop’ request from Service Control Manager. This is an informational message only. No user action is required.

I could not figure out the issue with this; so while talking to Microsoft I found two interesting facts:

  1. I was able connect to SQL Server using \\.\pipe\SQLLocal\NamedInstanceName\ (so using Named Pipes) using Windows Authentication and SQL Server authentication.
  2. I was able to log into ServerName\NamedInstanceName using SQL Server authentication only.

So Microsoft engineer thought it was an issue with NTLM so he got me to create a new value in the following registry location:

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Lsa\
Value Name: DisableLoopbackCheck
Value Type: DWORD
Value: 1

Microsoft Engineer referenced KB887993 as to this fix; this resolved the authentication issue with SQL Server.

But today I had issues with other servers where users not being able to authenticate, in a recent Windows Update, KB957097 (addressing MS08-068 Security Bulletin, link). After reading up KB957097, I found out disabling that setting actually is not advised way to fix this issue; I sent an email to Microsoft Engineer asking his opinion on this …

Ref Links:
SQL Server Protocols: Using Kerberos with SQL Server, Link.

Get listing of Databases user has access to …

March 30, 2009 Leave a comment

You can right click on each user to find out what database he or she has access to; which can be very time consuming. I have wrote the script below that does similar thing for one user or multiple users.

Please note this procedure will take a while to complete depending on the number of databases in your system. You can submit three types of searches:

-- Check Single User [DEFAULT]
EXEC up_CheckUsersAccessInDB 'Test'
GO

-- Check Multiple Users
EXEC up_CheckUsersAccessInDB 'Test1,Test2', 1
GO

-- Check All Users
EXEC up_CheckUsersAccessInDB '%', 2
GO

Stored Procedure: Link.

If any errors or issues please feel free to mail me, mohitkgupta at msn dot com.

Categories: Security, SQL 2008

Status Column in Sysusers

October 3, 2008 Leave a comment

I have encountered issues after adding logins to the SQL Server 2000 databases where even though the servers show up in the user list they still cannot access the database. It turns out I only granted the user permissions to the database and not actually added them; so it failed. In doing that found following states:

State Description

4

User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT Group).

6

User has permissions to the database (objects) and user belong to the database (Windows NT Group).

12

User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT User).

14

User has permissions to the database (objects) and user belong to the database (Windows NT User).

I wouldn’t recommend making any coding on this; but it is a good reference. And I don’t know if these hold true for SQL Server 2005 or 2008 yet.

Categories: Security, SQL 2000

Error 18456, Severity 14: Login Failed States

October 3, 2008 Leave a comment

The document referenced below explains all the states, this is just a summary.

State Description
1
Only state returned in SQL 2000. Seems to be catch-all where no other states apply; for example Account Disabled shows up as State 1(2005).
2
Invalid Userid
5
SQL Account provided does not exist on SQL Server. (SQL2005)
6
Attempt to use a windows login name with SQL Authentication
7
Login disabled and password mismatch
8
Password mismatch
9
Invalid Password (i.e. did not meet minimum password criteria)
10
Read Ref Link #2
11
Windows account does not exist on SQL Server or CONNECT permissions is set to DENY for the login. (SQL2005)
12
Valid login but server access failure; CONNECT permissions is set to DENY for the login.
13
SQL Server service paused
14
????
16
User doesn’t have permission to target (connection string) or default database (SQL Server login setting) (SQL 2005)
18
Change password required
23
Server in process of shutting down, and user attempted to login.
27
Initial database could not be determined for session
38
Initial database could not be determined for session (SQL 2008)
40
Default database could not be accessed (SQL 2008)
58
SQL Server is set to Windows Only Authentication, SQL Login not allowed.(SQL “Denali”)

In SQL 2005 it reports as state 1, user is not associated with a trusted account.

Ref Link #1: States Explained, http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Ref Link #2: KB925744, Issue with State 10, http://support.microsoft.com/?id=925744
Ref Link #3: Troubleshooting: Login Failed for User ‘x’, http://msdn.microsoft.com/en-us/library/ms366351.aspx

Updated: March 6th, added state 38/40 for SQL 2008.
Updated: March 12th, added a bit more detail about state 11/12 and added state 1.
Updated: April 14, 2011, added state 58. Updated state 5, 11, & 12. If I added (Version); it means I have confirmed/found it on that version.
Updated: November 22, 2011, updated the state 8, what is invalid password?

%d bloggers like this: