This is officially my first blog post. I was planning to write a blog for the last 2 years but couldn’t start until now, so here we go.

Last Friday, we ran into a weird login failed error message for a login who is a member of an AD group with sysadmin rights on a new installed SQL Server. I tried conventional methods to find root cause and fix it but I have never experienced this case before. So, I decided to write a blog post about it as my first post!

One of my colleague reported that he can not login into a newly installed Always On Secondary SQL server(let’s call it SQL2) from another database host(let’s call it Host1). I checked error log immediately and saw this errors:

This is a typical login failure which a dba sees a lot, however the problem is he is a member of an AD group(let’s call it [AD\Group1]) and [AD\Group1] exists on SQL2 with sysadmin privileges! Furthermore, I am also a member of [AD\Group1] and I can connect to SQL2 as other members. Also, [AD\Group1] is exists on SQL1, primary replica of this Availability Group, and he connected that server without any problem. Things were getting interesting.

First, I created his domain account directly, he was able to connect both SQL1 and SQL2. This way, we eliminated the possibility that his account is not able to login. But why he couldn’t connect through an privileged AD group? There was something going on with AD group and his membership. I used xp_logininfo procedure to retrieve information about [AD\Group1] members and I saw him as a member, so why the hell SQL does not allow him to login and says there is no such login?

I patched both servers with latest SP and CU, didn’t help.

We removed his account from AD group and added him again, didn’t help.

We removed [AD\Group1]  from SQL2 and created again with sysadmin role, didn’t help.

Even though I am embarrassed to tell this, I restarted SQL service, of course didn’t help.

I further searched this issue on internet, also didn’t help.

Then, I installed SSMS on SQL2 host and asked him to try to connect directly from database host, this time it worked. Now, I am even more confused.

So, We have an AD group with sysadmin role which contains 2 members in it. One of them can connect to both SQL1 and SQL2 from anywhere(Host1, local workstation, SQL2 host and so on) without problem, other member can connect to SQL1 without a problem. He can also connect to SQL2 as a member of [AD\Group1] from his workstation and SQL2 host. However, he can’t connect to SQL2 from Host1.

I realized that this needs more careful and detailed examination. I went back to basics and read about AD groups, kerberos authentication and SQL Server authentication-authorization. Then, my colleague told me that he removed himself from [AD\Group1]  one week ago and re-added 1 day before this incident occurred. That moment I realized that his kerberos ticket was different on AD than the one cached in his user profile on Host1 server. I asked him to log off from Host1 and log on again, only then he could connect to SQL2 from Host1.

To sum up, If you encounter a similar login failure make sure that domain user is logged off and logged in again!