4

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.
Improve this question
8
  • was the windows account renamed / changed after being added to sql server ? Check if the SID matches.
    – Kin Shah
    Commented Feb 9, 2017 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
    Commented Feb 9, 2017 at 14:45
  • @Kin, can you suggest how I would go about matching the SIDs? (no changes were made to the account, though)
    – Shoeless
    Commented Feb 9, 2017 at 14:56
  • @Garteh, this is a Windows login- no username or password is supplied.
    – Shoeless
    Commented Feb 9, 2017 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
    Commented Feb 27, 2017 at 17:19

6 Answers 6

Reset to default
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.

Improve this answer
6

This is an old thread, but it was the first hit on Google when I encountered the same issue.

My two cents: In my case it turned out that the AD group was a Distribution group, instead of a Security group, hence it could not be used to grant access to SQL Server.

Improve this answer
1
  • old thread, but you just solved my problem buddy. Thanks!
    – Edward Dortland
    Commented Jul 17, 2020 at 9:34
5

TLDR; Make sure the user re-authenticates on the local windows machine that the client is establishing the connection from.

From my experience, It seems SQL doesn't go check group membership when you connect to SQL but relies on the connecting client providing the security group information they belong to. If the local client authenticated - logged into Windows at a time before they were in the AD security group the new group membership will be missing and the login will be denied. If however the user was not logged in on their local client when added to AD and had to login after they were added to the AD group it will work just fine.

Improve this answer
2
  • 1
    This did it for me. Thank you.
    – GHauan
    Commented Sep 1, 2022 at 10:59
  • I can't believe this was my problem too. It boggles the mind that the client needs to be restarted for the server to acknowledge their new roles. And if it has to be this way, it boggles the mind that the client doesn't automatically refresh it's cache of user roles after a failed connection attempt.
    – Alain
    Commented Apr 25 at 20:00
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.

Improve this answer
0

Similar to the answer by Aaron https://dba.stackexchange.com/a/269060/43768 about the AD group being a distribution group causing this error, if your database server is a member of domain example1 and the group you provide access to is a member of domain example2 and the group is a Domain local group, then you'll get this same error.

Error: 18456, Severity: 14, State: 5.

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

If your group and database server must be in different domains, one solution is on your database in domain example1, provide access to a group in domain example2 that is a Global or Universal group.

Improve this answer
-1

I'm so late in posting. This is probably a group type permission issue. In order to get SQL Server group Windows Authentication for AD working, you will need to do the following steps:

  • Login as either Administrator or anyone (or yourself) who is a Domain Admin in the domain controller.
  • Open Active Directory Users and Computers and click Users.
  • Create a new group and give it a name (right-click and click New ... > Group)
  • Make sure that the Group is set to Universal and leave everything else at default.
  • Click the newly created group that you just created
  • Add some AD users in the newly created group in the Members section.
  • Click OK.

In SQL Server Management Studio, you need to create login for the group in order to get access to the server:

  • Login as Administrator.
  • Expand Security folder
  • Right-click Logins and click New Login.
  • In the login name field, click Search.
  • By default, groups will not show up in the list. Click Object Types, check the box Groups, and click OK.
  • Enter the newly created group name and click Check Names. (if it doesn't detect it, then click Advanced and click Find Now)
  • Click OK if the group matches
  • If you need to set permissions for specific database, you can do so now.
  • Click OK to create the group login.

This would allow the AD group to login properly. I have tested this against a fresh SQL Server 2017 instance with AD and worked fine for me.

Improve this answer

Your Answer

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

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