none
DAO connection string for Access works on one computer but not on others

    Întrebare

  • Hello,
    I've done several days of research, tried and failed with tons of things I've found in various forums, before bothering to post here. I'm sure the answer is out there, but I'm not finding it.

    I am using Access VBA to create "DSN less" passthrough queries (in code; not an existing query). I got one of the connection strings to (finally) work on my computer--but _only_ on my computer. It won't work on other computers with Access 2016, or for one colleague with Access 2010 (we were in the same IP range, which is allowed on the Azure SQL firewall, I checked).

    So, if it works on mine, why not on others? I also checked the References, and we have the same five enabled.

    Currently I have the recommended connection string for ODBC from our Azure SQL portal, and it's not working _anywhere_. :-/

    Below is my code. What am I doing wrong? Thanks for any help.

        Dim qd As DAO.QueryDef
        Dim rs As DAO.Recordset
        Dim strConnect As String
        Dim strUser As String
        Dim strPassword As String
        Set qd = CurrentDb.CreateQueryDef("")
        strUser = "user@domain.com"
        strPassword = "password"
        strConnect = "Driver={ODBC Driver 13 for SQL Server};" & _
            "Server=tcp:myserver.database.windows.net,1433;" & _
            "Database=mydatabase;" & _
            "Uid=" & strUser & ";" & _
            "Pwd=" & strPassword & ";" & _
            "Encrypt=yes;" & _
            "TrustServerCertificate=no;" & _
            "Connection Timeout=30;" & _
            "Authentication=ActiveDirectoryPassword"
        qd.Connect = strConnect
        qd.sql = sql
        qd.ReturnsRecords = True
        Set rs = qd.OpenRecordset


    10 iulie 2018 16:02

Toate mesajele

  • Hello,

    Have you installed the same ODBC driver on all computers?

    https://docs.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-2017


    Have you disabled any security software (Avast, Symantec, Kaspersky, Windows Firewall) on PC were this connection is not working?


    Share with us any errors.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    10 iulie 2018 20:32
  • Hi and thanks for the reply,

    I'm not sure the article you link is relevant. It's an ODBC driver for C++, and my issue is with ODBC for Access.

    I have installed ODBC 17 on each computer. But the point is to (hopefully?) find a connection string that works regardless of Access version or ODBC version.

    Below are two connection strings that worked on my computer, but not on another computer with the same version of Windows, the same ODBC 17, and the same version of Access (slightly different build):

        strConnect = _
            "ODBC;DRIVER={ODBC Driver 17 for SQL Server};" & _
            "server={tcp:myServer.database.windows.net};" & _
            "Database=myDatabase;" & _
            "UID=myuser@mydomain.com;" & _
            "PWD=myPassword;" & _
            "Authentication=ActiveDirectoryPassword"

        strConnect = "ODBC;DRIVER={SQL Server Native Client 11.0};" & _
            "Server=tcp:myServer.database.windows.net;" & _
            "Database=myDatabase;" & _
            "Uid=myUser@myDb;" & _
            "Pwd=myPassword;" & _
            "Encrypt=yes;"

    18 iulie 2018 08:27
  • Sorry; I shall also check the security software. (I suppose I would then need to tell Windows Defender somehow to allow this connection? Although I'm not confident that's the problem here.)
    18 iulie 2018 08:30
  • Hi again,

    I disabled the firewall and antivirus services we are using (both the built-in Windows solutions) and tried again with each of the connection strings that work on my computer. No joy.

    Here is the version information for the two computers I'm currently working with. (Of course, I have a colleague with Access 2010 on Windows 7 that I also want this to work for, and I hope to get it working regardless of version.)

    Computer that doesn't work
    --------------------------
    Windows 10 Enterprise Version 1803 [10.0.17134.165]
    *joined to Azure AD*
    --------------------
    Microsoft Access 2016 MSO (16.0.10228.20080) 32-bit
    Version 1806 (Build 10228.20104 Click-to-Run)
    Product ID: 00265-80140-72778-AA474

    Computer that works
    -------------------
    Windows 10 Home Version 1803 [10.0.17134.112]
    ---------------------
    Microsoft Access 2016 MSO (16.0.10228.20080) 32-bit
    Version 1806 (Build 10228.20098 Microsoft Store)
    Product ID: 00201-10991-96151-AA119


    18 iulie 2018 09:29
  • Sorry, I realise I haven't shared any errors with you as requested.

    I get the dreaded 3151 regardless of which connection string I use.

    When using the connection string for the database user:
    3151 ODBC --connection to {'SQL Server Native Client 11.0}myServer.database.windows.net' failed.

    When using the connection string for the Azure AD user:
    3151 ODBC --connection to '{ODBC Driver 17 for SQL Server}{tcp:myServer.database.windows.net}' failed.

    18 iulie 2018 10:45
  • Hello,


    Let’s try the following steps on the computers where the ODBC connection is not working.

    If you ping the name of your logical Azure SQL Database server you should receive a request time out message, but along with the message you should see the IP address of your server resolved. If you don’t see the IP address then you have a DNS resolution issue.

    Use the IP provided on previous step and perform a telnet command like:

    C:\> Telnet IP 1433

    You need to enable Telnet on Control Panel to be able to use it.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    18 iulie 2018 15:57
  • Hi,

    Thanks again for the reply and the assistance.

    I get the same response from both the working computer and the nonworking computer. Ping finds the IP address on both computers. I had to go into telnet without prompts in order to capture what was happening.

    ------------------------------------------------------------------------------------------------

    Welcome to Microsoft Telnet Client

    Escape Character is 'CTRL+]'

    Microsoft Telnet> o ~~.~~.~~.~~ 1433
    Connecting To ~~.~~.~~.~~ ...

    Connection to host lost.

    Microsoft Telnet>

    19 iulie 2018 08:51
  • ...telnet without prompts in order to capture what was happening...


    Sorry, that should be "...without parameters..."

    23 iulie 2018 07:47
  • Hi,

    The problem is almost sorted:

    ODBC 13.1 was the missing piece. The working computer had both 13.1 and 17, while the others had only 17.

    BUT: this only solved the issue if using a connection string with SQL authentication. We really need to use Active Directory authentication.

    Same issue: connstr with AD auth works on my computer, but not on the others. Some component is present on my machine and not on the others, and I've googled a ton and looked at loads of things, but haven't found it. Any ideas?

    Thanks.

    26 iulie 2018 21:44