SQL SERVER – FIX Error 18456, Severity: 14, State: 6. Login failed for user

One of the blog which I wrote earlier was also on Login failed for user with state 58. Let us see how to FIX Error 18456.

After reading that blog, one of the blog reader contacted me and told that she is getting state 6. I thought this error are very common and is important to demystify some of the reasons for the same. I have asked completed error message to assist him. Here is the message she shared:

Error: 18456, Severity: 14, State: 6.

Login failed for user ‘sqlserver2016\sysadmin’. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: <local machine>] If you are new to SQL Server, then use below to find ERRORLOG

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here was the connection string from the application:

“Provider=SQLNCLI11.1;Data Source=SQLSERVER2016;Initial Catalog=myDB;User ID=sqlserver2016\sysadmin;Password=myPassword@123”

You can reproduce the same error, but using SSMS also. If you put the domain account while using “SQL Authentication”.

SQL Server allows only SQL logins in the SQL Authentication mode. The logins are defined within SQL Server.

In short, if you want to use Window account to connect to SQL Server, connection string should not have user name and password. It would be something like below. Instead of username and password, we need to use “Integrated Security=SSPI”

Provider=SQLNCLI11.1;Data Source=SQLSERVER2016;Integrated Security=SSPI

I know this might not be a big deal, but hope this blog would help someone in future. Recording such errors is important and do let me know if you ever got this error in your environments.

Reference: Pinal Dave (https://blog.sqlauthority.com)