Accessing a Remote SQL Server instance via SQL Server Management Studio


When you work with remote SQL Server instances, such as those on a standalone machine, or one on a separate domain, and you only have Windows Authentication to access the remote machine, how would you do it?

  • If the target instance had SQL Server Authentication, you need only supply the user name and password of SQL login, and you are in.
  • If the target is on the same domain as the user you have logged on to your local machine with, then it’s just a matter of the login having access on the server, and you could just Windows Authentication it.

However, in this case, you are on your own domain/standalone machine trying to access another domain/standalone machine remotely. Your immediate impulse would be to run SQL Server Management Studio as another user, and supply the remote machine’s credentials. Problem is, your machine/domain is not going to know the domain’s/remote machine’s credentials:

Connect Error to Remote Intance

Hence, in order for you to open Management Studio using the target machine’s credentials, you need to do things a little differently: Open the command prompt as an administrator, and run Management Studio under the credentials of the target machine:

RUNAS with NETONLY flag

This will open Management Studio under the supplied remote machine’s credentials. Note that we use the RUNAS command with the NETONLY flag. Warning: you need to ensure that the password is entered correctly, since Management Studio will open regardless of you supplying the correct password.

Logging in to Management Studio will look like this:

Login to Remote Instance

and once logged in, the instance connected to, would look like this:

Management Studio Object Browser

You are now connected to a remote SQL Server instance from your local machine, with credentials of the remote instance.

Advertisements

One thought on “Accessing a Remote SQL Server instance via SQL Server Management Studio

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s