Sql-server – Error: 18456, Severity: 14, State: 5; SSMS > Network sever from PC

active-directoryconnectivitysql server

I just completed my first install of SQL as a server based, Enterprise Edition. I wanted to check it out using SSMS on PC using my Admin Access. But could not connect.

For security reasons my admin account and my Network ID on my PC are different, so I knew I would not be able to use 'windows authentication', but I tried it as part of my trouble shooting

  1. SQL 2014 install completed
  2. Attempted to access with SSMS (from my PC SQL2012) > Fail
  3. Found log MSSQL12.MSSQLSERVER\MSSQL\Log
  4. Logon match fail NT & Admin accounts
    • 2016-01-05 10:21:54.34 Logon Error: 18456, Severity: 14, State: 5.
    • 2016-01-05 10:21:54.34 Logon Login failed for user 'NT1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]
    • 2016-01-05 10:22:29.85 Logon Error: 18456, Severity: 14, State: 5.
    • 2016-01-05 10:22:29.85 Logon Login failed for user 'Admin1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]
  5. Validated ‘Admin1’ is a member of the 'AD group with access'
  6. Attempted access with SSMS from the server, as the server (windows authentication Server1) > Granted access
  7. Via SSMS on Server, check security of 'AD group with access'; Server1 > Security > Logins it looks like everything should be good, BUT windows authentication is checked, which leads me to suspect that unless ‘Admin1’ is logged on to a computer with SSMS it will not get access.
  8. Tried using ‘Admin2‘, still failed
    • 2016-01-05 11:09:58.89 Logon Error: 18456, Severity: 14, State: 5.
    • 2016-01-05 11:09:58.89 Logon Login failed for user 'Admin2'. Reason: Could not find a login matching the name provided. [CLIENT: IP]
  9. Tried to connect with ‘Server1’ from my PC, still failed
    • 2016-01-05 11:15:53.22 Logon Error: 18456, Severity: 14, State: 5.
    • 2016-01-05 11:15:53.22 Logon Login failed for user 'Server1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]
  10. Google leads me Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1 which is not the issue
  11. Google leads me to https://sqlblog.org/2011/01/14/troubleshooting-error-18456 which tells me that State: 5 = the login does not exist; currently thinking that means we don’t have a connection to AD.

NOTE: IP's and other specifics of account ID redacted from log files, generic place holders used.

The example is with SQL 2014 install but the problem is not version specific.

Best Answer

My team lead, pointed out the issue. As I could not find the issue/solution here, posting it for others.

I knew I could not use windows authentication, so I was trying to use SQL Server Authentication, this was the problem.

To access SSMS as a different user - When opening SSMS from your PC; Shift + right click > ‘Run as Different User’ enter ID ‘Admin1’ + password, proceed as normal.

Related Question