3

I have created a login for an AD group:

CREATE LOGIN [MYDOMAIN\Development Admins] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

One of the members of that AD group is a user named DBGuy. I can see the DBGuy user in this AD group if I execute

xp_logininfo 'MYDOMAIN\Development Admins', 'members'

But if I try to login using the DBGuy account, I get an error:

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

And in the error logs I see:

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

Some other info....

  • Other AD accounts seem to work fine as Windows logins, I am only having an issue with this group.
  • I found an article from Aaron Bertrand describing a similar issue regarding the default database in the connection- I have set the default database to master in the connection settings.
  • One blog post suggested using exec sp_change_users_login Report to look for orphaned users; this returns zero rows for me.
  • was the windows account renamed / changed after being added to sql server ? Check if the SID matches. – Kin Shah Feb 9 '17 at 14:38
  • How are you trying to login? You can't use a username/password combination for Windows/AD accounts, SQL Server will treat it as a sql login. – Gareth Lyons Feb 9 '17 at 14:45
  • @Kin, can you suggest how I would go about matching the SIDs? (no changes were made to the account, though) – Shoeless Feb 9 '17 at 14:56
  • @Garteh, this is a Windows login- no username or password is supplied. – Shoeless Feb 9 '17 at 14:57
  • 1
    Clearly something is "hosed" with the original AD group. I have created a second group which is essentially the same definition as the first, and it is working correctly. I will delete the original AD group. – Shoeless Feb 27 '17 at 17:19
2

I noticed similar issue and I was locked out of SQL Server after install. So I started SQL Server 2014, in single user mode with -m option and logged into server SQL Management studio. I verified that AD group is correctly configured but didn't do any modifications. I logged out of the SQL server and tried again with as a member of AD group and this time I was able to connect.

This is the second time this happened after a fresh install. At first I thought it was my fault but now I am convinced there is something in the SQL server that is not pulling AD info at first.

3

Clearly something was "hosed" with the original AD group. I created a second group which is essentially the same definition as the first, and verified the new group was working correctly. I then deleted the original AD group.

I have not experienced the issue again. If I do, I will certainly try @AshburnRK's solution to see if that works before recreating the AD group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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