Creating an Azure SQL Database with ARM Template is a fairly common task. For that you'll have to provide an admin login and password via the parameters of the ARM template. This task can be done by a single developer or in an automated flow via Continuous Deployment for example.
While a single developer may need to access the database with an application such as SQL Server Management Studio and has the need to know the database password for testing purposes, you could ask yourself if it's relevant to save this password in an automated environment.
When a QA, Staging or Production environment is deployed who really as the need to access the database except your application? Does a person needs to access the database and know the password?
At the end when deploying to environments, you can ask yourself the following question:
Do you really need to know your database password?
Today we will discover how to automatically generate a password for an Azure SQL database via an Azure Resource Manager template. Our ARM template will be created in a new Azure Resource Group deployment project in Visual Studio.
Creation
Let's declare the parameters of the ARM template:
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"databaseCollation": {
"type": "string",
"defaultValue": "SQL_Latin1_General_CP1_CI_AS"
},
"databaseEdition": {
"type": "string",
"defaultValue": "Basic",
"allowedValues": [
"Basic",
"Standard",
"Premium"
]
},
"databaseMaxSizeBytes": {
"type": "string",
"defaultValue": "1073741824"
},
"databaseRequestedServiceObjectiveName": {
"type": "string",
"defaultValue": "Basic",
"allowedValues": [
"Basic",
"S0",
"S1",
"S2",
"P1",
"P2",
"P3"
],
"metadata": {
"description": "Describes the performance level for Edition"
}
}
}
...
}
Now we will declare the variables of the ARM template and here comes the interesting part:
{
...
"variables": {
"databaseName": "[concat('mydb', uniqueString(resourceGroup().id, 'E931CAC0-7259-4FA1-80B4-BD47CBA4E040'))]",
"sqlserverAdminLogin": "[concat('l', uniqueString(resourceGroup().id, '9A08DDB9-95A1-495F-9263-D89738ED4205'))]",
"sqlserverAdminPassword": "[concat('P', uniqueString(resourceGroup().id, '224F5A8B-51DB-46A3-A7C8-59B0DD584A41'), 'x', '!')]",
"sqlserverName": "[concat('mysqlserver', uniqueString(resourceGroup().id))]"
}
...
}We can pay attention to several things here:
And to finish we will declare the resources of the ARM template:
{
...
"resources": [
{
"apiVersion": "2014-04-01-preview",
"name": "[variables('sqlserverName')]",
"type": "Microsoft.Sql/servers",
"location": "[resourceGroup().location]",
"properties": {
"administratorLogin": "[variables('sqlserverAdminLogin')]",
"administratorLoginPassword": "[variables('sqlserverAdminPassword')]"
},
"tags": {
"displayName": "SqlServer"
},
"resources": [
{
"apiVersion": "2014-04-01-preview",
"name": "[variables('databaseName')]",
"type": "databases",
"location": "[resourceGroup().location]",
"dependsOn": [
"[concat('Microsoft.Sql/servers/', variables('sqlserverName'))]"
],
"properties": {
"edition": "[parameters('databaseEdition')]",
"collation": "[parameters('databaseCollation')]",
"maxSizeBytes": "[parameters('databaseMaxSizeBytes')]",
"requestedServiceObjectiveName": "[parameters('databaseRequestedServiceObjectiveName')]"
},
"tags": {
"displayName": "Database"
}
},
{
"apiVersion": "2014-04-01-preview",
"name": "AllowAllWindowsAzureIps",
"type": "firewallrules",
"location": "[resourceGroup().location]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', variables('sqlserverName'))]"
],
"properties": {
"startIpAddress": "0.0.0.0",
"endIpAddress": "0.0.0.0"
}
}
]
}
]
...
}Here nothing new as it is an usual way to declare a SQL Server and its database.
Example of use
The ARM template is now ready, let's open a Windows PowerShell and try it:
.\Deploy-AzureResourceGroup.ps1 -ResourceGroupName 'MyResourceGroupName' -ResourceGroupLocation 'canadaeast' -TemplateFile '.\azuredeploy.json'
...
Resource Microsoft.Sql/servers/databases 'mysqlserverlugjlio5wdu64/mydbz3uejht5n74jk' provisioning status is succeeded
If everything goes well, you should see the same kind of message as above.
To go further
If you get the database connection string it will look like the following:
Data Source=tcp:'mysqlserverlugjlio5wdu64.database.windows.net,1433;Initial Catalog=mydbz3uejht5n74jk;User Id=lbkxzpxo3blcg4g@mysqlserverlugjlio5wdu64;Password=P77qwhg25nihlox!;
As no user needs to know the password, you could enforce the security by changing the password on every deployment using the deployment name in the ARM template function uniqueString. Change the password variable as following:
"sqlserverAdminPassword": "[concat('P', uniqueString(resourceGroup().id, deployment().name, '224F5A8B-51DB-46A3-A7C8-59B0DD584A41'), 'x', '!')]"
Summary
We have seen how to create an ARM template that will create an Azure SQL database with an automatically generated password.
Now you have a database, but you can't access it as you don't know the generated password. But again, you don't to know this password while the web app you will deploy along with the database in the ARM template needs to know it. Well we will discover how together in my next article, stay tuned!
You can download the example solution here:
Or
Browse the GitHub repository
Please feel free to comment or contact me if you have any question about this article.
It is not really unique. If you have the arm template and you know which resourceGroup is used you can recreate the password that is created at deployment.
@Frank, absolutely. For something unique check the To go further section.
Any idea if this would work for Azure Devops IaaS deployment via a pipeline?
I use ARM templates to build the infrastructure and an Azure Pipeline in conjunction with Variable Groups.
@Frank @Vivien you could use the newGuid() function as a parameter to the ARM template. then generate a hash from it (with unique())
You are basically storing your password in template which is bad practice. Anyone with access to your resource group can get this password.
@Vivien: This means you are reconfiguring your admin password on every deployment. Additionally you are effectively storing your password in ARM template. This is not safe.
@Jigga: yes you are right, if someone has access to the ARM template and has the permissions to get the resource group id, it could potentially recreate the password.
@Jonathan: yes this would work.
@Frank: by adding the deployment name to the uniquestring function, as explained in the To go further section. yes the password will change on every deployment.