2

How can I connect to a remote instance of SQL Server (on my VPS) from my local machine using Windows Authentication?

By default, if I try to connect using this method, the username is my computer name but this user isn't associated with the remote instance of SQL Server.

Do I have to set up my computer as a user on my VPS somehow? If this is possible, please could someone provide very simple instructions as to how I could do this as I am very much a beginner!

All help is very much appreciated.

Leah
  • 151
  • 1
  • 3

3 Answers3

2

If I understand you correctly you want to be able to run your SQL management tools using credentials from your VPS rather than your local machine. You can use the runas command using the /netonly switch. This authenticates you remotely rather than locally, allowing you to run programs using credentials your local computer can't authenticate (e.g. those on a remote computer or domain). E.g.:

runas /user:remotedomain\username /netonly /myprog.exe

If that's not what you're trying to achieve please clarify your question because it is a little ambiguous. Hope that helps.

Matt
  • 1,883
  • 5
  • 26
  • 39
  • Thanks for your help. Apologies if my question is not very clear. What I am trying to do is to connect to the remote SQL Server instance on my VPS from my local computer, using SQL Server Management Studio. I was just wondering if I would be able to connect from my local machine using Windows Authentication somehow. Could you please elaborate a little on how to use the 'runas' command that you have mentioned? I'm not familiar with that. – Leah Aug 08 '11 at 21:22
  • Yeah so it sounds like this should do the trick. You can either just enter the command above in the Run dialogue (open this by holding the Windows key and pressing R) or from the command line. Replace remotedomain\username with the domain and username on the VPS that has the privileges you need (e.g. the account you would use if you RDP onto the server and run from there) - if it's not part of a domain replace remotedomain with the server hostname. And replace myprog.exe with the path to your SQL management studio executable on your local machine. – Matt Aug 09 '11 at 21:26
  • Thanks ever so much for your help. Could I just ask one more question - how would I find the path to my SQL Management Studio executable on my local machine? Once I know how to do that I will try following your instructions. Thanks! – Leah Aug 10 '11 at 10:20
  • Right click the shortcut you normally double-click to run it, and choose Properties from the context menu. This will show you the path to the executable. To make it easier once you know the path and you've got it working you can just save the whole thing in a batch file (type it into notepad and save with .bat extension instead of .txt), then you can just run the batch file every time you need to run it rather than typing the whole command every time. – Matt Aug 10 '11 at 20:01
1

To achieve what you want your local user must be member of the domain of your VPS. I currently have no MS-SQL installation at hand, but there should be a user-group at your VPS named something like "SQL-Server-Remote-Access". So your local user must be a member of that group.

So go to your local user management an add him to the group of the remote server. This implies that you have the Domain-Admin password at hand to be allowed to do that.

mailq
  • 16,882
  • 2
  • 36
  • 66
  • Thanks for your help. Could you please explain how to "go to your local user management"? I'm not sure where this is and how to get to it. And do you mean it's something I need to do on my local computer as opposed to changing something on my VPS? Thanks. – Leah Aug 09 '11 at 09:24
  • Yes locally. As you didn't tell which OS you have, run `lusrmgr.msc` from Run-Dialog (WIN+R). But isn't that strange to setup user authentication when you don't know about user management? – mailq Aug 09 '11 at 09:37
  • I have Windows 7 on my local machine. Yes as I put in my question, I am a beginner and I am looking for a lot of help really. Thanks for bearing with me. I've managed to get to the Local Users and Groups on my local computer but you have mentioned adding this computer's user account to "the group of the remote server" - I can't see anything like that in the Groups list so is that something I need to add? I appreciate your help. – Leah Aug 09 '11 at 09:59
  • In the user add the group. Browse for the group located at the VPS. But please don't ask it here. Chat with a Windows Administrator about that. I'm not a Windows Administrator. – mailq Aug 09 '11 at 10:53
  • As @maliq points out in his original answer this will only work if your local computer and VPS are members of the same domain (or forest). If they are then this is (more or less) the correct way to do it. For more info into how to do this, look up AGDLP (this is Microsoft's security model for managing permissions in this way). – Matt Aug 09 '11 at 21:30
-2

Assuming your VPS is remote - do you have some type of VPN access to it? If so, that should suffice but you would always have to have the connection established. I would check with your ISP to see what they recommend for remote access to a SQL Server. If not, you could look to setup on the Win2K8 VPS but you would likely need to open up a handful of ports on the firewall.

af-at-work
  • 670
  • 1
  • 6
  • 12