We’re all aware of the importance of having strong passwords and changing them from time to time. Recently I made an effort to automate the process of updating my SQL Azure password using PowerShell. Now, as always I welcome suggestions, input and feedback on how you’re doing the same thing. I want to make clear this is just one way of accomplishing the goal of updating a password. Yes, you can use the Azure portal, Azure CLI, Azure PowerShell console, Azure Key Vault, set up inherited trust between Azure Apps and SQL, etc. There are lots of options. In my case, I like to generate my own strong passwords and update them manually. I feel like I have more control this way – but again, this is just my preference.

Backstory: In my tenant I have one SQL server and many Azure websites running as App services. I also have an Azure Function app with several scripts running on demand. Two of the scripts and all of the app services use configurations that require the password be updated. I use Entity Framework in my web apps and scripts, so I like keeping the connection string as a configuration option in my app services so I don’t have to store sensitive information either on my machine or when checking my code in. This script will not only change the SQL Azure password, it will update all of the app service configurations and the affected Azure Function App scripts.

The good stuff: The Azure SQL commands look like this

Connect-AzAccount -Tenant {your-tenant-guid-without-braces}
$rg = "resource-group-where-your-SQL-server-lives"
$server = "Azure-SQL-server-name"
$SecurePwd = ConvertTo-SecureString "strong-password" -AsPlainText -Force
Set-AzSqlServer -ResourceGroupName $rg -ServerName $server -SqlAdministratorPassword $SecurePwd

Just provide the resource group name where the SQL server is, the plain-text strong password and the name of the Azure SQL server. That’s it!

Now we need to update all affected Azure Apps and the Azure Function App scripts. Let’s start with an Azure App. In some cases I have more than one connection string, so I have to make sure the Hashtable used in the script block below takes into account multiple entries

$connectionStrings =  @{
    DefaultConnection = @{Type='SqlServer';Value='Data Source=Azure-SQL-FQDN;Initial Catalog=databasename;User ID=account;Password=$StrongPassword'}
    modelEntities = @{Type='Custom';Value='metadata=res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl;provider=System.Data.SqlClient;provider connection string="data source=Azure-SQL-FQDN;initial catalog=databasename;persist security info=True;user id=account;password=$StrongPassword;MultipleActiveResultSets=True;App=EntityFramework"'}
    }
Set-AzWebApp -Name 'azure-app-name' -ResourceGroupName 'resource-group-name' -ConnectionStrings $connectionStrings

Now you just need to repeat that once for each azure app! For the Azure Function app a little more is required. We will need to use the Kudu API (built into Azure) to download the script file from the remote server, update it, then re-upload it back to the same location. Let’s start with getting the publishing credentials for the Azure Function App

$resource = Invoke-AzResourceAction -ResourceGroupName resource-group-name -ResourceType Microsoft.Web/sites/config -ResourceName "AzureFunctionAppName/publishingcredentials" -Action list -ApiVersion 2018-02-01 -Force
$username = $resource.Properties.publishingUserName
$password = $resource.Properties.publishingPassword
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $username, $password)))

These publishing credentials can be reused across all functions inside of the same Azure Function app. Once obtained we create a Base64 string of the username and password, so it can be used in subsequent API calls for authentication.

Next we make the GET call to retrieve the run.ps1 script.

$userAgent = "powershell/1.0"
$url = "https://AzureFunctionAppName.scm.azurewebsites.net/api/vfs/site/wwwroot/FunctionScriptName/run.ps1"
$filePath = "C:\Users\{account}\Desktop\run.ps1"
$headers = @{
    'Authorization' = 'Basic ' + $base64AuthInfo
}
Invoke-WebRequest $url -OutFile c:\users\{account}\desktop\run.ps1 -Headers $headers

This is the default script name when using functions inside of an Azure Function app (if using PowerShell). The base uri can also be obtained from the Azure Function App Overview blade in Azure portal. The fragment after (/api/vfs/site/wwwroot/) will always be the same. Follow that up with the underlying function name and /run.ps1 and you will always have a solid URL you can use for API calls!

