I have a hosted Azure SQL Server instance with an elastic pool that contains multiple databases. I want to do a cross database query between two of those databases. I understand that the only way to do this is with External tables (rather than linked servers). I want to know what firewall rules should be set in Azure Portal's SQL Server Firewall blade.
When the External table is configured and I try and query I get the below message:
Error retrieving data from {server}.{remotedb}. The underlying error message received was: 'Cannot open server '{server}' requested by the login. Client with IP address '{origindb}' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.'.
I can change the firewall rules in Azure Portal for the SQL Server instance. My options within the firewall blade are:
- "Allow Azure services and resources to access this server"
- Individual firewall rules
- Virtual networks
Option 1 ("Allow Azure services and resources to access this server")
This works however I am allowing any Azure item firewall access to my database. This kind of negates the point of a firewall as anyone wishing "unauthorised" access could simply create an Azure account, create a VM/SQL and bypass the firewall that way.
Option 2 (Individual firewall rules)
If I add the IP given in the error message then it works. However I don't know if that IP could change, I can't find a range of IP addresses and I don't know what other user's databases are on that IP.
I'm a bit in the dark with this IP address as I can't find any Azure documentation about their SQL IP range.
Option 3 (Virtual networks)
I don't think this really applies as it is for a virtual network to access your database server and I don't think I can put my hosted databases in a virtual network without adding them to a VM and managing them myself.
I've also tried using localhost and 127.0.0.1 as the location on the External Data Source when setting up the External table but these couldn't resolve.
Can anyone help?
