Caveat: Ok, so this might not be necessary, but my standard security mentality is to make things as hard as possible, and then ease up as required.
Right now, I'm the sole developer for a software system that is based in Azure (SQL Server and Mobile Service).
Although the entire system is designed to access its SQL Server databases using the Azure Mobile Service API, I'm using SSMS to administer the database during development and testing, and I've had to create a Firewall rule to allow this access. If this project expands (which looks likely in the next few months), I'll need to give other DB admins/developers access to some of the same resources. That's fine, but given the sensitive nature of some of the data we will be housing, I'd like to automatically invalidate those Azure SQL Server firewall rules at the end of the day. I realize that this will require their re-creation at the start of each business day. I am ok with this.
From what I've read, there appear to be 3 different ways to manage Azure SQL Server firewall rules programmatically:
- T-SQL (
sp_delete_firewall_rule) - REST API (
DELETEtohttps://management.core...) - Azure Powershell (
Remove-AzureSqlDatabaseServerFirewallRule)
I don't really care how it gets done, but I'm already using a Mobile Service Scheduler job to expire password reset tokens (totally unrelated to the current task), so I thought I would give that technique a shot.
Here was that attempt:
function ClearSqlServerFirewalls() {
var sql = 'Exec sp_delete_firewall_rule ?';
mssql.query(sql, ['name'], {
success: function (results) {
console.log(results);
}
});
}
In a not-terribly-surprising outcome, I got the following error:
User must be in the master database.
Ok, so that makes good sense, given that this is a system-wide effect, not a database-specific one. Azure apparently creates a specific SQL user for each database and runs all Mobile Service code through that user. Since that user doesn't have database-wide access, no dice.
So, here are my current thoughts on this:
- SQL Agent apparently doesn't exist for SQL Azure, so automating the T-SQL script from within SQL Server appears to be out.
- I don't yet know of a way to automate the REST API call from within Azure itself.
- I am not familiar with Powershell in Azure.
Is there any way to do what I want to do? Giving the Azure user access to the master database (if even possible) seems like a bad solution.
Yes, I realize this may seem like overkill, but if I have the right credentials, I can add an IP rule for just about anywhere. Say I'm on a working lunch and need access. I add the rule and get to work. But say I forget about that rule, it could potentially just sit out there as an open invitation to someone who gets on that same network. I realize that this is slightly paranoid, but it just also seems logical to me that a firewall rule would be able to come with a built-in expiration. Am I missing something?
