MS Access connect to Azure SQL through SQL Native Client

Question

Hello all,

I am trying to connect with MS Access 2016 to Azure SQL DB through SQL Server Native Client 10.0, but when connecting I can see only the master DB.

Due to some restrictions, I can only use the SQL Server Native Client 10.0 and not the latest releases..

Also, I have given to the user the db_owner role, so I don't think this is a permission issue.

I don't know if I have done any config wrong, but please can you help me with any kind advice of regarding this issue.

Kind Regards,

ebartos.

Answer 1

You need to supply the specific database name in your connection string in order to connect to a database. Azure SQL Database, by default, just like regular SQL Server, will connect you to the Master database. However, unlike regular SQL Server, you don't then get the option of using the USE statement (or other methods) to switch the database. Instead, you need to connect straight to the database you're hoping to work with. Here's a great resource on getting connection strings right, specifically for Azure SQL Database.

Answer 2

Hello,

Please follow the steps provided on the following article:

https://debugmode.net/2011/04/22/connecting-microsoft-access-to-sql-azure/

For the procedure explained on the article, you will need to open the database with Azure portal, and make a click on “Show database connection strings” link. As you can see below, the connection string specifies the user database to connect to.

Server=tcp:morillo.database.windows.net,1433;Initial Catalog=AdventureWorksLT;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 3

Dear Grant, Dear Alberto,

Thank you for your support.

I tried the above links and they work ok with SQL Native Client 10.0 but it happens that the SQL Native Client was 6.0 version and I don't know if Azure SQL supports this version.

Please, can you give some kind of support, if this driver is still supported.And if not, then should I upgrade to a latest one ?

Thanks,

ebartos. 

Answer 4

I don't know for certain. I don't work for Microsoft. However, the way to test it would be the same mechanism as outlined. You need to supply the database you're connecting to as part of the connection. That's how it has to work, regardless of the version of the client you're using.

Answer 5

I tried the above links and they work ok with SQL Native Client 10.0 but it happens that the SQL Native Client was 6.0 version and I don't know if Azure SQL supports this version.

Please, can you give some kind of support, if this driver is still supported.And if not, then should I upgrade to a latest one ?

What exactly do you mean by SQL Native Client 6.0? Perhaps you mean the "SQL Server" ODBC driver listed under ODBC drivers. That is a legacy ODBC driver that ships with Windows for legacy application compatibility but should not be used for new development and may not work with Azure SQL Database.

There were 3 major versions of SQL Server Native Client released, with SQL Server Native Client 11.0 being the latest:

• SQL Server Native Client (SQL 2005)
• SQL Server Native Client 10.0 (SQL 2008)
• SQL Server Native Client 11.0 (SQL 2012)

For Azure SQL Database, it would be best to use either the latest SQL Server Native Client driver or the newer stand-alone ODBC driver, ODBC Driver 13.1 for SQL Server (https://www.microsoft.com/en-us/download/details.aspx). Be sure the specify your user database name in the connection string regardless of the driver you use.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 6

Dear Grant, Dear Guzman,

Thank you again for your support.

The Customer accepted to upgrade to latest version (ODBC Driver 13.1) and the connection works ok now.

Thank you.

Best Regards,

ebartos