How to use Windows authentication with SSMS without using RunAs?


I have SQL Server 2012 installed as a standalone instance in Windows 8 Enterprise edition. It is running in Mixed-mode. The currently logged in account is a member of local MACHINE\Administrators and the local BUILTIN\Administrators group is in the sysadmin role. For good measure, the current user is also in a MACHINE\SQLAdmins group which is also in the sysadmin role.

When I launch SQL Server Management Studio (SSMS) from the shortcut, I can't use Windows Authentication. I get the following error:

TITLE: Connect to Server
Cannot connect to INSTANCENAME.
Login failed for user 'MACHINE\user'. (Microsoft SQL Server, Error: 18456)
For help, click:

In fact, if I launch LinqPad from a shortcut, I can't connect to the server using a connection string which relies on Windows Authentication.

However, if I run SSMS or LinqPad with elevated privileges using RunAs Administrator, I can use Windows Authentication and the experience is as expected.

Alternatively, I can include the RunAs command into the SSMS shortcut like so.

C:\Windows\System32\runas.exe /USER:MACHINE\user "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Surprisingly, double-clicking the shortcut will prompt for me to enter the password of the current user. However, once that re-authentication occurs, SSMS will allow Windows Authentication of the current user.

The question is, what user is SSMS running under when I double click the shortcut and ultimately, how can I just run LinqPad or SSMS or any other app that connects to the database with Windows Authentication without first elevating my privileges?

Are you by any chance supplying the credentials of the user who installed SQL Server when you use Run as?

See the second answer on this SO question:

In previous versions of SQL the BUILTIN\Administrators group was given the sysadmin role. Local administrators are not given login privileges by default in SQL 2008. Only the user performing the install is defaulted sa privileges. During install you have the option to add additional users, but you apparently did not.

You will need to login as sa and configure whatever windows logins are required (such as the local administrator account).

I had the same thing happen, where my user account was a local admin, and yet unable to login until the guy who installed SQL Server added my windows account to the SQL Server logins manually.