Nettoyage de printemps et Azure SQL (ou comment prendre soin de vos accès à vos bases de données)

Le télétravail, c’est bien ! Ça rend les gens heureux ! Seulement voilà, le bonheur a un prix…

Ici, nous exprimons le coût du bonheur dans une monnaie bien particulière : le FRC (Firewall Rule Coin).

Lorsque les développeurs et l’équipe IT travaillent d’où ils veulent, souvent, ils bougent. Lorsqu’ils bougent, ils se connectent à leurs serveurs depuis des endroits différents et lorsqu’ils se connectent d’endroits différents…leurs adresses IP changent.

Si vous utilisez Azure SQL, le filtrage d’accès par adresses IP est mis en place par défaut pour vos serveurs de bases de données. C’est une bonne chose ! Du coup, à chaque fois qu’un membre de l’équipe se connecte depuis son nouvel hôtel ou nouveau chalet à la montagne, il utilise le portail pour s’autoriser l’accès au serveur SQL.

Dans ces conditions, une équipe de 4 développeurs arrive très vite à cette situation :

Ce qui est marrant ici, c’est la taille de la scrollbar…  Bref, il est important ici de noter qu’avoir une liste de 50 adresses IP ne limite pas forcément l’accès à 50 PC. Bien souvent, on ajoute l’adresse d’un réseau (le wifi de l’hôtel) et toute personne (attaquants inclus) se connectant à Internet via ce réseau peut maintenant faire joujou avec notre serveur de base de données. La liste de 50 IP permet l’accès à 50 x n PC, “n” n’étant pas ici une variable mais une inconnue (c’est pire ^^).

Que le ménage commence…

Un beau jour de printemps donc, vous décidez de faire le ménage. Mais bon, en bon développeur-fan-de-clickodromes que vous êtes, vous vous rendez compte que la tâche se révèle fastidieuse.

Et oui, avec le clickodrôme, impossible de faire de la sélection multiple pour la suppression. Non, non ! Il faut pour chaque règle cliquer sur le menu contextuel, supprimer (répéter autant de fois que vous avez de règles à supprimer) et enfin sauvegarder le tout*.

Il y a heureusement un moyen de faire ça un peu plus intelligemment…

Nous allons voir 2 méthodes:

• La suppression des règles en SQL, pour le plus grand bonheur de nos DBA.
• La suppression des règles en PowerShell, pour le plus grand bonheur de nos administrateurs Azure.

* Il y a encore peu de temps de ça, il était nécessaire de sauvegarder après chaque suppression. Le portal va donc dans le bon sens, et on verra sans doute la multi-sélection apparaître prochainement.

Manipulation des règles de pare-feu Azure SQL avec SQL

Les règles de pare-feu Azure SQL sont visibles depuis la vue (en lecture seule) suivante :

sys.firewall_rules

Les gourous du SQL l’auront compris, on peut aussi lister les règles 😊

Maintenant, la consultation c’est bien, mais la suppression dans notre cas, c’est mieux. Et comme sys.firewall_rules n’est qu’une vue en lecture seule, impossible de faire une simple suppression.

Heureusement, les gens de MS ont pensé à tout (à part permettre la sélection multiple dans le portail) en mettant à notre disposition une procédure stockée qui va bien.

sp_delete_database_firewall_rule [@name =] [N]'name'

Limitation de taille cependant : avec cette procédure stockée, on ne peut supprimer qu’une seule règle par appel (Une impression de déjà-vue ?).

On va donc maintenant démontrer notre magnificence en TSQL en créant un script qui va faire le boulot

On exécute, et la magie opère. Les règles sont maintenant mises à jour, mais les changements ne sont pas effectifs immédiatement (c’est à cause du méchant cache, comme d’habitude). On peut y remédier à l’aide de la commande suivante

DBCC FLUSHAUTHCACHE [ ; ]

Ce n’est pas l’objet de ce billet, mais vous pouvez aussi bien évidemment ajouter des règles au pare-feu en utilisant la procédure stockée sp_set_firewall_rule.

sp_set_firewall_rule [@name = ] 'name',
[@start_ip_address =] 'start_ip_address',
[@end_ip_address =] 'end_ip_address'
[ ; ]

Vous trouverez la liste des outils de manipulation des règles de pare-feu ici.

Petite précision : ces commandes sont à exécuter sur la base master…et seul le login d’administration du serveur Azure SQL a les privilèges nécessaires pour le faire.

Manipulation des règles de pare-feu Azure SQL avec Powershell

On va commencer au tout début pour ceux qui sont moins familiers avec PowerShell.

Dans un premier temps, on va définir nos variables qui vont contenir toutes les informations dont nous aurons besoin pour manipuler les règles de firewall (nom de la souscription et du groupe de ressources, nom du serveur SQL et liste de noms des règles à exclure de la suppression)

$subscriptionName = "MySubscription"
$resourceGroupName = "MyResourceGroup.with.clean.firewall.rules"
$serverName = "clean-rules-sql1"
$permanentRules = @("AllowAllWindowsAzureIps", "Mon_IP_a_ne_pas_virer")

Login-AzureRmAccount
Select-AzureRmSubscription -SubscriptionName $subscriptionName

$rules = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName

Enfin on supprime les règles indésirables à l’aide d’une belle boucle :

foreach($rule in $rules) {
    if($permanentRules -notcontains $rule.FirewallRuleName) {
        # vv N'utilisez pas de saut de ligne en vrai. C'est juste pour faire beau vv
        Remove-AzureRmSqlServerFirewallRule -FirewallRuleName $rule.FirewallRuleName
                                            -ResourceGroupName $resourceGroupName
                                            -ServerName $serverName
    }
}

Notez que là encore, les droits de votre compte doivent être suffisants pour permettre la suppression des règles de firewall. Si vous êtes Owner, Contributor, SQL Server Contributor ou encore SQL Security Manager pour la souscription Azure donc, pas de problème. En revanche, si vous n’êtes que Reader ou SQL DB Contributor, le script ne fonctionnera pas.

Quelle méthode choisir ?

Plus qu’une simple histoire de préférence personnelle, le choix peut être déterminé par des raisons purement pratiques. Il se peut que vous vous trouviez dans la situation ou vous disposez du compte administrateur du serveur SQL, sans avoir de droit d’administration sur Azure. Dans ce cas, vous pouvez vous servir des commandes SQL.

Si à l’inverse, vous ne connaissez pas ce compte mais que vous avez des droits nécessaires sur Azure, vous utiliserez PowerShell.

Quelque soit la méthode, pensez à effectuer cette opération chaque fois que nécessaire. Vos serveurs Azure SQL n’en seront que mieux protégés.