Using runas command to remotely logging into SQL Server

Question

I am stuck at this point using the runas command. I am trying to log into a remote SQL Server instance using Windows Authentication on that server. I am using this command -

runas /netonly /user:Domain\Username 
      /server=someIP "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

But when I do this, it opens up my Management Studio - the IP is of the remote server, Windows authentication is selected by default and I have my current system's domain and username.

I actually need a way to specify a connection string to log into a remote SQL Server and fire queries on it. It's cross domain.

One more question - do I need to have Management Studio on the machine I fire the query from?

Thanks.

EDIT

I am rephrasing for better understanding.

I have server A and server B(two separate computers). I need to fire a query from A on B. A does not have any sql server or management studio. B has both. I have to use windows authentication(something like myDomain\username) to log-in into sql server of B. I am using the runas command that I mentioned in the 'Target' in the properties of management studio on B. Is that wrong? I am logging in into A using a different username(but same domain) as that I have to login into B. How do I accomplish this task?

Thanks and sorry for the late edit.

Unfit

are you trying to run SSMS this way? – Dichromatism

Answer 1

I use the following on a shortcut to run SSMS on an xp image that is NOT on the domain to connect to a server using domain credentials.

%windir%\System32\runas.exe /netonly /user:domain\username "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe -S SERVERNAME"

Pulitzer

Note that you will still see your current domain/user in the SSMS connection dialog, but it will use the /user provided. – Actable

This helped me. Without explicitly setting server address in parameter I was getting untrusted domain error. I know you can avoid it by adding user to credential manager but I am also using instances and I was not able to specify instance name in there. – Paternal

In case if get the error: "1348 the validation information class requested was invalid when executing the command, change /user: from "domain\username" to "username@domain". – Crosstie

Answer 2

That is correct, /netonly will open your current environment, and SSMS will foolishly display your credentials in the login dialogs. This is to be expected, since /netonly really means that the provided identity will be used only on the net. However, when you authenticate with any remote resource the remote site will authenticate you as 'domain\username'.

You probably don't need the /server option.

Quisling

I think I understand what you're saying. So how do I write the connection string? – Unfit

Just choose the remote server in the connect dialog. – Quisling

And select "windows authentication". There's nothing more to it than that. Also note that if you have a local server, running on localhost, it won't use the netonly credentials, but your local ones. Makes it very convenient, in my opinion :) – Pedology

Please take a look at the EDIT. I think I think it's more clear :) – Unfit

You cannot run UI processes remotely, this is not X11 server. You need to remote desktop onto B. – Quisling

Exact, it is a bug in SSMS, it shows the wrong user name when you connect with runas /netonly /user – Ka

Answer 3

I can't comment so I will give new answer. To use shortcut with server name

%windir%\System32\runas.exe /netonly /user:domain\username "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe -S SERVERNAME"

You first need to add server name and server IP to Your host file:

C:\Windows\System32\drivers\etc\hosts

Without it You need to use server IP:

%windir%\System32\runas.exe /netonly /user:domain\username "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe -S SERVER_IP"

For example You want to connect to server 10.10.10.123 using name MSSQLSERVER01 in domain MASTER using account name STACKUSER2019. So You add this line to host file:

10.10.10.123 MSSQLSERVER01

and after it run from CMD or from shortcut:

%windir%\System32\runas.exe /netonly /user:MASTER\STACKUSER2019 "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe -S MSSQLSERVER01"

Polymorphous

Answer 4

Step 1: Navigate to the ssms.exe file

SSMS 2016 version:

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe

SSMS 2017 version:

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe

right click on ssms.exe and select Properties

then the tab Compatibility

and click on the button Change settings for all users

Check Run this program as an administrator and click OK

Step 2: make a shortcut with this path

SSMS 2016 version: C:\Windows\System32\runas.exe /savecred /user:domain\username "cmd /c \"C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe\""

SSMS 2017 version: C:\Windows\System32\runas.exe /savecred /user:domain\username "cmd /c \"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe\""

NOTE: the cmd /c in the path, this makes the whole difference.

Thats it. Hope it workes for you.

PS: About step one, if you only want to make the changes to start the application as admin for the specific user, login as that user on the computer and do the same as step without the Change settings for all users

Flunk

Answer 5

In case connection using name-pipe is not available, (i.e you can't connect using SERVERNAME) then you can use the IP address, for e.g:

%windir%\System32\runas.exe /netonly /user:domain\username "Ssms.exe -S 10.20.30.10"

---

The path to SSMS.exe depends on your SQL engine version:

• 2008 R2: C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
• 2012 : C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe
• 2014 : C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe
• 2016 : C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe

Corymb