How do I connect to a SQL Server which is usually a machine I remote desktop into

Question

Typically I remote into a machine with IP Address 00.00.00.00 and then I have an account in a domain, let's call it myspecialaccount\firstname.lastname.

Then I use Windows auth to connect to SQL Server instance for example:

ABCLACSQLC123\DEV04A

So my question is HOW can I connect from my laptop through SSMS directly to the machine (pending ports are open etc..)

Answer 1

In order to use Windows Authentication, you'd have to add the credentials you use to login to the laptop as a "Login" to the SQL Server. That can only be done if

1. You login to your laptop with a domain user and
2. The user is in the same domain in which your SQL Server instance resides

Otherwise, you have no choice but to use SQL Server Authentication.

In this case, you login to your laptop with a user in "Corp" domain, but SQL Server instance is in "Services" domain. So it won't work. Unless I think both domains are part of the same Forest.

Answer 2

Install SQL Server Management Studio Express on your laptop. Microsoft has made the download link obscenely hard to find on their own site, but I did manage to find it here. Download the one for your system, probably x64.

Installation isn't much easier. Once everything is extracted, run the program, and switch to the installation tab, and choose "Standalone installation or add new features". Continue along the installation, and just install the management tools.

Once installed and running, use the Connect to Server dialog (it should open when you start the program, but if it doesn't, it's the first option under the File tab), and target wherever you want to connect (IP or server name should both work). If your laptop also authenticates to the same server that handles Windows authentication for your database, you can use Windows authentication, otherwise, you'll have to create a SQL Server account to use for login.

Answer 3

Look at this answer : https://stackoverflow.com/a/1615431/3317709. There is no trick to login, unless you get rid of the "Network related..." error. If you are getting this error, SSMS is not even able to find your server let alone logging into it. Once you get "Login failed..." error, from that point, we can tinker and try to get thru using your windows auth.

Answer 4

Try creating a shortcut to runas.exe, pointing to SSMS.

C:\Windows\System32\runas.exe /netonly /user:myspecialaccount\firstname.lastname "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

(The path to your SSMS exe may vary.)

When you double-click the shortcut, this will open up SSMS. You should then be able to connect to your instance (ABCLACSQLC123\DEV04A) as if it were on your local machine.

See here for more info on the runas command: https://technet.microsoft.com/en-us/library/cc771525.aspx