Once the file is local, we use PowerShell to update the connectionstring with the new info and save the file, overwriting what was downloaded

$ScriptContent = Get-Content $filePath
$connectionstringLine = $ScriptContent | Select-String -pattern "Password"
$newConnString = "$db.ConnectionString = 'Server=tcp:Azure-SQL-FQDN,1433;Database=databasename;User ID=account;Password=$StrongPassword;'"
$scriptModified = $ScriptContent.Replace($connectionstringLine, $newConnString)
$scriptModified | Set-Content -Path $filepath

Finally we upload it back to the remote site using the PUT method

$headers = @{
    'Authorization' = 'Basic ' + $base64AuthInfo
    'If-Match' = '*'
}
Invoke-RestMethod -Uri $url -Headers $headers -UserAgent $userAgent -Method PUT -InFile $filePath -ContentType "multipart/form-data"
Remove-Item $filePath -Force

I followed it with Remove-Item because I wanted to make sure subsequent runs against other scripts and Azure Function Apps were not using old files.

And there you have it! The entire script is below, let me know what you think or if you have another way you prefer that works also!

Connect-AzAccount -Tenant {your-tenant-guid-without-braces}
$rg = "resource-group-where-your-SQL-server-lives"
$server = "Azure-SQL-server-name"
$SecurePwd = ConvertTo-SecureString "strong-password" -AsPlainText -Force
Set-AzSqlServer -ResourceGroupName $rg -ServerName $server -SqlAdministratorPassword $SecurePwd
# Update Azure App service web app configuration - repeat for each affected Azure App
$connectionStrings =  @{
    DefaultConnection = @{Type='SqlServer';Value='Data Source=Azure-SQL-FQDN;Initial Catalog=databasename;User ID=account;Password=$StrongPassword'}
    modelEntities = @{Type='Custom';Value='metadata=res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl;provider=System.Data.SqlClient;provider connection string="data source=Azure-SQL-FQDN;initial catalog=databasename;persist security info=True;user id=account;password=$StrongPassword;MultipleActiveResultSets=True;App=EntityFramework"'}
    }
Set-AzWebApp -Name 'azure-app-name' -ResourceGroupName 'resource-group-name' -ConnectionStrings $connectionStrings
# Update Azure Function app script - repeat for each affected Azure Function app script
$resource = Invoke-AzResourceAction -ResourceGroupName resource-group-name -ResourceType Microsoft.Web/sites/config -ResourceName "AzureFunctionAppName/publishingcredentials" -Action list -ApiVersion 2018-02-01 -Force
$username = $resource.Properties.publishingUserName
$password = $resource.Properties.publishingPassword
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $username, $password)))
$userAgent = "powershell/1.0"
$url = "https://AzureFunctionAppName.scm.azurewebsites.net/api/vfs/site/wwwroot/FunctionScriptName/run.ps1"
$filePath = "C:\Users\{account}\Desktop\run.ps1"
$headers = @{
    'Authorization' = 'Basic ' + $base64AuthInfo
}
Invoke-WebRequest $url -OutFile c:\users\{account}\desktop\run.ps1 -Headers $headers
$ScriptContent = Get-Content $filePath
$connectionstringLine = $ScriptContent | Select-String -pattern "Password"
$newConnString = "$db.ConnectionString = 'Server=tcp:Azure-SQL-FQDN,1433;Database=databasename;User ID=account;Password=$StrongPassword;'"
$scriptModified = $ScriptContent.Replace($connectionstringLine, $newConnString)
$scriptModified | Set-Content -Path $filepath
$headers = @{
    'Authorization' = 'Basic ' + $base64AuthInfo
    'If-Match' = '*'
}
Invoke-RestMethod -Uri $url -Headers $headers -UserAgent $userAgent -Method PUT -InFile $filePath -ContentType "multipart/form-data"
Remove-Item $filePath -Force