SSMS with RUNAS – trapping failed network logon

Question

SSMS with RUNAS – trapping failed network logon

Phil Parkin

SSC Guru

Points: 246963
More actions
March 25, 2015 at 3:16 am

#298931

I'm sure that a few of you use this command to open up an SSMS instance for accessing databases in domain [domain] using Windows authentication:
runas /user:[domain]\[username] /netonly "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"
Where [domain] is different from the domain you are currently logged in to.
It works well, except for one thing: when you run it, you are prompted for a password. If you enter that password incorrectly, SSMS launches anyway and you have to close it and try again.
Has anyone found a way of avoiding this, such that SSMS is launched only if password is correctly entered?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

joshdbguy SSCertifiable Points: 7707 More actions · Answer 1

I don't use /netonly. When I run it that way, if I enter the wrong password, it just exits cmd. If I include /netonly and run it, it continues to SSMS. Don't know of another way around.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I don't use runas /netonly any more for that reason (and I hate typing passwords), so now I store the credentials in the Windows Credential Manager and just run SSMS as normal.

I describe how to do that over on SA : http://dba.stackexchange.com/questions/66014/connect-to-sql-server-with-windows-authentication-in-a-different-domain/66022#66022

Make sure to read the comments on that as well - I found later that the name of the sql instance that you register in Credential Manager is case sensitive and must match the name returned by an nslookup or ping.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Phil Parkin SSC Guru Points: 246963 More actions · Answer 3

mister.magoo (3/26/2015)
I don't use runas /netonly any more for that reason (and I hate typing passwords), so now I store the credentials in the Windows Credential Manager and just run SSMS as normal.
I describe how to do that over on SA : http://dba.stackexchange.com/questions/66014/connect-to-sql-server-with-windows-authentication-in-a-different-domain/66022#66022
Make sure to read the comments on that as well - I found later that the name of the sql instance that you register in Credential Manager is case sensitive and must match the name returned by an nslookup or ping.

Well, that took some serious pratting about before I could make it work. Everything has to be 'just right', including case.

But now it's done ...COOL stuff 😎

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Glad you got it working - it takes some people quite a while to get to grips with it, especially figuring out the required naming in WCM.

The next pain you get is when you have a lot of these stored credentials and you need to update them with a new password, but I have something for that too 😀

I use a plain old DOS batch file to update them using the command "cmdkey.exe".

I cannot share that script as I don't want the pain of people asking for support when they mess up with it - and it is too much bother to make it generic.

However, the basic gist of it is:

@@set /p password=Enter your new password:

@@cmdkey /generic:<name of stored credential> /user:<domain>\<user> /pass:%password%

I have a lot of servers with common prefixes, which means I can use a for loop to update their stored credentials, but if you have one or two, you can just as easily update them manually through control panel.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Phil Parkin SSC Guru Points: 246963 More actions · Answer 5

Yes, I am already 'looking forward' to regularly changing passwords for them all 🙂

Good to know that there is an automated way of doing that. I might get back to you if I decide to attempt automation.

Cheers.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I like the option presented by MM.

Just throwing out another option. At a previous employer, we used a posh script to authenticate and launch ssms. If the creds were wrong, you would be reprompted to enter the correct creds. If you successfully authenticated to the domain, then ssms would launch - but not if you failed authentication.

If you are good with powershell, this would be a good option.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events