July 8, 2014 at 1:29 pm
I am running Windows 2012 R2 Server with SQL Server 12.0.2000. I have added the domain admins group into the sysadmins role, but members of domain admins are not able to log onto the server user Windows Authentication. The error is Login failed for user 'Domain\User'. (Microsoft SQL server, Erro: 18456)
July 10, 2014 at 8:15 am
There should be more information in the SQL Server error log; can you have a look there and let us know what it says?
July 10, 2014 at 9:04 am
From the SQL Server error log... Login failed for user 'Domain\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
Initial searches suggest this is an issue with UAC although I have UAC turned off on this server.
July 10, 2014 at 9:13 am
Have you tried explicitly granting 'Connect' to that group? Sorry if this is too basic.
July 10, 2014 at 9:16 am
Also check the profile for any denys that might have sneaked in.
July 11, 2014 at 2:22 am
sherrerk (7/10/2014)
Initial searches suggest this is an issue with UAC although I have UAC turned off on this server.
Are your domain admin users actually connecting via remote desktop to the server and running SSMS on there, then, or are they running it on their own machines and connecting remotely to the SQL instance? If the latter, it doesn't matter in the slightest what the UAC setting on the server is, it's the UAC setting on the admin's own machine that's important.
July 11, 2014 at 9:14 am
Paul, thanks for bringing that up. I am setting up a new server, and yes I was using RDP and using SSMS on the local machine. I tried to connect from my own machine and have no problem which seems to point even more at UAC on the server. I have read that even when turning off UAC from the interface in Windows 2012 R2 server that there are still some settings that need to be changed in the registry. I am in the middle of setting up a new QA environment and have decided to come back to the security problem at a latter time in lieu of getting the rest of the physical environment ready.
Beatrix, I don't follow what you mean by granting "connect." This particular server is in mixed authentication because I am having to support an application that can not use Windows authentication. I have never heard of granting connect and the domain admin group is granted sysadmin which in my experience should bypass all permissions. Deny is a separate issue, but I know there are no explicit denies.
At this point, I no longer feel this is an SQL Server issue, but an issue with the operating system which I am getting tired of dealing with. Internet Explorer is now absolutely useless on Windows server and it is a 15 minute process to get another browser on the machine just so you have access to the internet. I will come back in a few days and figure out what I need to do. If no one posts a solution before I figure it out, I will post a solution once I figure it out.
Thanks for the comments so far, they have been helpful.
July 11, 2014 at 10:00 am
We had a similar issue recently and it was UAC, even though we thought it was turned off. We changed our GP and it works now. See these links
http://clintboessen.blogspot.com/2013/05/you-dont-currently-have-permission-to.html
July 11, 2014 at 3:13 pm
So I assume the entire error is: Error: 18456, Severity: 14, State: 11
One other thing to check in AD is to see if the Domain Administrator group members are in other AD groups as well. When an AD user login occurs and the individual AD login is a member of multiple groups a 'union' of each group's AD permissions are brought together - they all must allow access. So, if one of the AD groups they are a member of the AD groups does not allow access to the SQL Server this type of error will occur. For AD authenticated user logins with multiple group membership it is an all or nothing connection.
Here is a good link: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
One other point - having domain administrator credentials logging in to SQL Server is probably not a good idea.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply