Trusted connection using cached credentials?

Question

Remote users need to run a dotNet app on their home PC. The PC is not on the company domain. The app uses trusted connection to MSSQL. A postI found said this was possible using keymgr.dll to cache credentials. Alternatively, have a network drive mapped to a share on the SQL server, and those creds would be used. Neither method works for me.

Is this possible?

Answer 1

Much better probably is re-write as a web application and having SQL Login to connect to SQL Server.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Rewrite is not an option. Is there no way to establish a trusted connection from an off-domain client?

Answer 3

The user must somehow have been authenticated by the domain before he can connect to SQL Server. Else SQL Server cannot trust Windows.

How do the users log on to their home computers to day? As MACHINE\MyUser I suppose? How do they connect to the work network? VPN?

If the users would log in at home as DOMAIN\Workuser, it would be different. But then each user would have to create a login for their domain user. You can bet that this will cause trouble.

I can think of two solutions: 1) Put the application on a terminal server, which they connect to once they are connected to the network. 2) Put the application on a virtual machine which is a domain member, and give that to each user.

Although, of these, it is likely that only first option is workable. The latter would require all users to install virtualization software and have a home PC which the capacity to host it.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Putting users' home machines on the domain is not an option, VM or not.  As for RDP, the company doesn't want to pay for Terminal Server licensing.

Is there no way to do this programatically, e.g. via impersonation?  If they create a local login on the home computer with the same uid and pw as at work, how would that help?

Answer 5

If user has id = JOE at work with passwork = "TopSecret" and then creates a login on his home machine with name JOE and the same password, then one of them is Domain\JOE and the other is Machine\JOE.

Hm, well, if the users have local users on the SQL Server machines, with the same password, it should work. At least if the SQL Server box is only in a workgroup. But having each user create his account on the SQL Server machine?

You need to fix the app and have it to use SQL Server authentication. Or just forget about it, since all suggestions we make are not options to you anyway.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Erland, thanks for your suggestions.  I do appreciate your taking time to respond.

I am still curious why some report successful trusted connection using credentials cached by keymgr.dll.

Answer 7

If your users are connecting through a VPN - then you can use runas to launch the application.  Or, better yet - download ShellRunAs (part of SysInternals suite) and launch the application with that utility.

The command line would be:

C:\{path to utility}\ShellRunAs.exe /netonly {yourprogram}

This will prompt the user for their domain credentials, which will have to be entered as either 'domain\user' or user@domain.  The application will launch with those network credentials and you will get a trusted connection for that account.

---

Jeff Williams

Answer 8

I tried using "Run as a different user" and put in the domain credentials, workdomain\workuser, but it comes back with "Unknown user or bad password." This is on a Windows 7 machine on a home workgroup. The SQL server is on the work domain.

Answer 9

Hi Kaborka,

According to the link you provided, after you connected to your company PC via VPN, you can open the command prompt and run

C:\WINDOWS\system32\rundll32.exe keymgr.dll, KRShowKeyMgr

A wizard will pops up, named “Stored User Names and Passwords”. And then you could do as Tony said in the link:

Then you can add a new server to the cached credentials - e.g. click Add then
Server box: name of server e.g. MYSQLSERVER
User name box: domain and name of valid user on MYSQLSERVER e.g. MYDOMAIN\username
Password ... password for that user in that domain
OK that then try connecting and Windows should use those credentials rather than the credentials you used to log into local machine

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 10

I tried that, but it doesn't work on Windows 7. Have you tried this method?

Answer 11

Kaborka,

Yes, I've tried that in windows7 and successed, did you get any error message? Could you describe it? Is the VPN connection succeed?

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 12

The error reported by the app is the standard "Login failed for user ''. The user is not associated with a trusted SQL Server connection."

The app works fine when configured to use a SQL login, but unfortunately that isn't an option for production use, due to the role structure set up for the existing Windows logins.

Shares on the SQL server can be mapped just fine on the home machine using the domain credentials cached by Keymgr. Any idea why it might be failing for the SQL connection?

Here is the connection string: "Server=OurServer;Database=OurDB;Persist Security Info=false;Integrated Security=SSPI;User ID=;Password=;Connection Timeout=30;"

Answer 13

The app works fine when configured to use a SQL login, but unfortunately that isn't an option for production use, due to the role structure set up for the existing Windows logins.

Could this be an option? User has an SQL login which no permission but IMPERSONATE on his Windows login. Directly after connecting, the application performs EXECUTE AS LOGIN = 'Domain\username' and off you go. The username for the SQL login would be the same as the Windows login.

I should that this is a little more complicated, since there is some conflict with connection pooling.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

I tried using "Run as a different user" and put in the domain credentials, workdomain\workuser, but it comes back with "Unknown user or bad password." This is on a Windows 7 machine on a home workgroup. The SQL server is on the work domain.

Run as a different user is not the same as the command line RunAs with the /netonly qualifier.  You need to run either the command line RunAs or the command line ShellRunAs from SysInternals with the /netonly qualifier.  Example:

runas /netonly /user:domain\username program.exe

ShellRunAs /netonly program.exe

The runas command will prompt for the password on the command line whereas ShellRunAs will prompt with a window for the user credentials.  In this dialog box you specify the user as domain\user with the appropriate password.

---

Jeff Williams

Answer 15

Jeff: It worked!  I used "runas /netonly ..." from cmd.  This will be a big help to my client.  Thanks very much!

Erland and others, thanks for your suggestions.