[ Microsoft.Sql/servers ]: Invalid value given for parameter ExternalAdministratorLoginName

[vargasol]

Resource Type

Microsoft.Sql/servers

Api Version

2024-05-01-preview

Issue Type

Inaccurate property type(s)

Other Notes

Hi,

First of all, checked the following topic already: [Bicep: Microsoft.Sql/servers@2022-05-01-preview Exception: Invalid value given for parameter ExternalAdministratorLoginName](https://stackoverflow.com/questions/77084543/bicep-microsoft-sql-servers2022-05-01-preview-exception-invalid-value-given-f)

Did not help.
It does not help if I use Microsoft.Sql/servers/administrator. If I use it as a dedicated resource, I receive administratorType must be configured ActiveDirectory, however it is already configured.

My bicep module can be found below.

Anyone any idea why the problem was not solved?

If I create the server and configures the aad login on portal, no ExternalAdministratorLoginName property is visible.

Error:
New-AzDeployment: 12:54:23 - The deployment 'sql-server-test' failed with error(s). Showing 1 out of 1 error(s).
Status Message: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage details. (Code: DeploymentFailed)
The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'. (Code: ResourceDeploymentFailure)
Invalid value given for parameter ExternalAdministratorLoginName. Specify a valid parameter value. (Code:InvalidParameterValue)

None of MS guides has any mention about this property.

Thanks!
Gabor Varga

Bicep Repro

@description('Location of paas sql server to be deployed')
param location string = 'West Europe'

@description('Name of paas sql server')
param name string

@description('Name of SQL administrator user')
param sqlAdministrator string

@description('SQL Administrator password')
@secure()
param sqlAdministratorPassword string

@description('Set true if you want to allow only AzureAD authentication. Default: false')
param azureADOnlyAuthentication bool = false

@description('Tags of resource')
param tags object = {}

@description('Set true if you want to enable Entra ID authentication. Default: false')
param configureEntraAdministrator bool = false

@description('Type of SQL admins. Values: Application, Group, User')
@allowed(['Application','Group','User'])
param aadSqlAdminType string = 'Group'

@description('Name of SQL admin group/user/application to be added on SQL server')
param aadSqlAdminObjectname string = ''

@description('Object Id of group/user/application to be configured on SQL server as administrator')
param aadSqlAdminObjectId string = ''

@description('Set true if you want to enable ipv6 support on SQL server.')
param ipV6Enabled bool = false

@description('Object id of federated client for customer managed key access. Default: empty')
param federatedClientId string = ''

@description('URI of customer managed key. Default: empty')
param cmkUri string = ''

@description('Minimal version of Tls encryption to be used for communication. Default: 1.2')
@allowed(['1.0','1.1','1.2','1.2','None'])
param minimalTlsVersion string = '1.2'

@description('Type of identity to be used by SQL server. Values: None, SystemOnly, UserOnly, SystemUser. Default: None')
param assignedIdentityType string = 'None'

@description('''User assigned identities to be assigned to SQL server as an object where key is the id of user assigned identity and value is empty. Please note that 1st identity will be used as primary identity. Format: 
{
  'object-id-1' : ''
  'object-id-2' : ''
  ...
}''')
param userAssignedIdentities object = {}

@description('Set true if you enable access to SQL server from public internet. Default: true')
param enablePublicNetworkAccess bool = true

@description('Set true if you want to restrict network outbound connectivity. Default: false')
param restrictOutboundNetworkConnections bool = false

@description('Set the version of SQL server. Default: 12.0')
param sqlServerVersion string = '12.0'

@description('Set true if you want to add private endpoint for this sql server. Default: true')
param addPrivateEndpoint bool = false

@description('Resource id of subnet where the sql server private endpoint is joined to. Required if addPrivatEndpoint equals true')
param privateEndpointSubnetId string = ''

@description('Resource id of private DNS zone where the private endpoint is registered into. Required if addPrivateEndpoint equals true')
param privateEndpointDnsZoneId string = ''

var primaryUserAssignedIdentity = assignedIdentityType == 'UserOnly' || assignedIdentityType == 'SystemUser' ? items(userAssignedIdentities)[0].key : null

resource sqlServer 'Microsoft.Sql/servers@2024-05-01-preview' = {
  identity: assignedIdentityType != 'None' ? {
    type: assignedIdentityType == 'SystemOnly' ? 'SystemAssigned' : (assignedIdentityType == 'SystemUser' ? 'SystemAssigned,UserAssigned' : 'UserAssigned')
     userAssignedIdentities: assignedIdentityType == 'SystemUser' || assignedIdentityType == 'UserOnly' ? userAssignedIdentities : null
  } : null
  location: location
  name: name
  properties: {
    administratorLogin: sqlAdministrator
    administratorLoginPassword: sqlAdministratorPassword
    /*
     administrators: {
      administratorType: 'ActiveDirectory'
      login: aadSqlAdminObjectname
      sid: aadSqlAdminObjectId
      tenantId: tenant().tenantId
      principalType: aadSqlAdminType
     }
      */
    federatedClientId: federatedClientId != '' ? federatedClientId : null 
    isIPv6Enabled: ipV6Enabled ? 'Enabled' : 'Disabled'
    keyId: cmkUri != '' ? cmkUri : null
    minimalTlsVersion: minimalTlsVersion
    primaryUserAssignedIdentityId: primaryUserAssignedIdentity
    publicNetworkAccess: enablePublicNetworkAccess ? 'Enabled' : 'Disabled'
    restrictOutboundNetworkAccess: restrictOutboundNetworkConnections ? 'Enabled' : 'Disabled'
    version: sqlServerVersion
  }
  tags: tags
}

resource sqlAadAdmin 'Microsoft.Sql/servers/administrators@2024-05-01-preview' = if (configureEntraAdministrator) {
  name: 'AADSQLAdmins'
  parent: sqlServer
  properties: {
    administratorType: 'ActiveDirectory' // this is detected as a missing
    login: aadSqlAdminObjectname
    sid: aadSqlAdminObjectId
    tenantId: tenant().tenantId
  }
}

resource aadAuthOnly 'Microsoft.Sql/servers/azureADOnlyAuthentications@2024-05-01-preview' = if (azureADOnlyAuthentication) {
  parent: sqlServer
  name: 'sqlAadAuthOnly'
  properties: {
    azureADOnlyAuthentication: true
  }
}

module privateEndpoint 'br:crshseitprd01iaccore.azurecr.io/bicep/modules/privateendpoint:1.0.0' = if (addPrivateEndpoint) {
  name: '${name}-pe_deployment'
  params: {
    name: '${name}-pe'
    location: location
    resourceId: sqlServer.id
    serviceId: 'sqlServer'
    subnetId: privateEndpointSubnetId
    pvepDnsZoneId: privateEndpointDnsZoneId
    registerDns: true
  }
}

output id string = sqlServer.id
output name string = sqlServer.name

Confirm

• I have read the troubleshooting guide and looked for duplicates.