Share via

ARM template outputs for Azure SQL Database

  • Article

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

Comments

  • Anonymous
    April 07, 2017
    The comment has been removed