Have you ever faced situations like below?
1- You are logged in to one server with your domain credentials and some other user wants to connect to sql server using his domain credentials but he does not have logon rights to that server
2- You have SQL Server instance running in different domain and you have sysadmin rights to that instances however you do not have logon rights to any of the server in that domain in that case how to launch management studio or sqlcmd?
3- You are managing hundreds of SQL Server instances in different domains you are sysadmin to all the sql servers but no logon access to the servers and you want to run a query to all the instance from single central server.
Roadblock isn’t it?
I also faced the same situation, I did some research and found an easy solution so thought of sharing with you.
Let me try to explain you with an example.
Note: All the scenarios are applicable only for windows authentication.
I logged into a server (“SQLactionnNode1”) which is in “SQLACTIONS.com“ domain and account name is sqlactions\administrator I have one SQL Server instance “ALWAYSON” running in different domain (“Manish_DC.com“) and i want to connect that instance with Manish_DC\adminstrator domain account from this server.
If this user (Manish_DC\adminstrator) has administrator rights to this server(“SQLACTIONNODE1.sqlactions.com”) then its quite easy, just run “runas” command and specify this user or right click on SSMS.EXE select runas and give the user account, in this case you are allowed to login. But if you do not have any logon rights to the current server you will receive below error.
RUNAS ERROR: Unable to run - ssms.exe 1385: Logon failure: the user has not been granted the requested logon type at this computer.
However in the same command i tried to explore other possible options and I found “/netonly” seems to be useful in such scenarios.
/netonly command is useful if the credentials specified are for remote access only
Below are the steps on how to use it.
go to command prompt and run the below command
First I launch ssms.exe without /netonly command and I got below error which confirms that i this account does not have access to the SQL Server.
Now i will launch management studio with below command.
runas.exe /netonly /user:manish_dc\administrator “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe”
or go to this directory (C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\) in command prompt and type below command.
runas.exe /netonly /user:manish_dc\administrator ssms.exe
it will ask you for password and then it will launch the ssms.exe.
Note: When it prompt for password if you enter wrong password it sill allow you to launch the SSMS.EXE but you will get error message “User is not associated with trusted domain”.
Look at the login account, remember i am logged into the server with sqlations\administrator and i am trying access SQL Server with manish_dc\administrator.
so if i look at the screen i should get the login failed error message like above but this time i tried to connect and i was able to connect to SQL Server instance successfully.
So if i go with /netlonly it will still show the same screen but it will allow me to login ….check the screen shots below.
Now as we can see in the below screen it still shows its logged in with sqlactions\administator (but this account does not have access to sqlserver at all) i run below query and look at the loginame column.
I also noticed one more thing, if you try this command with sqlwb.exe in that case at the bottom of the screen you will get the login id same like nt_user.
Hope you find this useful!!!!