-1

i am using MSSQL server 2016 i can login to sa account but when i try to login using windows authentication, i got this error

Login failed for user 'domain\username'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

but my windows administrator login id exist on sql server. can anyone help me with this problem? this is what i got UPDATE I checked errorlog using sp_readerrorlog and i got Error: 18456 state:5 but when i connect to sa account and checked Security -> Login, i have correct windows authentication user same as my domain name (Administrator) also i don't use AD DC i am using in WORKGROUP just for local database

this is my windows name WIN-2NEKEGJF11Q\Administrator

closed as off-topic by Erik Darling, hot2use, LowlyDBA, RDFozz, McNets Dec 27 '17 at 17:19

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, LowlyDBA, RDFozz, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • @Nyxx,If you are trying to connect using Windows Authentication, verify that you are properly logged into the correct domain. – Md Haidar Ali Khan Dec 27 '17 at 11:32
  • As @MdHaidarAliKhan says, you need to make sure you're on the correct domain. Also, you need to ensure that you're logged into the MACHINE you're running SSMS on as that Windows AD user before you try to log in as the AD user itself. – MguerraTorres Dec 27 '17 at 18:32
1

the error mesage is not very useful due to security reasons. You have to dig a little bit further into to the SQL Server error logs.

  1. You have to look into SQl Server error logs as @sepupic segested.

EXEC master.dbo.xp_readerrorlog 0, 1, 'Logon', NULL

  1. Find a string similar to this 2017-12-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8. 2017-12-27 00:02:00.34 Logon Login failed for user ''. [CLIENT: ]

The code after State: is important.

With this mapping table you get the real reason for your problem.

ERROR STATE

ERROR DESCRIPTION

  • 2 and 5 - Invalid userid
  • 6 - Attempt to use a Windows login name with SQL Authentication
  • 7 - Login disabled and password mismatch
  • 8 - Password mismatch
  • 9 - Invalid password
  • 11 and 12 - Valid login but server access failure
  • 13- SQL Server service paused
  • 18 - Change password required
  • i checked errorlog and i got State 5 Error – Nyxx Dec 28 '17 at 0:07
  • but when i connect to sa account and checked login in security node, i have correct windows authentication id same as my domain name (Administrator) also i dont use AD DC i use sql just for local database – Nyxx Dec 28 '17 at 0:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.