Friday, November 13, 2015

Unable to connect to SQL server instance with SQL Server Management Studio with administrator account even though domain admins group is assigned with sysadmin permissions

Problem

You’re logged directly into a SQL Server 2012 server with an account that belongs to the domain admins group, launch SQL Server Management Studio and attempt to use Windows Authentication to connect to a database instance but notice that you receive the following error:

image

Cannot connect to <SQLserverName>\<DatabaseInstance>.

Additional information:

Login failed for user ‘<domainName>\<userName>’. (Microsoft SQL Server, Error: 18456)

image

You’ve confirmed that the Domain Admins group is assigned to the Security > Logins folder and assigned sysadmin permissions:

image

You noticed that you do not have this problem if you directly add the account you’re logged in as to the logins folder.

Solution

One of the reasons why this issue occurs is if the SQL server exhibiting this behavior has UAC turned on.  If this is the case, the problem will go away if SQL Server Management Studio as ran as an administrator.

No comments: