none
Access->ODBC->SQL "untrusted domain" 18452 error

    Question

  • I have a number of client databases written in MS Access connecting via ODBC to SQL 2008. The databases were originally developed on WinXP and Access 2003. They were used successfully when opened on Vista and Windows 7 on machines that are actually on the domain. The problem began when I upgraded my own development machine to Windows 7. I began receiving the now-famous 18452 error -- that I couldn't connect to the database from an untrusted domain.

    Because I am a consultant and contractor with numerous clients, attaching to the domain is less-than-preferable. (In some cases, it might even be a violation of security and NDA issues with other clients.) Therefore, I need to be able to continue to develop and use these databases in the manner that I was able to do before.

    This is the particulars and what I have tried/determined so far:

    • The SQL server is in mixed mode authentication -- which is why I have been able to do this for 3 years.
    • I can connect to the server (and other servers on the domain) and browse directories, etc. as long as I use a login prompt with my domain ID. This used to be enough to then permit connection to the SQL server. However, in Windows 7, this is no longer the case.
    • I can connect to the SSMS using SQL Authentication.
    • I can connect to the SMSS using Windows Authentication if I use the much-publicized "runas /netonly" hack. (This hack is not usable, however, for an Access mdb or mde file because you can only use it for exe files.)
    • I can connect via ODBC from Access if I use SQL Authentication.
    • I have added the connection information for the domain and all servers in the (apparently useless) Credential Manager to no avail.
    So, it seems that what has changed is something particular to the way Windows 7 stores credentials. The seeming contradiction is that I can get to all the files, folders, shares, printers, etc. on all the domain machine (including the one that hosts the SQL server), but can NOT connect to the SQL server at all. That seems a little odd to me.

    So, is there something that needs to change on either my machine, the server, the domain, or in the SQL configuration in order to recognize my machine as being allowed in the same fashion that it is for the other network fashions above?


    Dave Mark -- Intrinsic Algorithm LLC
    Monday, November 22, 2010 4:36 PM

