When you create an Access Web App in SharePoint Online, in the back-end a SQL Azure database is created.
From your Access Client, you can enable Read-Only or Read-Write connections to that SQL Azure database.
For this, you need to go to the File tab in the Access client and click on Manage and click “Enable Read-Only Connection” (3rd option):
To get the connection details (server name, user name and password) you must click on “View Read-Only Connection Information”, which shows you exactly what you need to connect form (e.g.) SQL Server Management Studio (SSMS):
So far, so good. But then the issues start, as I’ve noticed all around the internet.
When using this information in SSMS, we get the following error:
“Client with IP address ‘xxxxxxx’ is not allowed to access the server. To enable access, …”
This is not an unusual Azure error, I’ve seen similar errors like this before and they are all caused by the Azure firewall being enabled (which is a good thing, mind you).
As this is an “auto-hosted” app, the instance is managed by Microsoft and you cannot log on to it using the Azure portal.
What most forgot however, is that there are extra things to tick in the Access client:
Notice the “From My Location” and “From Any Location” menu items. Check whichever applies to you and retry SSMS. You may need to give it some time (with me, a few seconds do the trick).
Now, I can connect to the Azure DB from SSMS:
As mentioned by Raj in the comments, make sure you enter both server name and database name when trying to connect to the Azure database. We are talking about auto-hosted databases in Azure SQL Databases, provisioned by Microsoft. The (virtual cloud) server is provisioned by Microsoft and you have NO control over it (except its contents & structure from within Access). Hence it makes some kind of sense that you cannot connect to the server and just select any database. You must specify exactly which database you want to connect to.
I hope this helps anyone!