Connect to your Azure SQL DB behind MsAccess web application

Background:

When you create new Custom web application from Ms Access on your Office 365 SharePoint your application will be hosted on Microsoft Cloud.

Sometimes you can have more value if you can gain direct access you your Azure SQL DB database.

This article explain how to configure and resolve connection problems to the back-end Azure SQL DB.

Figure 1. Create new Access Custom Web App.

For database serviced the application will use Azure SQL DB as the backend database.

As every Azure SQL DB you can use SSMS (Sql Server Management Studio) to connect and query your database.

But, since this database hosted in Microsoft's subscription you will not be able to manage this database with Azure Portal.

In this article, I want to show how to manage access to this database.

How to manage database access via MsAccess?

To manage access to database please open you web app with your MsAccess client and then click on File.

Then click on Manage near Connections.

Figure 2. Manage database connection properties.

Choose the right access method:

On this properties panel you can choose whether to enable either Read-Only or Read-Write access.

Get connection information:

After you choose the right access permissions, you can get the connection properties by clicking the "View Read-Only Connection Information" or "View Read-Write Connection Information"

Figure 3. get connection details

Reset connection password:

You can also use the "Reset … connection password" option to change the password.

Note: You cannot set your own password, it will be set automatically by the system.

Configure firewall access:

to set firewall access choose the appropriate option

Figure 4. set firewall access

Choose "From My Location" to enable access from you current public facing IP address or "From Any Location" to allow the connection from any IP address

Note: for organizations, internet connection uses specific public IP to hide the whole organizational network. Using the "From My Location" will enable every host within the organization network to access this database

Note: you cannot use T-SQL sp_set_database_firewall_rule or any other method except the above method to set firewall rules because you do not have the database rights to do so.

Troubleshooting:

Error 1 - when connecting with SSMS you may see the "new firewall rule" message

Figure 5. new firewall rule dialog box

Do not attempt to use your Azure subscription admin account as this database hosted in Microsoft internal subscription that your account cannot manage.

To resolve this problem read the above content.

Error 2 – the server you specified does not exists in any subscription in your account.

Figure 6. cannot add firewall rule error message.

if you did not read the previous error and did try to use your Azure subscription admin account you will face this error

To resolve this problem read the above content.

Error 3 – cannot open server requested by the login

Figure 7. cannot open server requested by the login

This error means that you are not allowed by the firewall rules to connect, use the above content to learn how to setup the firewall to allow access to your backend database.

This can also happen if you did not specify explicitly the database name. click here to read how to set the database name when connecting with SSMS.

This also apply when configuring ODBC connection, if database name is not explicitly used you will not be able to connect.

I hope you find this article helpful to help you connect to your back-end Azure SQL Database behind MsAccess custom web app.