Create SQL Server via Azure Resource Manager (ARM) template

Question

I am trying to create a new Azure instance of SQL Server in which I would like to then create a few new databases.

I know from the Azure Portal that some sort of admin users could be:

• an SA user (I think this means "Server Admin" and it looks like some sort of old way of managing a SQL Server instance, but at the same time very "basic" and proved to work)
• an Active Directory user (not sure about Azure terminology here, but it looks like this could be some "broad user" for the whole Azure platform, like e.g. my own login user for the Azure Portal, this is not specific to databases world).

I would like to create a SQL Server with a SA user to administer the server. From the Azure portal I can not find a way to generate an ARM template for a SA user for the SQL Server instance.

I am copy pasting from a 10000 lines ARM template for a very long list of SQL servers and databases but I am not able to isolate the basic steps to have a hopefully clean and short ARM template to start with.

This is the ARM template I am trying to deploy on Azure:

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
      "location": {
          "type": "string",
          "defaultValue": "westeurope"
        },
      "foo_sql_server_name": {
          "defaultValue": "foo-sql-server",
          "type": "String"
      }
    },
    "resources": [
      {
          "type": "Microsoft.Sql/servers",
          "kind": "v12.0",
          "name": "[parameters('foo_sql_server_name')]",
          "apiVersion": "2015-05-01-preview",
          "location": "[parameters('location')]",
          "scale": null,
          "properties": {
              "administratorLogin": "<MY_SA_USER_THAT_I_CAN_NOT_CREATE>",
              "version": "12.0"
          },
          "dependsOn": []
      }
    ]
}

When running the above with:

az group deployment create \
  --name "deployDBs" \
  --resource-group "MyCustomResourceGroup" \
  --template-file ./templates/db.json # --verbose --debug

Then I get the following error message:

Deployment failed. Correlation ID: <A_CUSTOM_GUID>. {
  "status": "Failed",
  "error": {
    "code": "ResourceDeploymentFailure",
    "message": "The resource operation completed with terminal provisioning state 'Failed'.",
    "details": [
      {
        "code": "InvalidParameterValue",
        "message": "Invalid value given for parameter Password. Specify a valid parameter value."
      }
    ]
  }
}

When removing the JSON field administratorLogin (because hopefully I could create the SA user somehow somewhere else that I yet have to figure out), then I get the following error message:

Deployment failed. Correlation ID: <ANOTHER_CUSTOM_GUID>. {
  "status": "Failed",
  "error": {
    "code": "ResourceDeploymentFailure",
    "message": "The resource operation completed with terminal provisioning state 'Failed'.",
    "details": [
      {
        "code": "InvalidParameterValue",
        "message": "Invalid value given for parameter Login. Specify a valid parameter value."
      }
    ]
  }
}

I am not able to find the definition for the pair "username password" for the SA user (Server Admin) from the 10000 lines auto-generated ARM template.

How could I create/inject a SA user for the SQL Server while deploying a new instance of a SQL Server?

Solution 1

The sa login you use on an on-premises SQL Server instance is known on Azure SQL as the Admin login. You can provide the name of the admin login and its password as parameter as shown on below sample template:

{
  "$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;')]"
    }
  }

}

Solution 2

Working sample:

"name": "name",
"type": "Microsoft.Sql/servers",
"location": "[resourceGroup().location]",
"apiVersion": "2014-04-01",
"properties": {
    "administratorLogin": "somelogin",
    "administratorLoginPassword": "somepasswordD1!"
}

please note that SA might not be allowed as a username and password has complexity requirements

Solution 3

We wanted to create a temporary unique password per resource group and don't have to worry about passwords in template or parameters files since these are checked into git. Solved it like this:

template.json:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "vulnerabilityAssessments_Default_storageContainerPath": {
      "type": "SecureString"
    },
    "servers_dev_name": {
      "defaultValue": "dev-app",
      "type": "String"
    }
  },
  "variables": {
    "servers_dev_password": "[concat('P', uniqueString(resourceGroup().id, '224F5A8B-51DB-46A3-A7C8-59B0DD584A41'), 'x', '!')]",
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2019-06-01-preview",
      "name": "[parameters('servers_dev_name')]",
      "location": "northeurope",
      "kind": "v12.0",
      "properties": {
        "administratorLogin": "OurSaName",
        "administratorLoginPassword": "[variables('servers_dev_password')]",
        "version": "12.0",
        "publicNetworkAccess": "Enabled"
      }
    },

"To make sure that we are compliant with the Azure SQL database policy "Your password must contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, etc.)", we insert one character for each category before and after the unique string."

Sources:

https://docs.microsoft.com/en-us/azure/azure-resource-manager/templates/template-functions-string#uniquestring

https://vivien-chevallier.com/Articles/automatically-generate-a-password-for-an-azure-sql-database-with-arm-template

Warning:

If you add to your parameters.json:

"servers_dev_password": {
  "value": "[uniqueString(resourceGroup().id)]"
}

and add the parameter to template.json the actual password will be:[uniqueString(resourceGroup().id)].

"servers_dev_password": {
  "type": "SecureString"
}

A thing to note is that the definition for uniqueString is:

Creates a deterministic hash string based on the values provided as parameters.

This means that if you want to create a unique password per deployment it would have to look something like this:

"parameters": {
  "newGuid": {
    "type": "string",
    "defaultValue": "[newGuid()]"
  }
}

"variables": {
  "sqlserverAdminPassword": "[concat(uniqueString(guid(resourceGroup().id, deployment().name)), parameters('newGuid'), 'Tg2%')]"
}

Your password would then be updated on every deploy.

https://stackoverflow.com/a/70325944/3850405

Comments

[TPPZ]

Thanks, is there a way to inject at runtime that password value? Ii seems very dangerous to put a password value in a JSON template. That 10000 lines ARM template I got from the Azure Portal does not contain the administratorLoginPassword and I am wondering what is the recommended best practice here. Regarding the password structure requirement here are the rules: docs.microsoft.com/en-us/sql/relational-databases/security/…

[4c74356b41]

well, you can use a parameter for that (like the one you use for a name), or use a key vault reference

[Johan Stenberg - MSFT]

Using KeyVault secret with Azure Resource Manager templates: docs.microsoft.com/en-us/azure/azure-resource-manager/… Parameters in Azure Resource Manager templates (specifically, search for secureString): docs.microsoft.com/en-us/azure/azure-resource-manager/…

[TPPZ]

@JohanStenberg-MSFT @4c74356b41 thanks regarding the Key Vault. I was thinking at a more unix/bash solution local to my PC/laptop like dynamically replacing a placeholder in the JSON template with an environment variable combined with commands like sed & awk, perhaps something more modern that comes from the Azure CLI. However looking at the documentation it seems easy to setup a Key Vault via a JSON ARM template. Thanks again!