Builtin\Administrators doesn't permit access

Question

I have created a SQL 2016 (Developer) instance on a new build machine. For ease of use, when we do this, we grant Builtin\Administrators sysadmin rights on the instance. This allows all developers on the machine to be able to have full control over their local instance.

In previous versions of SQL (e.g. 2014). This works fine. However, when I try it on SQL 2016, this does not appear to work.

Whenever I attempt to log into the machine with Windows Authentication, I receive the following error:

Login failed for user 'xxxx'. (.Net SqlClient Data Provider)

---

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

---

Server Name: xxxx

Error Number: 18456

Severity: 14

State: 1

Line Number: 65536

Below are shots of the Builtin\Administrators group within SQL:

As you can see, it's set up and enabled.

The server is configured for Mixed Mode authentication. If I add a Login for the user directly, Windows Authentication will work successfully.

Below is the error log from SQL, which includes a bit more information:

For clarity, the user is in a group which is a member of the Administrators group on the machine. Running whoami /groups on the commandline confirms that the local Administrators group is listed.

What do I need to do to get Builtin\Administrators to allow successful logins?

Answer 1

As commented, UAC is blocking your connection to SQL Server. Run it as administrator and it should be fine.

Answer 2

Ensure you have added a Windows Firewall rule for port 1433. Without the firewall rule it only connects if you run Management Studio as an Administrator.