Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
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 adm...
#2: Post edited
by
Alexei
·
2020-10-20T17:12:18Z (over 3 years ago)
added relevant tags
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 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.
- 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.
#1: Initial revision
by
James Jenkins
·
2020-08-21T17:53:00Z (over 3 years ago)
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 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 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.