0

I changed sa and Windows administrators password successfully, I can login to server with sa without any problem, services working normally, database is not corrupted and working without any problem.

Problem is I cannot login with Windows authentication after changing sa password, mixed login mode is enabled, even I can create new user and I can login with it but I can not login with Windows authentication, the error I get is always:

Error Number: 18456 Severity: 14 State: 1 Line Number: 65536

Another thing is I can connect to Analysis services with Windows authentication, but Database Engine does not accept Windows authentication.

info:

Select @@Version:

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

enter image description here

enter image description here

SSMS is checked to run as Administrator always, but does not help.

Improve this question
5
  • When you login the server using the sa user can you see that xxx-DC\administrator login listed under the Security folder on SSMS?
    – Ronaldo
    Apr 11, 2022 at 11:32
  • @Ronaldo I do not have access to database now (out of office), i will inform you as soon as i check security folder (sorry for now). But when i choose Windows Authentication xxx-DC\Administrator fills automatically, does it have to be there?
    – Yuzarsif
    Apr 11, 2022 at 12:33
  • Yes, when you choose Windows Authentication on SSMS it automatically fills the login with the user currently logged in Windows. After changing the Administrator password have you tried to log off and login again before connecting to SQL Server?
    – Ronaldo
    Apr 11, 2022 at 12:45
  • Dear @Ronaldo, i checked the security folder and as you say it was not present there so i added with Windows Authentication, and problem solved, but i was able to login before long time ago, i think this did not happen after i change password, it is some thing else. I asked this question in many forms and Discord channels, no body could answer this. Some experts (they think they are) suggested hire an expert for this. Thank you very much, please Add your suggestion as Answer and i will accept it. Thank you.
    – Yuzarsif
    Apr 11, 2022 at 15:47
  • Glad to help, Yuzarsif. I added the answer.
    – Ronaldo
    Apr 11, 2022 at 16:05

1 Answer 1

Reset to default
1

The error log you provided says:

Could not find a login matching the name provided.

So, my guess is that at some point that Administrator login was removed from SQL Server.

To check that, please, connect to your SQL Server instance using the sa login and verify if the xxx-DC\Administrator is listed under the folder Security > Logins. If it's not there, add the login and try using the Windows Authentication.

Improve this answer
4
  • Probably want to add it as a group xxx-DC\Administrators
    – Charlieface
    Apr 11, 2022 at 16:04
  • eg create login [xxx-DC\Administrator] from windows and alter server role sysadmin add member [xxx-DC\Administrator]
    – David Browne - Microsoft
    Apr 11, 2022 at 17:47
  • @Ronaldo thank you.
    – Yuzarsif
    Apr 11, 2022 at 19:12
  • i want to thank you too for trying to help Charlieface, DavidBrowne-Microsoft and J.D. (Not allowed to mention more than one).
    – Yuzarsif
    Apr 11, 2022 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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