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.

Improve this question
10
  • Is the name of your Windows Authenticated SQL Server Group in the form <domain>\<group> (e.g. CONTOSO\SQL_Server_Group)?
    – John K. N.
    Aug 10, 2018 at 11:48
  • Yup,in Security->Logins it says MyDomainName\GroupName
    – Lucy
    Aug 10, 2018 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.
    – John K. N.
    Aug 10, 2018 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, 2018 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.
    – John K. N.
    Aug 10, 2018 at 12:05

2 Answers 2

Reset to default
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.

Improve this answer
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)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.