Posted by: Colin Smith
18456, connectivity, SQL Server, Troubleshooting
I know that this is everyone’s favorite error message that SQL Server spits out. I love it because it has so many states that can give you an idea about what the root cause of the issue is. The downside is that some of the states really do not give you any good information and are just generic. So I ran into an issue where a user lost her ability to connect to an instance and it appeared to be out of the blue.
I looked in the log and I found Error 18456 Severity 14 State 16. I went to look up State 16 and lucky me it is one that does not really tell you much. Just says basically that the SQL Server was unable to authenticate the user. This error points out to look at AD and make sure the DC is accessible. Well I know all that is good so I decided to do some of my own testing to figure out what the issue was.
1st I found out that the user was getting access to the database via a group membership. I thought perhaps it is an issue with that group. I added her account explicitly and granted her the access she needed to no avail. Same error. Then I thought I would have someone else in that group test it and I also thought about elevating her access and seeing if that helped. If that helped then it would rule out an issue with her AD account.
Second I did just that, I granted her sysadmin role and it worked like a charm. So I took that access away as soon as I verified that the elevated access worked. Now I thought that it must be something on the SQL Server that is causing the issue.
I put together a script to check all the AD Groups that were logins on the SQL Server and then check to see in AD if she was a member of any of those groups. Turns out she was a member of another group. So I thought that perhaps that group was not granted access to connect or perhaps an explicit deny some where was causing the issue. So I looked deeper at that group login and found that the group had no mappings to any online databases on the instance. I happen to know that the group did have access to one database on the server but that database is offline. Since that group had no access as long as that database was offline, I removed the login group and like magic the user has access again.
Keep an eye out for this as it was a difficult one to track down. It seems buggy to me that membership in a group with a mapping to a database that is offline will keep a user from connecting to a database that they have access to with a valid mapping.
I will post the powershell script in another post. Have to do some cleanup first.