Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

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.

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

+3
−0

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]
  1. Validated ‘Admin1’ is a member of the 'AD group with access'
  2. Attempted access with SSMS from the server, as the server (windows authentication Server1) > Granted access
  3. 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.
  4. 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]
  1. 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]
  1. Google leads me https://dba.stackexchange.com/questions/53789 which is not the issue
  2. 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.

History
Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

0 comment threads

1 answer

+3
−0

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.

History
Why does this post require moderator attention?
You might want to add some details to your flag.

0 comment threads

Sign up to answer this question »