Error: 18456, Severity: 14, State: 5; SSMS > Network sever from PC

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 troubleshooting

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 https://dba.stackexchange.com/questions/53789 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.

posted over 4 years ago

CC BY-SA 4.0

4y ago by Alexei‭

James Jenkins‭

210 reputation 4 3 28 4