THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Using Windows Authentication from a non-domain joined machine

Sometimes it is necessary to use Windows domain credentials to authenticate with a SQL Server from a non-domain joined machine. Here’s a trick from my coworker Martin Kastenbaum to pass Windows domain credentials from a non-domain joined machine. You can pass your Windows credentials to SQL Server Management Studio by making a simple modification to the shortcut you use to launch it. It's probably a wise idea to make a copy of your SSMS shortcut and make all changes to the copy, not the original.

image image

Figure 1. SSMS 2005 shortcut on the left, SSMS 2008 shortcut on the right.

You need to modify the target, but first you should press the Change Icon… button and save the path to the icon. You’ll need to use it later.

image

Figure 2. Save the path for the icons.

Modify the shortcut’s target as follows:

SQL Server 2008 x64: C:\Windows\System32\runas.exe /user:YourDomain\YourUsername /netonly "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\VSShell\Common7\IDE\Ssms.exe -nosplash"

SQL Server 2008 x86: C:\Windows\System32\runas.exe /user:YourDomain\YourUsername /netonly "C:\Program Files\Microsoft SQL Server\100\Tools\binn\VSShell\Common7\IDE\Ssms.exe -nosplash"

SQL Server 2005 x64: C:\Windows\System32\runas.exe /user:YourDomain\YourUsername /netonly "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe -nosplash"

SQL Server 2005 x86: C:\Windows\System32\runas.exe /user:YourDomain\YourUsername /netonly "C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe -nosplash"

The –nosplash is optional. It slightly speeds up the SMSS startup time.

Modifying the shortcut’s target messes up the icon. You’ll want to fix that by pressing the Change Icon… button again and restoring the path to the one you saved earlier. In case you didn’t save the path, the default values for the path to the icon are:

