Passthrough query connstring from Access VBA to Azure SQL works on one PC and not on others!

Hi,

We have an Azure SQL database and a local Access app that needs to interact with it. Using VBA-generated pass-through query definitions to grab data as needed, no linked tables and no permanently saved queries.

On my computer, this application is working like a charm. On our others, it's not. Maddening!

The error is: 3151, ODBC--connection to '{ODBC Driver 17 for SQL Server}tcp:myServer.database.windows.net,1433' failed.

Here's as much information as I can think to provide:

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
  • Microsoft ODBC Driver 17 for SQL Server

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
  • Microsoft ODBC Driver 17 for SQL Server
  • (also SSMS)

Here's the code I have:

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 = "myUser@myDomain.com"
strPassword = "myPassword"
strConnect = _
    "ODBC;Driver={ODBC Driver 17 for SQL Server};" & _
    "Server=tcp:myServer.database.windows.net,1433;" & _
    "Database=myDatabase;" & _
    "Uid={" & strUser & "};" & _
    "Pwd={" & strPassword & "};" & _
    "Encrypt=yes;" & _
    "TrustServerCertificate=no;" & _
    "Authentication=ActiveDirectoryPassword"
qd.Connect = strConnect
qd.ODBCTimeout = 30
qd.sql = sql
qd.ReturnsRecords = True
Set rs = qd.OpenRecordset
Set SelectViaPassthrough = rs

I've been researching and trying a million things. I have a post going at MSDN.

  • I've confirmed that the firewall settings on the database in Azure are correct (and so has my supervisor)
  • I've tried to telnet to the database, per the suggestion of someone at MSDN, on each computer and got the same results on each computer
  • I've tried to use a database user rather than an Active Directory user, along with the appropriate connection string for it. Same result: works on my computer, not on others.

Would be so grateful for any answers. I have a critical project that is hung up on this nonsense.

Thanks!

0 answers