locked
Login Failure Error 18456 Severity 14 State 5 on a SQL Server 2012 Database RRS feed

  • Question

  • Hello Forum


    I have just added an AD Developer Group to a SQL Server Development instance and I've granted the group dbo permissions to all the databases under development; however our developers have tried to connect to SQL Server and it failed and this is the error message

    Date  17/07/2015 15:45:26
     Log  SQL Server (Current - 17/07/2015 15:44:00)

    Source  Logon

    Message
    Login failed for user 'xxx\smithj'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.?.??]

    Note that the group is active in the Active Directory
    Server details: -

    SQL Server Version is 2012 Standard on a Windows 2012 Server Cluster.

    History of checks and attempted fixes: -

    1. Users have been logging off and on their machines and rebooting their machines to no avail.
    2. Ran the query xp_logininfo 'CSU\All Software Engineers','members'; SQL Server is detecting the Group members and the failing members are members of the Group.
    3. I generated a Script to drop and create the Group; ran the script and members still cannot connect.
    4. Carried out a Clustger failover and once again the members cannot connect.

    Has anyone else got any suggestions as to why SQL Server 2012 is denying logins via Group Membership?


    Please click "Mark As Answer" if my post helped. Tony C.

    Friday, July 17, 2015 4:32 PM

All replies

  • What happens if you try to add one of the users NT accounts directly?



    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    Friday, July 17, 2015 4:49 PM
  • Hello,

    Please make sure the SQL Server service account has permissions to query the active directory about an AD Windows account?  Is this a clustered instance? Is the SQL Server service account a Domain account? Try a AD domain account for SQL Server service with enough permissions to query AD.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Friday, July 17, 2015 6:42 PM
  • Thanks for these suggestions.

    If I add the logins individually there are no issues.

    Yes I is a clustered instance (as stated in the thread at the top).

    The Service Account currently in use is the Domain Administrator Account so it does have permission to query AD.


    Please click "Mark As Answer" if my post helped. Tony C.


    Monday, July 20, 2015 9:11 AM
  • Is the SQL Server located in the same domain as the developer group? If not, check if the group is a 'Global' or 'Universal' group.

    'Domain Local' groups cannot be used to connect to SQL Servers in different domains.

    Monday, January 4, 2016 4:28 PM
  • Is the SQL Server located in the same domain as the developer group? If not, check if the group is a 'Global' or 'Universal' group.

    'Domain Local' groups cannot be used to connect to SQL Servers in different domains.

    Hello Ian

    Many thanks for your reply; the AD Group in question is in use on other 2000 and 2005 Servers; it's just not working on the 2012 SQL Server.  I think the issue is that the domain controllers are all 2003 and as such SQL Server 2012 cannot authenticate with AD Groups; with this in mind I've already put in a request to have the DC's replaced with more up to date Servers.


    Please click "Mark As Answer" if my post helped. Tony C.


    Monday, January 4, 2016 4:44 PM