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... Commented Jan 17, 2014 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
    Commented Jan 17, 2014 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. Commented Jan 17, 2014 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
    Commented May 26, 2014 at 22:26

2 Answers 2

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. Commented Jan 15, 2016 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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