How can I allow unknown users to access my SQL (Azure) DB?
AzureAzure Sql-DatabaseAzure Problem Overview
Apparently one must explicitly specify (whitelist) the IP addresses that will be allowed access to a SQL Azure DB. I want, though, to have N-gazillion* users that will access these tables for data specific to them.
- delusions of grandeur/megalomania
Will these need to first provide me with their IP address so that I can add it as a valid entry, or is there a programmatic way to do it, or some other workaround?
Azure Solutions
Solution 1 - Azure
It would be advisable to have some sort of middle ware access the db and not your clients directly.
However if you want any IP to be able to connect to the db just add this entry to the firewall list:
Azure Portal -> Databases -> Servers -> Configure and add the following rule:
Solution 2 - Azure
How will your users be accessing the DB, via a Web App (front end) or directly (I assume you won't give users direct access to your DB?), if its via a Web App (presentation layer) then all you need todo is grant access to this IP address of the presentation layer/service layer (and if hosted in Azure its beside it).
SQL DB Azure has two types of access restrictions (more info here) "Windows Azure SQL Database Firewall"
- Server-level firewall rules:
- Database-level firewall rules
You could either open up all IP address 0.0.0.0 - 255.255.255.255 (not very secure) or come up with more finer grained policies based on the above Database firewall rules.
Solution 3 - Azure
The only way is to do it is via SQL query. Azure shows only Firewall Server rules to be visible only on the portal but on database level the only way is via SQL.
-- Enable Allconnections.
EXECUTE sp_set_database_firewall_rule N'Allow All', '0.0.0.0', '255.255.255.255';
Solution 4 - Azure
Login to azure Portal
select your database subscription
click on Tools
Now there is option 'Open in VisualStudio' (click on it)
You can see "Configure Firewall" click on it.
Add you new IP.
Done :)
Solution 5 - Azure
If you let them talk directly to your database (for example via SSMS) you need to enter their IP (or you can just whitelist the whole range). Usually they will use your database via your own API, then it's not needed to whitelist their IP addresses.