public static void Run([TimerTrigger("0 0 15 * * *")] TimerInfo myTimer, TraceWriter log) // 10:00AM eastern
{
log.Info($"{DateTime.Now} : Function starting...");
int sqlOffenses = 0;
string botToken = Environment.GetEnvironmentVariable("botToken");
string ruleChange = Environment.GetEnvironmentVariable("UpdateRules");
string whiteList = Environment.GetEnvironmentVariable("WhiteList");
if (string.IsNullOrEmpty(botToken))
{
log.Error($"{DateTime.Now} : One or more of the required app settings is missing, check the Azure portal to verify all parameters.");
return;
}
var slackClient = new SlackClient(new HttpClient(), botToken);
var slackPost = new SlackPost {
Channel = Environment.GetEnvironmentVariable("channel"), Text = "Azure SQL FW Auditor findings"
};
if (!bool.TryParse(ruleChange, out bool updateFwSetting))
{
updateFwSetting = false;
log.Info($"{DateTime.Now} : Unable to parse 'UpdateRules' setting {ruleChange}. Defaulting to False");
}
else
{
log.Info($"{DateTime.Now} : UpdateRules variable set to {updateFwSetting}");
}
AzureCredentialsFactory credFactorty = new AzureCredentialsFactory();
var msi = new MSILoginInformation(MSIResourceType.AppService);
var msiCred = credFactorty.FromMSI(msi, AzureEnvironment.AzureGlobalCloud);
var azureAuth = Azure.Configure()
.WithLogLevel(HttpLoggingDelegatingHandler.Level.BodyAndHeaders)
.Authenticate(msiCred);
var approvedRanges = string.IsNullOrEmpty(whiteList) ? ipRanges.ToList() : ipRanges.Union(whiteList.Split(',')).ToList();
log.Info($"{DateTime.Now} : Authenticated into tenant... Pulling subscriptions");
var fields = new List <SlackField>();
foreach (var sub in azureAuth.Subscriptions.List())
{
log.Verbose($"{DateTime.Now} : Logging into subscription : {sub.SubscriptionId.ToString()}");
var azure = Azure.Configure()
.WithLogLevel(HttpLoggingDelegatingHandler.Level.Basic)
.Authenticate(msiCred).WithSubscription(sub.SubscriptionId.ToString());
// loop through the sql servers in the subscription
foreach (var server in azure.SqlServers.List())
{
var outOfRangeRules = server.FirewallRules.List().Where(ruleDef => IsIpInRange(ruleDef.StartIPAddress, approvedRanges) == false || IsIpInRange(ruleDef.EndIPAddress, approvedRanges) == false);
// process all the rules that are deemed bad
foreach (var firewallRule in outOfRangeRules)
{
var field = new SlackField {
Short = false, Title = " ",
};
// The appsetting is set to true, so we try and delete the rule.
if (updateFwSetting)
{
try
{
firewallRule.Delete();
field.Value = $"Server - {server.Name}, Firewall Rule(s) : \r\n>{firewallRule.Name} : {firewallRule.StartIPAddress} - {firewallRule.EndIPAddress} *Deleted : YES*";
}
catch (Exception e)
{
field.Value = $"Server - {server.Name}, Firewall Rule(s) : \r\n>{firewallRule.Name} : {firewallRule.StartIPAddress} - {firewallRule.EndIPAddress} *Deleted : NO, encountered exception*";
log.Warning($"{DateTime.Now} : {e.Message}");
}
}
else
{
field.Value = $"{server.Name}, Firewall Rule(s) : \r\n>{firewallRule.Name} : {firewallRule.StartIPAddress} - {firewallRule.EndIPAddress} *Deleted : NO, deletion not enabled.*";
log.Info($"{DateTime.Now} : FW setting {updateFwSetting} firewall rule {firewallRule.Name} will be left to fester here");
}
sqlOffenses++;
}
// Once its clean we add in missing ranges
AddInMissingIpRanges(log, server);
}
}
if (fields.Any())
{
slackPost.Attachments = new List <SlackAttachment>()
{
new SlackAttachment {
Fields = fields, Color = "ok", Title = " ",
}
};
_ = slackClient.PostToSlackAsync(slackPost);
}
}
