locked
Trusted connection using cached credentials? RRS feed

  • 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?

    • Edited by kaborka Saturday, March 17, 2012 1:08 AM
    Saturday, March 17, 2012 1:08 AM

Answers

  • 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

    • Marked as answer by kaborka Friday, March 23, 2012 5:44 PM
    Friday, March 23, 2012 12:23 AM

All replies

  • 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/

    Sunday, March 18, 2012 9:46 AM
  • Rewrite is not an option. Is there no way to establish a trusted connection from an off-domain client?
    Sunday, March 18, 2012 6:05 PM
  • 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, esquel@sommarskog.se
    Sunday, March 18, 2012 8:05 PM
  • 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?


    • Edited by kaborka Monday, March 19, 2012 8:10 PM
    Monday, March 19, 2012 6:59 PM
  • 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, esquel@sommarskog.se
    Monday, March 19, 2012 11:01 PM
  • 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.

    Monday, March 19, 2012 11:19 PM
  • 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

    Tuesday, March 20, 2012 2:19 AM
  • 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.
    Tuesday, March 20, 2012 11:19 PM
  • 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.

    Wednesday, March 21, 2012 8:33 AM
  • I tried that, but it doesn't work on Windows 7. Have you tried this method?
    Wednesday, March 21, 2012 4:40 PM
  • 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.

    Thursday, March 22, 2012 1:09 AM
  • 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;"

    • Edited by kaborka Thursday, March 22, 2012 6:28 PM
    Thursday, March 22, 2012 6:26 PM
  • 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, esquel@sommarskog.se
    Thursday, March 22, 2012 10:52 PM
  • 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

    • Marked as answer by kaborka Friday, March 23, 2012 5:44 PM
    Friday, March 23, 2012 12:23 AM
  • 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.

    Friday, March 23, 2012 5:49 PM