ARM template outputs for Azure SQL Database

[OrsolyaGal]

First published on MSDN on Dec 01, 2016
Azure Resource Manager (ARM) templates are a common way to deploy Azure Resources. One of the less often used features is the output. You may want to use this with linked templates to pass variables between templates, or if you like to print out some information. Here I describe how you can display Azure SQL Database information in the output.

To get an overview of ARM and linked templates, please check out the docs:

Azure Resource Manager overview
Using linked templates with Azure Resource Manager

As always, please follow the recommendations:

Best practices for creating Azure Resource Manager templates

To demonstrate the output information for Azure SQL Database I used a very simple JSON to create a server with one database. Scroll down to get the full template and some logistics how I tested.
The last part of the template is the output section.

"outputs": {

}

We can print out simple text, parameter or variable:

"outputs": {
"SomeString": {
"type": "string",
"value": "Whatever you want to print out"
},
"ServerNameParam": {
"type": "string",
"value": "[parameters('yourservernameName')]"
}
}

You will get:

Name Type Value
=============== ========================= ==========
someString String What ever you want to put here
serverNameParam String yourservername2

More interesting is the Resource ID:

"outputs": {
"ServerResourceID": {
"type": "string",
"value": "[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
},
"DbResourceID": {
"type": "string",
"value": "[resourceId('Microsoft.Sql/servers/databases', parameters('yourservernameName'), parameters('dbnameName'))]"
}
}

The output looks like this:

Name Type Value
=============== ========================= ==========
serverResourceID String /subscriptions/12345678-1234-abcd-a1b2-a1a1a1a1a1a1/resourceGroups/resourcegroupname/providers/Microsoft.Sql/servers/yourservername
dbResourceID String /subscriptions/12345678-1234-abcd-a1b2-a1a1a1a1a1a1/resourceGroups/resourcegroupname/providers/Microsoft.Sql/servers/yourservername/databases/dbname

The server object or database object:

"outputs": {
"ServerObject": {
"type": "object",
"value": "[reference(parameters('yourservernameName'))]"
},
"DbObject": {
"type": "string",
"value": "[reference(parameters('dbnameName'))]"
}
}

The output

Name Type Value
=============== ========================= ==========
serverObject Object {
"fullyQualifiedDomainName": "yourservername.database.windows.net",
"administratorLogin": "VeryWiseAdmin",
"administratorLoginPassword": "ReplaceWithTheMostSecurePasswordThatEverExisted&NeverShareLikeThisWithAnyone!",
"externalAdministratorLogin": null,
"externalAdministratorSid": null,
"version": "12.0",
"state": "Ready"
}
dbObject Object {
"databaseId": "f5cae93b-5770-4907-b25f-687dfce1f3d5",
"edition": "Basic",
"status": "Online",
"serviceLevelObjective": "Basic",
"collation": "SQL_Latin1_General_CP1_CI_AS",
"maxSizeBytes": "1073741824",
"creationDate": "2016-11-29T15:44:27.453Z",
"currentServiceObjectiveId": "dd6d99bb-f193-4ec1-86f2-43d3bccbc49c",
"requestedServiceObjectiveId": "dd6d99bb-f193-4ec1-86f2-43d3bccbc49c",
"requestedServiceObjectiveName": "Basic",
"sampleName": null,
"defaultSecondaryLocation": "North Europe",
"earliestRestoreDate": "2016-11-29T15:55:00.003Z",
"elasticPoolName": null,
"containmentState": 2,
"readScale": "Disabled"
}

Or if you want only one of the properties, in this example only the server URL:

"outputs": {
"SqlServerURL": {
"type": "string",
"value": "[reference(parameters('yourservernameName')).fullyQualifiedDomainName]"
}
}

The output

Name Type Value
=============== ========================= ==========
sqlServerURL String yourservername.database.windows.net

You can also use the ARM template functions to put together something more complex, in this example a ADO.Net connection string:

"outputs": {
"DbAdoConnString": {
"type": "string",
"value": "[concat('Server=tcp:',reference(parameters('yourservernameName')).fullyQualifiedDomainName,',1433;Initial Catalog=',parameters('dbnameName')';Persist Security Info=False;User ID=',reference(parameters('yourservernameName')).administratorLogin,';Password=',reference(parameters('yourservernameName')).administratorLoginPassword,';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;')]"
}
}

The output

