4

My situation: I have installed SQL Express on my machine, while logged in as a domain user. I work remotely so I need to connect to a VPN to be on the domain, but I often prefer not to work while attached to the domain so my packets don't have to travel across the country. My user account has local admin rights (win 7).

My problem is very similar to this one. I receive the same error message when I try to do "admin-y" things, like creating new databases or adding database diagram support to existing databases. The catch is that the error only happens when I am NOT attached to the VPN (and therefore my machine cannot reach the domain controller).

Is there a way to allow SQL Server to trust my login credentials when I'm not connected to the VPN?

4
  • Well, you could login as a SQL authenticated login instead of your domain account, and add that login to the sysadmin role... – Aaron Bertrand Jan 17 '14 at 17:36
  • Could anybody explain why SQL Server needs to connect to the domain when you use a domain account? I can't think of any other administrative task that I can only perform when I'm connected to the VPN. Windows trusts me, why doesn't SQL Server? – roufamatic Jan 17 '14 at 19:28
  • 2
    Well, for starters, it needs to validate that the domain account exists, still has the permissions it had when it was first added to SQL Server, etc. – Aaron Bertrand Jan 17 '14 at 19:29
  • Are you getting access to the SQL Server via a domain group, or does your local account have direct access to the SQL Server? – mrdenny May 26 '14 at 22:26
2

Another solution is to create an SQL Server Log on and give it sysadmin permissions. This avoids using windows entirely for authentication. This is what the SA account on SQL Server is, a special SQL Server log in (which i normally disable log on once installed and another admin user is created) with sysadmin privileges.

This would probably be the best bet as it does not require creation of a local user on your machine and having to run SSMS in a different manner every time, just select Sql Server authentication when logging in and use the new credentials.

0

One possible solution:

Create a local Windows user, create a Windows login in SQL Server for that user, give it the appropriate rights (sysadmin?), then launch Management Studio (or whichever tool you need) via right click, Run As. Specify the credentials for the local user.

You can also launch a program with the "runas /netonly" command to run the program as the currently logged-in user, but authenticate to network services with different credentials. I have no idea how this behaves if the "network" service is on the same machine.

1
  • That doesn't work any more. I don't know if it is a change in SSMS or Windows, but it is most frustrating. – Jonathan Allen Jan 15 '16 at 19:33

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.