SQL Server 2008 x64: %ProgramFiles% (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

SQL Server 2008 x86: %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

SQL Server 2005 x64: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe

SQL Server 2005 x86: C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe

When you start SSMS from a modified shortcut, you’ll be prompted for your domain password:

image

Figure 3. Password Prompt

Notice that the Connect to Server dialog is misleading after making this change. Although it properly shows I’m using Windows Authentication, it incorrectly indicates that my credentials are Win7L\John, which they are not. Once I click connect, I’m connected with the Domain\Username supplied in the modified shortcut.

image

Figure 4. Notice that the supplied domain and username do not appear in the dialog box.

This is a handy trick for consultants who use their own laptops to connect to a client’s domain joined SQL Server.

Published Friday, February 26, 2010 6:28 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bill Graziano said:

Thanks!  This is fantastic!

February 27, 2010 7:22 PM
 

ALZDBA said:

One side note though, it ruins all your SQLauthenticated connection settings of registered servers !

February 28, 2010 12:04 PM
 

AaronBertrand said:

ALZDBA, isn't that only for an instance of SSMS launched from that version of the shortcut?  I have used this trick in the past, but created a dedicated shortcut and only used it when I explicitly needed to connect to that other domain - so I wasn't using any of my other registered servers.  I don't recall my registered servers ever getting messed up... can you elaborate?

February 28, 2010 10:41 PM
 

Cary Wagner said:

Just brilliant!  Works great!!!  Thanks for posting.

Cheers...

May 6, 2010 3:58 PM
 

Max said:

it did not work for me. I get the error:

Cannot connect to SERVER01.domainblah.net.

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

April 11, 2011 4:52 PM
 

Chad said:

This absolutely 100% DOES NOT work for me - whenever I modify the target and paste it in and save it, I get the following message:

"The name <file path> specified in the Target box is not valid.  Make sure the path and file name are correct."

Why does this have to be so complicated?  Seems like getting to another domain should be much easier than this...in any case, your steps above do not work.

April 22, 2011 9:20 PM
 

John Paul Cook said:

I understand the frustration of not having something work. This technique definitely works, but unfortunately not for everyone. If you have a file or path error message, I suggest taking just your fully qualified path to the SSMS executable and pasting it directly into an elevated command prompt window. That will test the validity of your fully qualified path. Since the machine you'd be using with this technique isn't joined to the domain you are trying to connect to, it is possible that the domain in question is configured to require that both the machine and the user login be in the domain. If your machine must be joined to the domain of interest and it isn't, there's no workaround for that.

April 23, 2011 9:03 AM
 

Jeff said:

Works perfectly...thanks

May 10, 2011 3:27 PM
 

Cecil Champenois said:

This did work for me, but not at first, due only to not typing it perfectly. I had to go back and make a space between my domain user name and /netonly.

May 20, 2011 10:48 AM
 

Nathan H. Omukwenyi said:

For the error "The name <file path> specified in the Target box is not valid. Make sure the path and file name are correct." you can put the -nosplash switch outside the double quotes. Works just fine.

May 28, 2011 9:54 AM
 

Mike said:

Works for me. Windows Authentication username is incorrect in screen, but it used the right account.

June 6, 2011 4:41 PM
 

anusha said:

when i am entering password in password prompt, it was not taking the input ,can u plz tel me what may b the problem

thanks & regards

anusha

July 4, 2011 3:52 AM
 

Stephanie said:

I have the same problem as Anusha. I get the runas prompt but it won't let me type the domain/password.

July 18, 2011 4:42 PM
 

Dawie said:

Works fine for me!

Thanks

September 24, 2011 7:35 AM
 

erick garcia said:

thanks a lot, WORK 100%.....

October 19, 2011 10:59 AM
 

Charlie said:

Works perfectly great and for existing connections on local machine

November 8, 2011 7:15 PM
 

Plato said:

This works for TOAD 5.6 for SQLServer (freeware).  Remember to exclude "-nosplash " from the parameters supplied to Toad executable.  Everything else is as stated in this article.  

Thanks John...

January 16, 2012 1:10 PM
 

Periasamy Senniappan said:

Works perfectly

May 8, 2012 5:11 AM
 

Navneet said:

Simply brilliant dude. It worked just fin

June 6, 2012 2:38 AM
 

Mark Cleary said:

There is another way to do this - use the Windows stored credentials manager to save the credentials you need for the target domain. This is where IE (and other things but not terminal services) store passwords when you check that save password check box.

This has the advantage that the same instance of SSMS can simultaneously access different servers using different credentials.

The way to do it is to use "control keymgr.dll" to start it. The window title will be "Stored Usernames and Passwords" in XP. To activate it via the control panel you need to be an administrator because you need to activate User Accounts first and then select the Advanced tab and click the Manage Passwords button.

Anyway, once you've got the stored credential manager UI up, create a new entry for the target server. When you connect to any network resource on that server, those credentials will be used. There is no validation when you create the entry. If you have the wrong password you'll get a 18452 error saying you are from an untrusted domain.

If you sometimes connect via IP address, it's helpful to put in an entry for that too.

At my customer's site, the DNS setup requires the FQDN for the SQL Server so I have to put that in the entry, not just the short name.

July 24, 2012 10:29 PM
 

Alex said:

Works like a charm.

BTW, you do not really need to type full path to runas.exe

Something like this works just fine (in windows 7 from cmd):

runas.exe /user:acme\alex /netonly "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

October 12, 2012 3:16 PM
 

schacko said:

Alternatively... In Windows 7, you could just hold the shift key, right click on the SSMS icon, and select "Run as different user".  Then type your user name in with Domain\user format.

FYI, the shift key plus right-click on mostly every icon will bring up the Run As feature.

November 7, 2012 2:28 PM
 

Carlos said:

FWIW...  "run ass with /netonly" on command line works perfectly for me.  Run as using shift right click as @schacko said results in failed authentication.

November 9, 2012 10:06 AM
 

Arnie said:

I used the following with SQL Server 2012 x64: C:\Windows\System32\runas.exe /user:DOMAIN\UserName /netonly "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\binn\ManagementStudio\Ssms.exe -nosplash"

Thanks for the article.

November 24, 2012 9:01 AM
 

Linda said:

Thanks you so much!!! Works great..!

March 7, 2013 1:52 PM
 

Sidd said:

Hi

I am able to connect successfully on Windows7 but when i use the same command on Windows 8, i get error as System cannot find the file Ssmx.exe. i am using SQL Server 2008 X86 , so looking at the following path:

%ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\

September 24, 2013 10:11 AM
 

BitSar said:

Excellent workaround - this helped out a lot and worked like a charm.

October 9, 2013 8:26 PM
 

Poncho said:

Great Post, works awesome! This will make my startup as a consultant so much easier on client sites. Thanks for posting!

November 5, 2013 9:02 AM
 

Karen said:

This is exactly what I'm looking for...but cannot proceed further than the command password prompt

November 20, 2013 6:21 AM
 

KN2 said:

Works for me.. Recent update to Windows 8.1 left me with my "windows live" account attempting to authenticate to my local Domain and SQL instance on another computer in my domain.. this lets me continue to use my domain\user account

Works with Windows 8.1 and SQL Server 12 Client tools to SQL Server 2008 instance.

Thanks dude, you the man.

December 1, 2013 7:03 PM
 

Karthic Raghupathi said:

Excellent! Works brilliantly!

December 20, 2013 2:03 PM
 

Tom said:

Worked for me, too.  Took me a couple of tries and I'm not sure what I did differently but it ultimately worked.

OS Windows 7, SMSS 2008 R2

January 22, 2014 9:42 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement