1

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:

General Tab

Status Tab

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:

enter image description here

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?

  • Did you add a group into Builtin\administrators, or all accounts individually? – Stijn Wynants Nov 24 '16 at 13:12
  • @StijnWynants It's an AD group thats in the Administrators group on the computer. This has worked on other machines fine. Although I did attempt to add the user manually, and it didn't seem to make any difference. – Obsidian Phoenix Nov 24 '16 at 13:13
  • What does your error log on SQL Server say? – Stijn Wynants Nov 24 '16 at 13:17
  • Ah. Missed those logs. Apparently "login lacks connect sql permission" - but the group does appear to have it in the screenshot above – Obsidian Phoenix Nov 24 '16 at 13:30
  • can you add a screen of the errorlog message? – Stijn Wynants Nov 24 '16 at 13:38
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.