Azure Function and PowerShell: unable to set connection strings with Set-AzWebApp

Question

For deployment purposes I created a PowerShell script to set App Settings. This works fine via

$currentAppSettings = $app.SiteConfig.AppSettings

$appSettings = @{}
# Add existing App Settings
ForEach ($currentAppSetting in $currentAppSettings) {
    $appSettings[$currentAppSetting.Name] = $currentAppSetting.Value
}

# Add new App Settings
$appSettings["someKey"] = "someValue"

# Update App Settings
Set-AzWebApp -ResourceGroupName $resourceGroupName -Name $appName -AppSettings $appSettings

As I am using now Entity Framework I also need to set the Connection Strings. First I thought this should work like with App Settings and just adding the ConnectionStrings Argument:

$currentConnectionStrings = $app.SiteConfig.ConnectionStrings

$connectionStrings = @{}

ForEach ($currentConnectionString in $currentConnectionStrings) {
    $connectionStrings[$currentConnectionString.Name] = $currentConnectionString.ConnectionString
}

$connectionStrings["someKey"] = "someValue"

Set-AzWebApp -ResourceGroupName $resourceGroupName -Name $appName -ConnectionStrings $connectionStrings

But this fails with the error

Set-AzWebApp : Cannot validate argument on parameter 'ConnectionStrings'. Connection string type value pair must be of type 'System.Collections.Hashtable'

despite the fact that $connectionStrings is of type System.Collections.Hashtable

Also trying to work with Custom Objects, arrays and so on failed.

How do I pass the connection strings correctly? And how do I specify the Type (e.g. SQLAZURE)?

Thanks

For your second question, apparently you can set the type to SQL Azure by using Type 2. Have a look at the code in this answer, especially where they do `connectionString.Add(..)` https://stackoverflow.com/a/41219876/2102114 — S Raghav, Apr 03 '19 at 05:45

score 5 · Answer 1 · answered Apr 03 '19 at 06:09

As per the Documentation of Set-AzwebApp ConnectionStrings should be Hastable.

Set-AzWebApp
   [[-AppServicePlan] <String>]
   [[-DefaultDocuments] <String[]>]
   [[-NetFrameworkVersion] <String>]
   [[-PhpVersion] <String>]
   [[-RequestTracingEnabled] <Boolean>]
   [[-HttpLoggingEnabled] <Boolean>]
   [[-DetailedErrorLoggingEnabled] <Boolean>]
   [[-AppSettings] <Hashtable>]
   [[-ConnectionStrings] <Hashtable>]
   [[-HandlerMappings] <System.Collections.Generic.IList`1[Microsoft.Azure.Management.WebSites.Models.HandlerMapping]>]
   [[-ManagedPipelineMode] <String>]
   [[-WebSocketsEnabled] <Boolean>]
   [[-Use32BitWorkerProcess] <Boolean>]
   [[-AutoSwapSlotName] <String>]
   [-ContainerImageName <String>]
   [-ContainerRegistryUrl <String>]
   [-ContainerRegistryUser <String>]
   [-ContainerRegistryPassword <SecureString>]
   [-EnableContainerContinuousDeployment <Boolean>]
   [-HostNames <String[]>]
   [-NumberOfWorkers <Int32>]
   [-AsJob]
   [-AssignIdentity <Boolean>]
   [-HttpsOnly <Boolean>]
   [-AzureStoragePath <WebAppAzureStoragePath[]>]
   [-ResourceGroupName] <String>
   [-Name] <String>
   [-DefaultProfile <IAzureContextContainer>]
   [<CommonParameters>]

You should set connectionstring using a Hashtable as shown below:

$connectionStrings = @{connectionStrings = @{Name="<ConnectionStringName>";Value="<ConnectionSyring>";type="<SQLAzure/SQLServer/Custom/MySql>"}}

Set-AzWebApp -ResourceGroupName $resourceGroupName -Name $appName -ConnectionStrings $connectionStrings

quervernetzt · Accepted Answer · 2019-04-05T06:14:44.693

@KetanChawda-MSFT: Thanks for your advice, it helped me a lot to develop the solution (but did not work as such in my context as mentioned below).

Here is the way it works for me now:

$currentConnectionStrings = $app.SiteConfig.ConnectionStrings
$connectionStrings = @{}

# Add existing connection strings
ForEach ($currentConnectionString in $currentConnectionStrings) {
    $connectionStrings[$currentConnectionString.Name] =  
        @{
            Value=$currentConnectionString.ConnectionString;
            Type=($currentConnectionString.Type | Out-String).ToUpper()
        }
}

# Add database connection string
$connectionStrings["connName"] = @{
    Value="connString";
    Type="SQLAZURE"
}

# Update settings
Set-AzWebApp -ResourceGroupName $resourceGroupName -Name $appName -ConnectionStrings $connectionStrings

What is interesting is that in case of existing connection strings I have to format it as string and use toUpper, otherwise it does not work for me (Error: Set-AzWebApp : Cannot validate argument on parameter 'ConnectionStrings'. Connection string type must be specified.). When adding a new connection string I can use e.g. SQLAZURE as well as SQLAzure.

EDIT:

Here is an approach where you don't need the connection strings section at all when working with Entity Framework and Azure Functions: Missing ProviderName when debugging AzureFunction as well as deploying azure function

@quervernetzt, I dont see any difference as such with the mine. anyways, I am glad that helped you to get your solution. — Ketan, Apr 03 '19 at 09:04
@KetanChawda-MSFT: your answer gives general guidance but is as such no solution to my problem. And if you compare the code in your and my answer there are some differences (e.g. how the entries in the hash table are created, to handle already existing connection strings, to deal with issues related to existing connection strings and so on). But still your advice guided me on the right track. — quervernetzt, Apr 03 '19 at 11:18

Azure Function and PowerShell: unable to set connection strings with Set-AzWebApp

2 Answers2