locked
AD users can't log to SQL RRS feed

  • Question

  • Hi,

    i have AD group that is linked to SQL 2012 server and that group has sysadmin role. I am member of that group and i can connect to SQL server. When i want to connect with other AD user to SQL that is also part of that AD group, SQL gives me error DOMAIN\username is not recognized. Kerberos is enabled. 

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

    If i directly add that user to SQL server then i can connect.

    WTF? :D

    Tuesday, December 16, 2014 1:08 PM

Answers

  • Hi opti2k4,

    According to your description, you tried to assign an entire AD group access to SQL Server 2012, and you failed to use other AD user of the AD group except the user account of yours to access SQL Server, but when you directly add that user to SQL server then the user can connect to SQL Server, right?

    The error “Could not find a login matching the name provided” means “The login used for the connection did not exist on the SQL Server”.
    And you mentioned that the user account of yours was able to connect to SQL Server while other AD user wasn’t. I think may be the user account of yours is a local administrator, or in another group, which has permission to connect to SQL Server.

    So based on the error message and my understanding, I assume that it may be caused by incorrectly adding AD group as login in SQL Server.
    In order to verify that, we can use the queries that Ashwin has mentioned above to get the access information of the AD group in SQL Server. For more information about xp_logininfo (Transact-SQL), please refer to the following article: http://msdn.microsoft.com/en-us/library/ms190369.aspx

    And we can follow steps below to Grant Active Directory Group Access to SQL Server:
    Step 1: Create a server login for the AD group:

    USE master
    CREATE LOGIN [YOURDOMAIN\SomeGroupName] FROM WINDOWS;

    Step 2: Go to the database and create a user based on that login:

    USE YourDatabaseName
    CREATE USER [YOURDOMAIN\SomeGroupName] FROM LOGIN [YOURDOMAIN\SomeGroupName];

    If you have any question, please feel free to let me know.

    Regards,
    Jerry Li

    • Marked as answer by Donghui Li Wednesday, December 24, 2014 1:19 AM
    Thursday, December 18, 2014 1:33 AM
  • If there is no issue in AD 

    Check if this is orphan user account

    exec sp_change_users_login Report  --> It will show the orphan user

    if found  

    exec sp_change_users_login @Action = 'Update_One',
                                     @UserNamePattern = 'DOMAIN\user',
                                     @LoginName = 'DOMAIN\user'

    • Marked as answer by Donghui Li Wednesday, December 24, 2014 1:19 AM
    Thursday, December 18, 2014 1:44 AM

All replies

  • Maybe this help you
    Tuesday, December 16, 2014 1:35 PM
  • What would you get if you run these queries

    xp_logininfo 'Domain\Group Name' , 'members'
    

    also the below query with both the individual logins

    xp_logininfo 'Domain\Account Name' , 'All'


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, December 16, 2014 2:03 PM
  • Hi opti2k4,

    According to your description, you tried to assign an entire AD group access to SQL Server 2012, and you failed to use other AD user of the AD group except the user account of yours to access SQL Server, but when you directly add that user to SQL server then the user can connect to SQL Server, right?

    The error “Could not find a login matching the name provided” means “The login used for the connection did not exist on the SQL Server”.
    And you mentioned that the user account of yours was able to connect to SQL Server while other AD user wasn’t. I think may be the user account of yours is a local administrator, or in another group, which has permission to connect to SQL Server.

    So based on the error message and my understanding, I assume that it may be caused by incorrectly adding AD group as login in SQL Server.
    In order to verify that, we can use the queries that Ashwin has mentioned above to get the access information of the AD group in SQL Server. For more information about xp_logininfo (Transact-SQL), please refer to the following article: http://msdn.microsoft.com/en-us/library/ms190369.aspx

    And we can follow steps below to Grant Active Directory Group Access to SQL Server:
    Step 1: Create a server login for the AD group:

    USE master
    CREATE LOGIN [YOURDOMAIN\SomeGroupName] FROM WINDOWS;

    Step 2: Go to the database and create a user based on that login:

    USE YourDatabaseName
    CREATE USER [YOURDOMAIN\SomeGroupName] FROM LOGIN [YOURDOMAIN\SomeGroupName];

    If you have any question, please feel free to let me know.

    Regards,
    Jerry Li

    • Marked as answer by Donghui Li Wednesday, December 24, 2014 1:19 AM
    Thursday, December 18, 2014 1:33 AM
  • Hi,

    How are you trying to connect to SQL Server as a different user - Run SQL Server as different user or Logging of and logging back in using Other AD user Credentials?

    If the user is added to the AD group that has access to SQL Server, He has to log off and log back on to windows for changes to apply.

    Hope this helps

    Thanks

    Bhanu 

    Thursday, December 18, 2014 1:42 AM
  • If there is no issue in AD 

    Check if this is orphan user account

    exec sp_change_users_login Report  --> It will show the orphan user

    if found  

    exec sp_change_users_login @Action = 'Update_One',
                                     @UserNamePattern = 'DOMAIN\user',
                                     @LoginName = 'DOMAIN\user'

    • Marked as answer by Donghui Li Wednesday, December 24, 2014 1:19 AM
    Thursday, December 18, 2014 1:44 AM