AD User disabled and re-enabled, Now SQL login fails (Could not find a login matching the name provided)

Jay Jurado 96 Reputation points
2021-11-10T21:07:13.04+00:00

We disabled a user, then re-enabled them, and they use Windows auth to login to our SQL server (100+ other users using win auth no problem). I've deleted the user account in SQL overall, removed it from each database, checked that it no longer shows up anywhere, then re-added it, but at every logon attempt of this one user from any PC using SSMS, they receive the following error. I've redacted the name and IP for privacy.

*Login failed for user 'DOMAIN\REDACTED'. (.Net SqlClient Data Provider)

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

Server Name: REDACTED
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536*

This is the error that shows when I query the login failed table in the SQL database:

Login failed for user 'REDACTED'. Reason: Could not find a login matching the name provided. [CLIENT: REDACTED]

I confirmed the user account is being created by running [select * from master.sys.server_principals] and confirming the account shows up in the table output. I'm definitely creating it as a Windows auth account, not local sql account.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,423 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jay Jurado 96 Reputation points
    2021-11-11T15:05:31.21+00:00

    All, I found the fix - https://serverfault.com/questions/767094/change-sql-server-user-sid

    Once I updated the registry key on my SQL server to basically tell it not to cache local SIDs but reach out to AD every time, I was able to drop the user, add them back, and it then allowed them to log in. Thank you all for your help!


3 additional answers

Sort by: Most helpful
  1. Guoxiong 8,206 Reputation points
    2021-11-10T21:58:13.983+00:00

    Make sure the permission of the login to connect to database engine is not denied:

    148361-image.png


  2. Erland Sommarskog 116.4K Reputation points MVP
    2021-11-10T22:30:09.597+00:00

    I would double-check with the AD that you have the correct SID. (Although a SID mismatch usually results in a different error. As does the permission error that Guoxiong suggested.)

    Next, I would double and triple-check that I have the domain and login name correct.


  3. AmeliaGu-MSFT 13,991 Reputation points Microsoft Vendor
    2021-11-11T06:21:20.2+00:00

    Hi JayJurado-6949,
    Could you please try to remove the user from each database and re-add it to database again?
    Dropping and recreating a domain user account with the same account name could cause the unexpected issues such as mismatched SIDs in SQL Server. Please refer to this article to check if domain level SID is mapped to SQL Server SID correctly.

    In addition, is your database involved in HA such as always on?

    Best Regards,
    Amelia


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.