SQL Server – Troubleshooting Network Errors When Connecting from SSMS

sql serverssms

Here some background on the setup. The app server and the db server are on a workgroup no domain. Both are AWS EC2 instances. A partner went and install an evaluation copy of SQL Server 2014. I apply the licence key (SQL Server 2014 Standard) on the instance and run the upgrade wizard since then nothing is working as it should. Before the expired date you could connect to the SQL server from the app server using the SSMS. Now getting an network error 18456.

Firewalls are disabled
Under the SQL Configuration manager TCP/IP have been enable.
Can telnet form the APP server to the SQL server on port 1433 and 1434
Can login with my sql user using SSMS on the SQL server it self

I had a look at these websites as will but still no luck.
https://logicalread.com/errors-sql-server-login-failures-pd01/#.WsNZsIhuZEY
http://sqltechtips.blogspot.co.za/2018/03/sql-server-login-failed-error-18456.html

Sorry I am not an DB admin. Any help will be greatly appreciated
Gerrit

Best Answer

As you said that the SQL Server Error code : 18456 is coming with state: 8.

Mostly the Error code : 18456 with state : 8 coming due to user authentication.

As per MSDN Blog here the key to the message is the 'State' which the server will accurately set to reflect the source of the problem.

For Troubleshooting Error 18456

As SQLBlog of @AaronBertrand here , you can find out every error details with description , based on their state with Error code: 18456.

After Edit

As SQLSERVERLEARNER Blog here the

Error Number: 18456 Severity: 14 State: 1

may have so many reasons.

Reasons for this error:

  1. User does not have access on the server
  2. Provided user name is wrong
  3. Provided Password is Wrong
  4. Using windows login credentials for SQL Server authentication
  5. Login for the user is disabled
  6. Login was successful but access to the server failed
  7. Problems with SQL Service
  8. Password for the user has to be changed
  9. User does not have access to perform the specified acton on the server, Only has limited access
  10. User does not have previlages to access the specific database, agent service etc
  11. Even here MSDN BOL here mention that for State : 1 , Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information.
  12. As here State 1 now occurs when a login is disabled – but actually, the error in the log is 18470, not 18456 – because the login is disabled, it doesn't get that far. See state 7. Prior to SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting. The login is disabled and the password is incorrect. This shows that password validation occurs first, since if the password is correct and the login is disabled, you get error 18470 (see state 1 above). It's possible that your application is sending cached credentials and the password has been changed or reset in the meantime – you may try logging out and logging back in to refresh these credentials.

For further your ref here and here

Related Question