SQL Server 2014 – Troubleshooting AD Group Login Issues

active-directoryloginsSecuritysql serversql server 2014

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.

Best Answer

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.

Related Question