Can not Build/Publish Visual Studio Database project Targeting Azure Sql Database v12

Recently, I was working on a visual studio Database project and trying to publish to Azure SQL. But there is no option for selecting Azure SQL Database V12 in Target platform Database dropdown.  Microsoft Azure SQL Database is the only option.

In order to fix that issue just open the database project file(.sqlproj) in the XML editor and manually update the DSP tag with the following.

<DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>
That’s it. Now we should be able to publish to Azure SQL Database.
Happy Coding 🙂

Azure Web App – Request Timeout Issue- 500 Error

Recently I was facing a issue with request time out in a web app in azure app services. It was a synchronous file upload which take more than 4 seconds. (Yes, off-course, synchronous way is not the optimum solution)

I investigated this issue and I found that Azure App services (Web app) has default 230 seconds of timeout. If a request take more than this time it will be a 500 Error. But still this request is allowed to continue in the background in server.

So we should keep this in mind and we should design our applications in a reactive way.

So if you are getting a request timeout in Azure web app this could be the issue.

More readings:

https://feedback.azure.com/forums/169385-web-apps/suggestions/36572656-make-web-app-timeout-of-230-seconds-configurable

https://social.msdn.microsoft.com/Forums/azure/en-US/560dc2a9-43e1-4c68-830c-6e1defe2f72d/azure-web-app-request-timeout-issue?forum=WAVirtualMachinesforWindows

https://docs.microsoft.com/en-us/azure/app-service/faq-availability-performance-application-issues#why-does-my-request-time-out-after-230-seconds

https://www.edureka.co/community/22010/azure-asp-net-webapp-the-request-timed-out

Deploy storage account and output connection string with SAS token using ARM template

I found my self in a situation where I needed to deploy Azure storage account with a blob container and generate connection string with SAS token and update one of the web app’s settings with generated connection strings.

For this purpose, I used linked ARM template and created Storage account and blob container and generated the connection string with SAS token and output from that template so that master template can use this value.

Table of content

  1. Craft ARM Template
  2. Output connection string with SAS token
  3. Output connection string with account key
  4. Deploy Template

Craft arm template

We need to Craft ARM template as below for our requirement.

{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"variables": {
"storageAccountApiVersion": "2018-07-01",
"storageAccountNameTidy": "[toLower(trim(parameters('storageAccountName')))]",
"blobEndPoint":"[concat('https://&#39;,variables('storageAccountNameTidy'),'.blob.core.windows.net/')]"
},
"parameters": {
"location": {
"type": "string",
"defaultValue": "southeastasia"
},
"storageAccountName": {
"type": "string",
"defaultValue": "awesomestorage"
},
"accountType": {
"type": "string",
"defaultValue": "Standard_LRS"
},
"accessTier": {
"type": "string",
"defaultValue": "Hot"
},
"supportsHttpsTrafficOnly": {
"type": "bool",
"defaultValue": true
},
"sasTokenExpiry": {
"type": "string",
"defaultValue": "2020-12-31T23:59:00Z"
},
"containerName": {
"type": "string",
"defaultValue": "test"
},
"accountSasProperties": {
"type": "object",
"defaultValue": {
"signedServices": "b",
"signedPermission": "rl",
"signedResourceTypes": "sco",
"keyToSign": "key2",
"signedExpiry": "[parameters('sasTokenExpiry')]"
}
}
},
"resources": [
{
"name": "[parameters('storageAccountName')]",
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "[variables('storageAccountApiVersion')]",
"location": "[parameters('location')]",
"properties": {
"accessTier": "[parameters('accessTier')]",
"supportsHttpsTrafficOnly": "[parameters('supportsHttpsTrafficOnly')]"
},
"dependsOn": [],
"sku": {
"name": "[parameters('accountType')]"
},
"kind": "BlobStorage",
"resources": [
{
"name": "[concat('default/', parameters('containerName'))]",
"type": "blobServices/containers",
"apiVersion": "[variables('storageAccountApiVersion')]",
"dependsOn": [
"[parameters('storageAccountName')]"
]
}
]
}
],
"outputs": {
"storageAccountConnectionString": {
"type": "string",
"value": "[concat('DefaultEndpointsProtocol=https;AccountName=', variables('storageAccountNameTidy'), ';AccountKey=', listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountNameTidy')), variables('storageAccountApiVersion')).keys[0].value)]"
},
"storageAccountConnectionStringWithSAS": {
"type": "string",
"value": "[concat('BlobEndpoint=',variables('blobEndPoint'),';SharedAccessSignature=', listAccountSas(variables('storageAccountNameTidy'), variables('storageAccountApiVersion'), parameters('accountSasProperties')).accountSasToken)]"
}
}
}

 

