2

I have created a Group in SQL Server and add an Active Directory group to it with which contains users.

When I try to login through Windows authentication, it gives me the following error:

Login failed for user 'UserName'. (Microsoft SQL Server, Error: 18456)

But when I add just an AD user separate from the group, it has no problem logging in.

Why doesn't the login work with only the group added as a login?

I have tried changing the Active Directory settings from global to universal type but that didn't change anything

enter image description here

This on Windows 2016 server machine and SQL Server 2017.

  • Is the name of your Windows Authenticated SQL Server Group in the form <domain>\<group> (e.g. CONTOSO\SQL_Server_Group)? – hot2use Aug 10 '18 at 11:48
  • Yup,in Security->Logins it says MyDomainName\GroupName – Lucy Aug 10 '18 at 11:52
  • 1
    Did the user log off his/her session after being added to the group? The Kerberos Tokens aren't refreshed automatically, so the user could be missing his/her group membership until he/she logs off and back on. – hot2use Aug 10 '18 at 11:54
  • I logged out of the server now ,but I can't get back in ,it says I'm not authorized anymore ,could that be to do with Active Directory? – Lucy Aug 10 '18 at 11:59
  • 1
    Adding a Windows Authenticated SQL Server Group to the SQL Server instance should not have anything to do with you no longer being able to log in to a Windows Server remotely. I'm guessing that your company has some form of Active Directory Tool that automatically synchronises Groups and Accounts between the tool and AD, which has resulted in the Windows Group being removed from AD (just guessing). Are you responsible for your AD or is this a different group/person? I would go and have a chat with them and then verify that your account and or the new Windows Group has not been locked. – hot2use Aug 10 '18 at 12:05
5

Normally when you receive an error message with the Error: 18456, ... it will contain additional information somewhere.

Detailed SSMS Login Error

In the case of the SSMS Login, the error message will be displayed together with a couple of icons. One of them is page with a white cross on red background. If you click on that icon you will be presented with the detailed error message, which could look a bit like this:

SQL Server Management Studio - Login Error Message Detailed

The detailed error message will contain additional information:

Server Name: .
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Detailed Login Error in ERRORLOG

Additionally you can find the full error message in the ERRORLOG of your SQL Server instance. The entry will look similar to the following:

2018-08-10 15:37:56.10 Logon       Error: 18456, Severity: 14, State: 5.
2018-08-10 15:37:56.10 Logon       Login failed for user 'bigger'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Decoding The State Number

With the state number you can then go and check Aaron Bertrand's Blog's article:

Further Analysis

If you can edit additional information into your question, then we might be able to find the root cause together.

1

After asking and Trying out different options , the issue was really simple ,I just had to reboot the server ,when the server was back up I tried to login with the account that is in the AD group ,it logged in successfully .(I feel a bit embarrassed)

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.