locked
Access to MSSQL connection across domains RRS feed

  • Question

  • Hi,

    I have a problem I hope you are going to help me solve.

    Sorry about the length of this post, but I thought it was worthwhile writing all the relevant things I could remember....

    I have two domains that do not have a trust relationship between them and there is no prospect of being able to establish one. In one domain, the "client" domain, I have an MS Access DB that has been "upsized" (using the Wizard) to migrate the data to MSSQL. In the other domain (the server domain) I have the MSSQL server. All systems are Windows XP Pro, the MSSQL server is 2005 and the Access is 2003.

    What I want to be able to do is connect to the MSSQL server from the client domain with the MSSQL server set to "Windows Authentication". I cannot use MSSQL authentication as this is not sufficiently "strong".

    However, I have not been able to do this, despite the fact that I can map a network drive (that exists on the server) on the client system, using the SAME username and password that exists on the server domain.

    The MSSQL has the user added as a user and has all the correct permissions, which has been tested by running the Access DB from a client within the server domain.

    The Access upsize wizard migrates the Access tables to be ODBC linked tables, so I have been playing around with the connection string and have used ALL sensible combinations of the following options (including options not present):

    DRIVER={sql server}; also tried {sql native driver}
    DATABASE=<databasename>;
    SERVER=<servername>;
    Persist Security Info=True; also tried false
    Trusted_Connection=no; also tried yes
    UID=SERVER_DOMAIN\cpaterson;
    UID=cpaterson;
    PWD=apassword;

    None of them work, where they don't throw up errors about invalid options the error that is logged at the MSSQL server is:

    2009-11-13 11:46:05.43 Logon Error: 18452, Severity: 14, State: 1.
    2009-11-13 11:46:05.43 Logon Login failed for user 'cpaterson'. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.110]

    I have even gone as far as trying ADO connections rather than the ODBC connections (which I think might mean I would have to rework my Access code considerably), but I get the same error.

    If I enable Mixed mode Authentication (and use an appropriate username and password) it works with ODBC and with ADO.

    Am I trying to do the impossible?

    Why can I map a drive but not connect to the MSSQL server?

    Any suggestion how to solve my problem or where else I might get help?

    Regards

    Callum
    Monday, November 16, 2009 3:28 PM

All replies

  • Callum,

    As far as I understand, without trusted domains cross domain windows authentication is not possible as their credentials wouldn't be validated. You're only options as I am aware are using SLQ Server authentication and trusting the domains, adding the given domain user, and setting the account is trusted for delegation active directory profile property.

    Sorry,
    -h
    Monday, November 16, 2009 4:28 PM
  • Hi Callum,

    Here are the steps from another thread in the forum, but I am not sure that is what you want:

    1. Run the following from the command line:
                   "rundll32.exe keymgr.dll, KRShowKeyMgr"

    This opens the Windows Credentials Logon Store which allows you to specify different Windows Auth Credentials when connecting to different servers - regardless of your logged in Windows Credentials.  Note that it is NOT necessary to reboot after adding any credentials for new servers.

    2. I clicked "Add" and then typed in the name of the remote server along with the remote domain credentials to use when logging in to that server.
    3. Log in to your VPN to allow access to the remote servers
    4. Connect to the SQL Server by typing in the Server Name and selecting Windows Authentication.

    Much simpler than some of the other solutions presented here.  Thank you everyone for your assistance :)

    Link to the thread: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/b583efa2-47df-4b45-ba05-ec1622ee3d3c/

    Hope this helps.


    ChunSong Feng
    Microsoft Online Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 18, 2009 8:50 AM
  • Hmm, but I don't think I want cross domain windows authentication, what I want to be able to do is log in on the remote server with username and password defined on remote server, just as I have managed to do when logging in to the share.  I can log in to the share with any username and password that is valid on the system hosting the share, but it appears that I can't login to MSSQL server using these credentials!  :-(

    Callum
    Thursday, November 19, 2009 12:50 PM
  • I had a quick look at this, but I don't think I'll be able to run this from the PCs involved (the customer has a "very locked down system").

    On my system I didn't have an add button which made it impossible to add some credentials (even tried right clicking, double clicking....)????

    Callum
    Thursday, November 19, 2009 1:02 PM