generate connection string with sas token

As per the above full ARM Template, we can see connection string is generated with full access and another is generated with SAS token.

In order to generate a connection string with SAS token, I have used listAccountSas ARM function.

Find More details on this function here

"storageAccountConnectionStringWithSAS": { "type": "string", "value": "[concat('BlobEndpoint=',variables('blobEndPoint'),';SharedAccessSignature=', listAccountSas(variables('storageAccountNameTidy'), variables('storageAccountApiVersion'), parameters('accountSasProperties')).accountSasToken)]" }

We need to pass three parameters for this function

  • resourceIdenifier

The name of the storage account within the specified resource group

  • apiVersion

The API version to use for this operation

  • requestParameters

We need to pass parameters as specified here

"accountSasProperties": {
            "type": "object",
            "defaultValue": {
                "signedServices": "b",
                "signedPermission": "rl",
                "signedResourceTypes": "sco",
                "keyToSign": "key2",
                "signedExpiry": "[parameters('sasTokenExpiry')]"
            }
        }

We can find more details about parameters specified herein above Microsoft documentation.

Generate connection string with storage account key

We can generate connection string which has full access to storage account with Storage account access keys.  We can use listKeys ARM function for this.

We can find more details on this function here.

We need to pass two parameters for this function

  • Storage account resource id
  • API version

This function gives you all keys in the storage account and we can select one key to create connection string as below.

 


listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountNameTidy')), variables('storageAccountApiVersion')).keys[0].value

Deploy ARM template

We can use the following PowerShell script to deploy ARM template.

Note: Fill out the required parameters. (denotes in caipatal)


$password = "SECRET"
$clientId = "CLIENTID"
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential ($clientId, $securePassword)
Login-AzureRmAccount -ServicePrincipal -TenantId "TENANTID" -SubscriptionId "SUBSCRIPTIONID" -Credential $credentials 


$templateFilePath = "ARM TEMPLATE PATH"

$resourceGroupName = "RESOURCEGROUPNAME"
$resourceGroupLocation = "LOCATION"
$deploymentName = "DEPLOYMENTNAME"

#Create or check for existing resource group
$resourceGroup = Get-AzureRmResourceGroup -Name $resourceGroupName -ErrorAction SilentlyContinue
if(!$resourceGroup)
{
    Write-Host "Resource group '$resourceGroupName' does not exist. To create a new resource group, please enter a location.";
    if(!$resourceGroupLocation) {
        $resourceGroupLocation = Read-Host "resourceGroupLocation";
    }
    Write-Host "Creating resource group '$resourceGroupName' in location '$resourceGroupLocation'";
    New-AzureRmResourceGroup -Name $resourceGroupName -Location $resourceGroupLocation
}
else{
    Write-Host "Using existing resource group '$resourceGroupName'";
}


# Start the deployment
Write-Host "Starting deployment...";
New-AzureRmResourceGroupDeployment -ResourceGroupName $resourceGroupName -Name $deploymentName -TemplateFile $templateFilePath;

We can see the following output once deployment is successful.

So this is how we deploy storage account and generate connection strings. 🙂

Custom functions in Azure ARM Templates

Azure provides a helpful number of functions which can be used in ARM templates. It makes our life easier.

We can see the complete list of Azure ARM function here

Apart from that in some situations, you may find your self where you need to implement custom function inside ARM templates. So we can reuse it. So it is DRY.

Typically, we use complicated logic inside the function that we don’t want to duplicate in the ARM template.

So is it possible in ARM template? Yes, ARM templates give us the opportunity to implement custom functions. 🙂

Keep in mind. There are some restrictions when we use functions as below.

  • The function can’t access variables.
  • The function can only use parameters that are defined in the function. When you use the parameters function within a user-defined function, you’re restricted to the parameters for that function.
  • The function can’t call other user-defined functions.
  • The function can’t use the reference function.
  • Parameters for the function can’t have default values

The custom function should be declared inside functions property in an ARM template.

Following is a sample function which accepts the container name as a parameter and appends resource group location to container name.

{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"functions": [
{
"namespace": "rt",
"members": {
"containerNameWithLocation": {
"parameters": [
{
"name": "containerName",
"type": "string"
}
],
"output": {
"type": "string",
"value": "[concat(parameters('containerName'), resourceGroup().location)]"
}
}
}
}
],
"parameters":{
"containerName": {
"type": "string",
"defaultValue": "blobcontainer"
}
}
}

