Connection string for DAO Azure Integrated set-up (1 Viewer)

[CedarTree]

Hello - my company has migrated some DBs to Azure and I want to update the DAO connection string in VBA. Suggestions for string formats that work well please? Thanks. I was trying the string(s) below for example but I get error 3170 installable ISAM error?

gsConnectionDB = "Provider=MSOLEDBSQL;Data Source=SERVER;Initial Catalog=DATABASE;Connect Timeout=0;Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true"
gsConnectionDB = "Server=SERVER; Authentication=Active Directory Integrated; Encrypt=True; Database=DATABASE;"

 

[pbaldy]

We use SQL Server users, but see if this helps:

Public Const dbPassThruConnStringCabTS As String = "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=tcp:sql-prod.abc.com,1433;" & _
                                              "Database=CabTS;Uid=OurUser;Pwd=OurPassword;Encrypt=yes;TrustServerCertificate=Yes;" & _
                                              "Connection Timeout=4900;"

Our Azure guy gave me the basics of it.

 

[GPGeorge]

Hello - my company has migrated some DBs to Azure and I want to update the DAO connection string in VBA. Suggestions for string formats that work well please? Thanks. I was trying the string(s) below for example but I get error 3170 installable ISAM error?

gsConnectionDB = "Provider=MSOLEDBSQL;Data Source=SERVER;Initial Catalog=DATABASE;Connect Timeout=0;Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true"
gsConnectionDB = "Server=SERVER; Authentication=Active Directory Integrated; Encrypt=True; Database=DATABASE;"

This appears to be a connection string for an OLEDB connection, not a DAO connection. I think you also have to include TrustServerCertificate, as Paul's sample shows.

 

[CedarTree]

This is the version I'm trying based on your input and others.... still no luck...

DRIVER={ODBC Driver 17 for SQL};Authentication=ActiveDirectoryIntegrated;SERVER=ServerName;DATABASE=DatabaseName;ApplicationIntent=READONLY;TrustServerCertificate=No;Encrypt=Yes

 

[CedarTree]

By the way, putting in just the server name + database name in the ODBC app, I can easily connect to the tables. So the string does not need a username or anything like that. But how to convert that DSN string into a non DSN string?

 

[CedarTree]

And/or can I use VBA to insert a DSN in the ODBC utility?

 

[GPGeorge]

This is the version I'm trying based on your input and others.... still no luck...

DRIVER={ODBC Driver 17 for SQL};Authentication=ActiveDirectoryIntegrated;SERVER=ServerName;DATABASE=DatabaseName;ApplicationIntent=READONLY;TrustServerCertificate=No;Encrypt=Yes

Sorry, I should have been more precise. You must use TrustServerCertificate=Yes

 

[sonic8]

This is the version I'm trying based on your input and others.... still no luck...

Did you pay attention to @pbaldy's comment? The connection string for a linked table in Access must start with "ODBC;".

 

[Minty]

That syntax looks a little odd. All our DSN's in Access are as follows:

DRIVER=ODBC Driver 17 for SQL Server;SERVER=ServernName;UID=DbUser;PWD=ServerPassword;Trusted_Connection=No;APP=Microsoft Office;DATABASE=DatabaseName

Obviously, you need to change to Trusted_Connection=Yes , and remove the user and password bits.

 

[CedarTree]

Thanks guys. For some reason, when I had tried ODBC at the beginning it kept bombing but now I tried it and it worked!