Sql-server – Active Directory not working through SQL server Management studio

active-directorysql serversql-server-2017ssms

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.

Best Answer

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.

Related Question