Note that parameters used in scope of function should be declared in parameters section in ARM template.

Your functions require a namespace value to avoid naming conflicts with template functions. In above function we have used rt as namespace.

 

Then we can call the function as below

rt.containerNameWithLocation(parameters(‘containerName’))

Following snippet shows actual usage of this function.

"resources": [
{
"name": "[parameters('storageAccountName')]",
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "[variables('storageAccountApiVersion')]",
"location": "[parameters('location')]",
"properties": {
"accessTier": "[parameters('accessTier')]",
"supportsHttpsTrafficOnly": "[parameters('supportsHttpsTrafficOnly')]"
},
"dependsOn": [],
"sku": {
"name": "[parameters('accountType')]"
},
"kind": "BlobStorage",
"resources": [
{
"name": "[concat('default/', rt.containerNameWithLocation(parameters('containerName')))]",
"type": "blobServices/containers",
"apiVersion": "[variables('storageAccountApiVersion')]",
"dependsOn": [
"[parameters('storageAccountName')]"
]
}
]
}
],

 

So enjoy writing custom functions in ARM Template 🙂

Create Service Principal using Powershell and Login to Azure with Powershell

What is Service principle?

Service principle are non-interactive Azure accounts.  Applications use Azure services should always have restricted permissions.  Azure offers Service principals allow applications to login with restricted permission Instead having full privilege in non-interactive way.

Using Service Principal we can control which resources can be accessed.

For Security reason, it’s always recommended to use service principal with automated tools rather than allowing them to log in with user identity

Create a Service Principal with PowerShell. 

Note: For this demo we are using Azure RM PowerShell module. Azure has introduced new PowerShell module called AZ. Create AD App

Create AD app

#Create Service principal
New-AzureRmADServicePrincipal ApplicationId $app.ApplicationId `
DisplayName $dummyUrl `
Password $securePassword `
Scope "/subscriptions/<SUBSCRIPTION ID>" `
Role Contributor `
StartDate ([datetime]::Now) `
EndDate $([datetime]::now.AddYears(1)) Verbose

Create a Service Principal

#Create Service principal
New-AzureRmADServicePrincipal ApplicationId $app.ApplicationId `
DisplayName $dummyUrl `
Password $securePassword `
Scope "/subscriptions/<SUBSCRIPTION ID>" `
Role Contributor `
StartDate ([datetime]::Now) `
EndDate $([datetime]::now.AddYears(1)) Verbose

This service principal is valid for one year from the created date and it has Contributor Role assigned. Further using this Service principal application can access resource under given subscription.  We can scope to resources as we wish by passing resource id as a parameter for Scope.

View created AD app in Portal

   1. Log in Portal

  • Go to Azure Active Direcoty -> App Registrations
  • We can find the created app as below
  • Once we click the app we will see app details as below

We need this information when we need to login through Service principal

Login using Service Principal with Powershell

#Login with service principal
$clientId = "<CLIENT ID>"
$credentials = New-Object System.Management.Automation.PSCredential ($clientId, $securePassword)
LoginAzureRmAccount ServicePrincipal TenantId "<TENANTID>" `
SubscriptionId "<SUBSCRIPTIONID>" `
Credential $credentials

Fill out the required parameters.

Once we run the script we can successfully log in to Azure using Service Principal

Full code: 🙂

#Create AD app
$dummyUrl = "https://dummy.dummy.com"
$passpowrd = "Qwerty@123!"
$securePassword = ConvertTo-SecureString String $passpowrd AsPlainText Force
$app = New-AzureRmADApplication DisplayName $dummyUrl `
IdentifierUris $dummyUrl `
HomePage $dummyUrl `
Password $securePassword Verbose
#Create Service principal
New-AzureRmADServicePrincipal ApplicationId $app.ApplicationId `
DisplayName $dummyUrl `
Password $securePassword `
Scope "/subscriptions/<SUBSCRIPTION ID>" `
Role Contributor `
StartDate ([datetime]::Now) `
EndDate $([datetime]::now.AddYears(1)) Verbose
#Login with service principal
$clientId = "<CLIENT ID>"
$credentials = New-Object System.Management.Automation.PSCredential ($clientId, $securePassword)
LoginAzureRmAccount ServicePrincipal TenantId "<TENANTID>" `
SubscriptionId "<SUBSCRIPTIONID>" `
Credential $credentials