All replies

  • Hi Dave,

    normally the following would apply:

    http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452

    * I can connect via ODBC from Access if I use SQL Authentication.

    How do you connect when it fails?

    Take a look at

    http://www.connectionstrings.com/sql-server-2008

    Have you specified the "Integrated Security=SSPI;" part?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Monday, November 22, 2010 5:10 PM
  • As is usually the case, Access writes the connection string when you originally set up (or re-link) the ODBC connection for a linked table. I'm using a System DSN to provide the data on the server. However, the DSN doesn't include the connection information. Copying from one of my tables' property settings:

    ODBC;DSN=[xxx];Description=[xxx];APP=Microsoft Office 2010;DATABASE=[xxx];TABLE=[xxx]

    The relevant information that was redacted above was all correct.

    Regarding your MS-link to the error, I'm aware of all of this. All of the possibilities are listed in my bulleted list. The point being, I am allowed to browse domain servers via Explorer simply by entering my login credentials when asked. Prior to 6 months ago, I was able to do this simply because the we had set it in the domain to trust me.

    Apparently this summer a security patch came out that insisted that I log in manually to do so. Windows was polite enough to ask me the first time I connected and would then remember my token. Access would not ask this, however. The solution at the time was to simply attempt to connect to a network drive, enter the login information, and only then run Access.

    This was all under the old method of WinXP and Access 2003. Under Win7 and/or Access 2010, this method no longer works... while I can access the network drives (after entering my info as before), this information is apparently not used by Access or even SSMS.

    I repeat... the laptop, OS, and version of Office are the ONLY things that have changed.

     

    Thanks,

     


    Dave Mark -- Intrinsic Algorithm LLC
    Monday, November 22, 2010 5:27 PM
  • Hi IADaveMark,

    There are a few workarounds:

    1. create a VPN connection while you are on client site (however, it could be slow - it depends)
    2. have clients' sql server databases on your own laptop (restored from backup) and develop your own linked tables manager (requires some development work).
      You could develop on your laptop and then deploy a new Access application on clients' domains.

    Sergei
    Monday, November 22, 2010 10:35 PM
  • Not sure how #1 helps because I can't get in via the VPN when I am working from home either (anymore).

    As for #2, that doesn't help either because a lot of the work I need to do must be on the live data (it's not just development work).


    Dave Mark -- Intrinsic Algorithm LLC
    Monday, November 22, 2010 11:06 PM
  • May be this workaround

    1. Create your own VPC image with Access 2010 (MS VPC virtual machine)  on the external hard drive.
    2. In the settings for virtual machine configure "shared folders" pointing to Access 2010 mdb folder on your external hard drive.
      So changes to Access mdb will be saved on your external hard drive.
    3. Install MS VPC (free download) on any computer on the client network.
    4. Connect your external hard drive to that computer
    5. Start virtual machine on the client computer and add your virtual machine to the Virtual PC Console
    6. Join VPC image to the client domain (you don't join your laptop)
    7. Start Access application (from a shared folder) and connect to SQL Server
    8. Do your work on the live data

    Sergei
    • Marked as answer by Ai-hua Qiu Tuesday, November 30, 2010 8:38 AM
    • Unmarked as answer by IADaveMark Tuesday, November 30, 2010 5:06 PM
    Tuesday, November 23, 2010 12:56 AM
  • I'm mildly annoyed that this was marked as an "answer" when it was really a hack. It didn't answer the question that I asked. If I wanted to jump through that many hoops, I would simply have just joined the domain.
    Dave Mark -- Intrinsic Algorithm LLC
    Tuesday, November 30, 2010 5:06 PM
  • Hi IADaveMark,

         >>So, is there something that needs to change on either my machine, the server, the domain, or in the SQL configuration in order to recognize my machine as being allowed in the same fashion that it is for the other network fashions above?

    Thank you for your time to report an issue.

    In my humble opinion, your question is too wide (for example, referring to domain, server, your machine and including two applications (MS Access and SQL Server) to be answered without having details on domain/server/your machine configuration.

    Clients' domains have different configurations and different operating systems on their domain controllers. It means a solution provided for one domain might not work for other domain, etc.


    Sergei
    Tuesday, November 30, 2010 10:35 PM
  • You are correct that something has changed in the way Windows 7 (actually Windows Vista and higher) store credentials in Credential Manager. Fortunately, there is a simple workaround which should help.

    In Credential Manager, you are probably today typing the target name as something like "foo.corp.com". Instead, assuming you are connecting to SQL Server over TCP and it is on the default TCP port 1433, you should specify "foo.corp.com:1433".

    The reason for this is that Windows Authentication relies on the Service Principal Name ("SPN") that SQL Server clients provide, to build the target name that is passed to Credential Manager. But, the underlying Windows Authentication in Windows Vista and higher doesn't remove the port # from the end of the Service Principal Name that the client provides. SQL Server clients, when connecting over TCP, use SPNs like "MSSQLSvc/foo.corp.com:TcpPortNumber", so as a result, the Credential Manager entries need to have the port # appended to them.

    Friday, December 03, 2010 6:32 PM
    Moderator
  • OK.. NOW we're getting somewhere! Still can't quite get there, however.

    So, in the credential manager, I normally have it listed as simply:

    [servername]

    rather than:

    [server].[domain].[com]

    I added two entries to the Cred Manager... 

    [servername]:1433

    [server].[domain].[com]:1433

    using my domain login of [domain]\[uid]

    Neither one seems to work when I simply try to connect my ODBC connection to it.

    Thoughts?



    Dave Mark -- Intrinsic Algorithm LLC
    Friday, December 03, 2010 6:44 PM
  • This thread could shed some light on user accounts Configuring and Troubleshooting NTLM and Kerberos on Windows 7 (Windows Server 2008) and IIS7 - section "SPN-s in Win 7 and Win Server 2008 R2" and further down to "Trusted delegation".

    Also, I would suggest to post your question on Windows forum.
    Sergei
    Saturday, December 04, 2010 7:32 AM
  • Using credential manager with the port fixed my issues with linked servers and trusted domains.  This saved me a ton of time.
    Tuesday, December 11, 2012 5:38 PM