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.

0

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.

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.