Name Type Value
=============== ========================= ==========
dbAdoConnString String Server=tcp:yourservername.database.windows.net,1433;Initial Catalog=dbname;Persist Security Info=False;User ID=VeryWiseAdmin;Password= ReplaceWithTheMostSecurePasswordThatEverExisted&NeverShareLikeThisWithAnyone!;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Note
The listkeys function is not available for all resources, amongst others you cannot use it with Azure SQL Database.
If you try, the resources will be deployed but the output is not displayed and you get error:
"Resource not found for the segment 'listkeys'."

Logistics

I used PowerShell to deploy the template file:

# log in
Login-AzureRmAccount
# existing resource group where you would like to deploy
$RGName="resourcegroupname"
# path to the
$TemplateFile="c:\path\file.json"
# validate the template
Test-AzureRmResourceGroupDeployment -ResourceGroupName $RGName -TemplateFile $TemplateFile
# deploy to the specified resource group
New-AzureRmResourceGroupDeployment -ResourceGroupName $RGName -TemplateFile $TemplateFile
# list all the deplyoments for the resource group
Get-AzureRmResourceGroupDeployment -ResourceGroupName $RGName
# stop a deployment, this may be helpful for large deployment files which have many resources and you realize you didn't want to deploy just yet
Stop-AzureRmResourceGroupDeployment -ResourceGroupName $RGName -Name "export"

A simple template example:
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"yourservernameName": {
"type": "string",
"defaultValue": "yourservername2"
},
"yourservernameAdminLogin": {
"type": "string",
"defaultValue": "VeryWiseAdmin",
"minLength": 1
},
"yourservernameAdminLoginPassword": {
"type": "securestring",
"defaultValue": "ReplaceWithTheMostSecurePasswordThatEverExisted&NeverShareLikeThisWithAnyone!"
},
"dbnameName": {
"type": "string",
"defaultValue": "dbname",
"minLength": 1
},
"dbnameCollation": {
"type": "string",
"minLength": 1,
"defaultValue": "SQL_Latin1_General_CP1_CI_AS"
},
"dbnameEdition": {
"type": "string",
"defaultValue": "Basic"
},
"dbnameRequestedServiceObjectiveName": {
"type": "string",
"defaultValue": "Basic"
}
},
"variables": {
},
"resources": [
{
"name": "[parameters('yourservernameName')]",
"type": "Microsoft.Sql/servers",
"location": "West Europe",
"apiVersion": "2014-04-01-preview",
"dependsOn": [],
"tags": {
"displayName": "yourservername"
},
"properties": {
"administratorLogin": "[parameters('yourservernameAdminLogin')]",
"administratorLoginPassword": "[parameters('yourservernameAdminLoginPassword')]",
"version": "12.0"
},
"resources": [
{
"name": "[concat(parameters('yourservernameName'),'/AllowAllWindowsAzureIps')]",
"type": "Microsoft.Sql/servers/firewallRules",
"location": "[resourceGroup().location]",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
],
"properties": {
"startIpAddress": "0.0.0.0",
"endIpAddress": "0.0.0.0"
}
},
{
"name": "[concat(parameters('yourservernameName'),'/',parameters('dbnameName'))]",
"type": "Microsoft.Sql/servers/databases",
"location": "West Europe",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
],
"tags": {
"displayName": "dbname"
},
"properties": {
"collation": "[parameters('dbnameCollation')]",
"edition": "[parameters('dbnameEdition')]",
"maxSizeBytes": "1073741824",
"requestedServiceObjectiveName": "[parameters('dbnameRequestedServiceObjectiveName')]"
}
}
]
}
],
"outputs": {
"SomeString": {
"type": "string",
"value": "What ever you want to put here"
},
"ServerNameParam": {
"type": "string",
"value": "[parameters('yourservernameName')]"
},
"ServerResourceID": {
"type": "string",
"value": "[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
},
"ServerObject": {
"type": "object",
"value": "[reference(parameters('yourservernameName'))]"
},
"SqlServerURL": {
"type": "string",
"value": "[reference(parameters('yourservernameName')).fullyQualifiedDomainName]"
},
"DbResourceID": {
"type": "string",
"value": "[resourceId('Microsoft.Sql/servers/databases', parameters('yourservernameName'), parameters('dbnameName'))]"
},
"DbObject": {
"type": "object",
"value": "[reference(parameters('dbnameName'))]"
},
"DbAdoConnString": {
"type": "string",
"value": "[concat('Server=tcp:',reference(parameters('yourservernameName')).fullyQualifiedDomainName,',1433;Initial Catalog=',parameters('dbnameName'),';Persist Security Info=False;User ID=',reference(parameters('yourservernameName')).administratorLogin,';Password=',reference(parameters('yourservernameName')).administratorLoginPassword,';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;')]"
}
}

}

I hope this information is helpful, if you have any questions please let me know in the comments,
Orsi