Remove the administratorLogin of the SQL server resource

Question

Our goal is to not have the SQL server login created, or remove it at a later stage in an automated way, if possible.

Currently, we have the Bicep code:

resource sqlServer 'Microsoft.Sql/servers@2021-02-01-preview' = {
name: serverName 
location: location
properties: {
  administratorLogin: administratorUsername
  administratorLoginPassword: administratorPassword
  minimalTlsVersion: '1.2'
  publicNetworkAccess: publicNetworkAccess
  restrictOutboundNetworkAccess: restrictOutboundNetwork
  version: '12.0'
}
}

The property values are provided by parameters. For example, for the value of administrator login, we set something like 'admin'.

That SQL user is not something we use, as we use an automated workflow (using AZ CLI) to change the 'administratorType' to 'ActiveDirectory', during the provisioning of this resource.

However, by using this approach, the 'admin' login is still created.

We would like to use Azure AD Only Authentication. And for the login, we would like to set it to a group inside an Administrative Unit. The reason why we didn't specify this in the Bicep code, is that we don't have the SID (object ID) of that group as it has not been created yet (as it will happen later in our automated workflows). Have considered using 'Get-AzureADGroup' cmdlet via deployment script resource, so that we can get the SID of that security group. But this approach seems to automatically deploy additional resources which is denied by our internal policy.

We are now trying the following code:

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: serverName 
  location: location
  properties: {
    administrators: {
      administratorType: 'ActiveDirectory'
      azureADOnlyAuthentication: true
      login: serverExternalAdminostratorLoginExisting
      principalType: 'Group'
      sid: serverExternalAdministratorSidExisting
    }
    minimalTlsVersion: '1.2'
    publicNetworkAccess: publicNetworkAccess
    restrictOutboundNetworkAccess: restrictOutboundNetwork
    version: '12.0'
  }
}

We are not even using the property 'administratorLogin'. But it seems that a default login user is created. By looking at the template after the resource is created on Azure, we see the following:

"properties": {
                "administratorLogin": "CloudSAb7d51d5b",
                "version": "12.0",
                "minimalTlsVersion": "1.2",
                "publicNetworkAccess": "Enabled",
                "administrators": {
                    "administratorType": "ActiveDirectory",
                    "principalType": "Group",
                    "login": "admin_s704sql084",
                    "sid": "dfa109eb-****-41cb-****-ea8c69093534",
                    "tenantId": "3aa4a235-****-48d5-****-7fcf05b459b0",
                    "azureADOnlyAuthentication": true
                },
                "restrictOutboundNetworkAccess": "Enabled"
            }

How do we get rid of that 'CloudSA********' user? Could not find any documentation/information of this user. If possible, we would do that using code, to get this automated.

Answer 1

As a workaround, you can try specifying the AdministratorLogin property explicitly as an empty string ('') while defining the SQL Server resource.

By changing it like this, it will rely on only Azure AD authentication with the given specified group.

Your Modified bicep code looks like below:

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: serverName 
  location: location
  properties: {
    administratorLogin: ''
    administrators: {
      administratorType: 'ActiveDirectory'
      azureADOnlyAuthentication: true
      login: serverExternalAdminostratorLoginExisting
      principalType: 'Group'
      sid: serverExternalAdministratorSidExisting
    }
    minimalTlsVersion: '1.2'
    publicNetworkAccess: publicNetworkAccess
    restrictOutboundNetworkAccess: restrictOutboundNetwork
    version: '12.0'
  }
}