One of the testers has reported an error today that I have never encountered before and here is the steps I have taken in order to resolve it. This blog is not aimed to explain what the error was about, but rather to point you in the right direction if you happen to be here looking for a solution.
The very first thing I do when dealing with any error is try to reproduce the error. I was able to consistently reproduce the same error by logging in to SQL Server using Windows authentication with the account domain1\test1.
It was actually reassuring to see the error myself because I always believe that if it can be reproduced, it can be resolved.
2. Login with SQL authentication. WORKED.
I then switched over to SQL authentication and I was able to log in with a SQL user account. Alright! There was nothing wrong with SQL Server. The issue was definitely limited to Windows authentication and/or the user account.
3. Login as me. FAILED.
Another test I did was to log in using my credential, e.g. domain1\dev1. The same error message popped up.
4. Login as another user. WORKED.
There was another tester (domain1\test2) who claimed that she was able to log in using Windows authentication. This was a crucial piece of information as it ruled out issues relating to the authentication mode itself. So then I was pretty sure it had something to do with the domain1\test1 user account.
5. Add user to the server. WORKED.
One thing I should mention is that individual user accounts were not directly created in SQL Server, access was granted through group memberships, e.g. domain1\testers_group.
So another thing I’d tried was to add domain1\test1 into the server as an individual user account and then attempted to login. That actually worked. This was also a very important step as it pointed to AD group membership issues rather than the AD user account itself.
Apart from the steps mentioned above, I have also tried the following avenues but other than ruling out a few more possibilities, the actions didn’t really contribute towards the resolution.
1. Wanted to check the Windows event log but don’t have access to the SQL Server box.
2. Googled and found out about UAC, but the error was seen on a Windows XP box.
3. Checked the registry for UAC just in case but found nothing.
4. Tried xp_logininfo but it revealed no useful info.
In the end, it turned out to be a missing mapping of the AD group memberships for the user across two domains. The resolution was to simply request for the mapping to be put in place for the user.
Your situation may very likely be different from the one I’ve described above, but I hope this blog has provided you with some ideas on the problem solving steps you can utilise to narrow down the cause of the issue.
For more info, please refer to the following blog posts: