Create Azure SQL DB with ONLY AD Administrator

only connections established with active directory accounts can create other active directory users.
active directory integrated authentication sql server
add user to azure sql database
azure sql database roles
active directory integrated authentication sql server connection string
give read only access to azure sql database
add ad group to azure sql database
sql azure check user permissions

I'm in the process of converting my Azure SQL DB to use Active Directory authentication.

I've added this snippet to my ARM template which correctly sets up the Administrator as the AD Group. So far, so good!

{
  "type": "administrators",
  "name": "activeDirectory",
  "apiVersion": "2014-04-01-preview",
  "location": "[resourceGroup().location]",
  "properties": {
    "administratorType": "ActiveDirectory",
    "login": "[parameters('sql_ad_admin_username')]",
    "sid": "[parameters('sql_ad_admin_objectid')]",
    "tenantId": "[parameters('azure_ad_directory_id')]"
  },
  "dependsOn": [
    "[parameters('sql_db_name')]"
  ]
},

Now that I've got it working, I want to get rid of the old SQL Server Auth Administrator details from the ARM Template (and I want to store the template in source control, so obviously no credentials should be in it).

So, I tried deleting these administratorLogin and administratorLoginPassword entries from the file (after all they're not needed any more, I have an AD Administrator instead):

  "properties": {
    "administratorLogin": "admin",
    "administratorLoginPassword": "XXXXXX",
    "version": "12.0"
  },

However, after deleting those, I get the following error running the template:

Invalid value given for parameter Login. Specify a valid parameter value.

Now I'm a bit confused. Why can't I get rid of these? I am now using AD Authentication which is more secure than SQL Authentication, yet it seems to be forcing me to have a less secure username/password administrator login set up? How can I disable this?

For Azure SQL Database, you cannot get rid of the initial SQL Administrator login and password. This is always required, however in the same way that you have parameterized the "login", "sid", and "tenantid" for the AD Administrator you could do the same for the "administratorLogin" and "administratorLoginPassword" values in the template.

The admin structure is shown here

Azure Active Directory authentication, The login gets you access to the SQL Server only. Then you Create a contained Azure Active Directory user for a database(s). Create Connect to your Azure SQL Database server with SSMS as an admin in master. Create� To create contained users mapped to Azure AD identities, you must be logged in using an Azure AD account that is an administrator in the database in Azure SQL Database. In SQL Managed Instance, a SQL login with sysadmin permissions can also create an Azure AD login or user.

According to my research, when we create Azure SQL server with ARM template, we have no way to not provide administratorLogin and administratorLoginPassword. For more details, please refer to the document

Authorize server and database access using logins , For Azure SQL Database, you cannot get rid of the initial SQL Administrator login and password. This is always required, however in the same� The same subscription must be used to create the Azure SQL Database, SQL Managed Instance, or Azure Synapse resources. Administrator structure. When using Azure AD authentication, there are two Administrator accounts: the original Azure SQL Database administrator and the Azure AD administrator. The same concepts apply to Azure Synapse.

The solution I eventually went for:

Parameterise the password in the template, then configure the parameters file to get it from the Key Vault:

"sql_admin_password": {
  "reference": {
    "keyVault": {
      "id": "/subscriptions/XXXXXX-XXXXXXXXX-XXXXXXXX/resourcegroups/MY_RESOURCE_GROUP/providers/Microsoft.KeyVault/vaults/MY_KEY_VAULT_NAME"
    },
    "secretName": "SQLDatabaseAdminPassword"
  }
}

This avoids needing to have the password anywhere in source control OR anywhere in the CI system which would have been necessary to pipe it in as an explicit parameter

Credit: https://www.anexinet.com/blog/deploying-sql-azure-using-credentials-keyvault/

Adding Users to Azure SQL Databases, Setup Azure SQL Server to use Azure Active Directory authentication option Azure SQL Server to use their domain accounts (they only used SQL Server Admin — we created this account when creating a new service. Azure Active Directory (AD) can be used to access to several Azure resources like Azure SQL Database, Azure SQL Data Warehouse, Office 365, Salesforce, Dropbox, Adobe Create Cloud, ArcGis and more. Azure Active Directory is a cloud directory and an identity management service.

I also couldn't find solution to get rid of sql admin credentials. There's a parameter called azureADOnlyAuthentication added to "apiVersion": "2019-06-01-preview" of Microsoft.Sql/servers/administrators template, but all the time I tried to put any value there the db deployment failed with timeout.

I like the solution suggested by Vivien Chevallier - to generate login and password for Sql Admin. I slightly modified it based on comments, also used random generated password as a prefix. The idea is to omit sql admin credentials parameters when you use AD admin credentials, so sql admin credentials are generated, the password is not stored anywhere, so there's no way to retrieve it. The template:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]"
    },
    "sqlServerName": {
      "type": "string",
      "metadata": {
        "description": "The name of the SQL Server."
      }
    },
    "sqlServerAdministratorName": {
      "type": "string",
      "metadata": {
        "description": "The name for SQL Server administrator."
      },
      "defaultValue": "[uniqueString(resourceGroup().id, '{24CF6AE7-F4CA-44D7-8FBD-B7F85C0BDDF6}')]"
    },
    "sqlServerAdministratorPassword": {
      "type": "securestring",
      "metadata": {
        "description": "The password for SQL Server administrator."
      },
      "defaultValue": "[concat('C3@TnTAjqVnr', uniqueString(resourceGroup().id, newGuid()), toUpper(uniqueString(resourceGroup().id, newGuid())))]"
    },
    "sqlServerAdministratorADName": {
      "type": "string",
      "metadata": {
        "description": "The name of the AD user/group for SQL Server administrator."
      }
    },
    "sqlServerAdministratorADSid": {
      "type": "string",
      "metadata": {
        "description": "The SID of the AD user/group for SQL Server administrator."
      }
    },
    "transparentDataEncryption": {
      "type": "string",
      "allowedValues": [
        "Enabled",
        "Disabled"
      ],
      "defaultValue": "Disabled",
      "metadata": {
        "description": "Enable or disable Transparent Data Encryption (TDE) for the database."
      }
    },
    "databaseName": {
      "type": "string",
      "metadata": {
        "description": "The name for the database."
      }
    },
    "databaseCollation": {
      "type": "string",
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS",
      "metadata": {
        "description": "Database collation"
      }
    },
    "databaseServiceObjectiveName": {
      "type": "string",
      "defaultValue": "Basic",
      "metadata": {
        "description": "The name of the configured service level objective of the database."
      }
    },
    "sqlTier": {
      "type": "string",
      "defaultValue": "Standard"
    },
    "sqlSkuName": {
      "type": "string",
      "defaultValue": "S1"
    }
  },
  "resources": [
    {
      "name": "[parameters('sqlServerName')]",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2019-06-01-preview",
      "location": "[parameters('location')]",
      "properties": {
        "administratorLogin": "[parameters('sqlServerAdministratorName')]",
        "administratorLoginPassword": "[string(parameters('sqlServerAdministratorPassword'))]"
      },
      "tags": {
        "displayName": "SqlServer"
      },
      "resources": [
        {
          "name": "[concat(parameters('sqlServerName'), '/', 'ActiveDirectory')]",
          "type": "Microsoft.Sql/servers/administrators",
          "apiVersion": "2019-06-01-preview",
          "properties": {
            "administratorType": "ActiveDirectory",
            "login": "[parameters('sqlServerAdministratorADName')]",
            "sid": "[parameters('sqlServerAdministratorADSid')]",
            "tenantId": "[subscription().tenantId]"
          },
          "dependsOn": [
            "[parameters('sqlServerName')]"
          ]
        },
        {
          "name": "[concat(parameters('sqlServerName'), '/', parameters('databaseName'))]",
          "type": "Microsoft.Sql/servers/databases",
          "apiVersion": "2019-06-01-preview",
          "location": "[parameters('location')]",
          "tags": {
            "displayName": "Database"
          },
          "properties": {
            "collation": "[parameters('databaseCollation')]",
            "requestedServiceObjectiveName": "[parameters('databaseServiceObjectiveName')]"
          },
          "sku": {
            "name": "[parameters('sqlSkuName')]",
            "tier": "[parameters('sqlTier')]"
          },
          "dependsOn": [
            "[parameters('sqlServerName')]"
          ],
          "resources": [
            {
              "comments": "Transparent Data Encryption",
              "name": "current",
              "type": "transparentDataEncryption",
              "apiVersion": "2014-04-01",
              "properties": {
                "status": "[parameters('transparentDataEncryption')]"
              },
              "dependsOn": [
                "[parameters('databaseName')]"
              ]
            }
          ]
        }
      ]
    }
  ],
  "outputs": {
    "sqlServerFqdn": {
      "type": "string",
      "value": "[reference(resourceId('Microsoft.Sql/servers/', parameters('sqlServerName')),'2015-05-01-preview').fullyQualifiedDomainName]"
    },
    "databaseName": {
      "type": "string",
      "value": "[parameters('databaseName')]"
    }
  }
}

Example of generated parameters:

sqlServerAdministratorName           ka7bwq3hord7a
sqlServerAdministratorPassword       C3@TnTAjqVnrqquzzkrgjp4tuLS645X4JUANDU

Create Azure SQL DB with ONLY AD Administrator, A subscription to Azure; An Azure SQL Server and database created When a new person comes, you only need to assign him to the role We added Azure Active Directory Users as Administrators of Azure SQL Database. Azure Active Directory authentication is only available for MySQL 5.7 and newer. Only one Azure AD administrator can be configured for a Azure Database for MySQL server at any time. Only an Azure AD administrator for MySQL can initially connect to the Azure Database for MySQL using an Azure Active Directory account.

Setup Azure SQL Server to use Azure Active Directory , Azure Database is the PaaS solution for SQL Server databases, on a In order to create Azure AD logins, you must set up an AD administrator first using Note that as PaaS, you connect only to one database, so, the USE� Wait for the Azure SQL Database deployment to be done. Enable Azure Active Directory Authentication. With the Azure SQL Database that is created you also create an Azure SQL Server or you have chosen to use an existing one. Within the portal navigate to the Azure SQL Server. In the Azure SQL Server blade click on “Active Directory admin

Working with Azure Active Directory and Azure SQL Database, When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest"� Create a contained Azure Active Directory user for a database(s). Create a SQL authentication login, add a user mapped to it in master and add the user to a server level admin role. Create a user mapped to an Azure Active Directory user and add the user to a server level admin role. Examples . Following are examples of our options listed above:

Creating logins and users in Azure Database – SQLServerCentral, Setting up AD authentication with Azure SQL Database sounds If you look at the below diagram, I basically want to create an Active Directory Admin for something that I would recommend over just a single user account. To create a resource group, server, and single database in the Azure portal: Sign in to the portal. From the Search bar, search for and select Azure SQL. On the Azure SQL page, select Add. On the Select SQL deployment option page, select the SQL databases tile, with Single database under Resource type.

Comments
  • Shame... I'd like to keep the Production Parameters file in source control too but I guess this means it's not possible
  • You could keep it in source control as a separate file. See here
  • Right, but then wouldn't that mean that the sql admin password is exposed in source control?
  • The parameters file could contain everything except the password and then you provide that at deployment time along with the other parameters in the parameter file - i.e. you can mix and match between a parameter file and command line arguments.