Microsoft.Sql/servers not supporting changing AAD admin with templating #2320
Comments
|
The PUT request you invoke with targetScope = 'resourceGroup'
param location string
param environmentName string
param project string
param tags object
param sqlAdministratorLogin string
@secure()
param sqlAdministratorLoginPassword string
param administrators object
resource SqlServer 'Microsoft.Sql/servers@2021-05-01-preview' = {
name: '${project}-${environmentName}-sqlserver'
location: location
tags: tags
properties: {
administratorLogin: sqlAdministratorLogin
administratorLoginPassword: sqlAdministratorLoginPassword
version: '12.0'
}
}
resource sqlAdmins 'Microsoft.Sql/servers/administrators@2021-05-01-preview' = {
parent: SqlServer
name: 'ActiveDirectory'
properties: administrators
}Does that help? |
|
That works nicely. Good catch. Anyway, looks like you can set the setting once with bicep + That should be somehow reflected on VS code tooltips and https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/servers?tabs=bicep |
|
Is there any news on this one? It is still only possible to set the AAD admin once. When trying to change it via bicep, changes are ignored. |
Using |
Like this it working. Thanks. The reason why we put the administators directly inside |
|
And unfortunately this is still the case: |
|
Any InternalServerErrors can only be debugged/fixed by the Resource Provider team. Please open a support case to get this routed to the SQL RP team to further traction on the issue. |
|
Hello, I apologize for late reply, just came to know about this thread. Let me explain a little bit behavior wise.
I recently made a change to fail this update workflow when servers call is made with administrators in it. I will take a look at this again to make sure behavior is documented and errors are visible to the caller. |
|
@martinakarolina @jikuja @anthony-c-martin I validated on my prod server that if on update server, changes are made to the AAD Admin , it will throw an error now. This is what my template is. |
|
And if AD Admin is set already, how do we remove/unset it via template/pipeline? |
|
Also, please please change the error message to something that does not make me think something is wrong with parameter passing. Maybe like "You can only set AD Admin on the servers api on initial deployment. Please set on the administrators api otherwise" |
|
Hey folks -- this is not something we can fix on the bicep/ARM template side. @leifdude / @jikuja / @amolagar5 -- can any of you open a support ticket so this can be routed to the SQL team? I will also pass this on to the contacts we have on the SQL team. |
|
Hi. For anyone, who is stuck on this: I've got an answer from MSFT support, that "upsert is not a supported operation for the administrators property in the SQL Server API" and that you would have to use two separate templates. One for deployment and the other one for changing / updating the AAD admin. |
|
That is not an acceptable answer. Per the ARM Resource Provider Contract, they must support idempotent PUT operations if the payloads are identical to the current state. It's ok to not allow updates to specific values, but if the values are the same then it must be allowed. Can you ask the support contact at microsoft to reach out to us at alfran@microsoft.com? |
Hi Alex. Maybe I explained it incorrectly. They do allow using the same value, which results in no-op. The "update" operation is the issue. |
|
Ah sorry for misreading.
Did they share what a template would look like that could update this property? From what you are saying, I am assuming it is not possible to update this resource via Bicep/ARM Templates at all. |
|
I think I'm hitting this same issue. I'm not able to update the adminstrator, but my deployment also breaks if I change the AD administror to different value and then back to the original via PowerShell or Azure Portal. So if I touch the ad admin in any way my next bicep deploy will fail even though I'm trying to set properties to same values the server already has. {
"status": "Failed",
"error": {
"code": "ResourceDeploymentFailure",
"message": "The resource operation completed with terminal provisioning state 'Failed'.",
"details": [
{
"code": "InvalidParameterValue",
"message": "Invalid value given for parameter ExternalAdministratorLoginSid. Specify a valid parameter value."
}
]
}
} (Code:Conflict)I've also noticed that if I've originally set the AD admin principal type to Application and then change the AD via PowerShell or Portal the principal type changes to Group. Even though admin is not a group. Using Group as admin type in bicep instead of Application doesn't help with the issue. Next I'll try to work around the issue by checking if the server already exists via script and then passing that information to my bicep template. If someone doesn't have a better idea. |
They did not share the template, but a link to SQL server administrators API (https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/administrators?pivots=deployment-language-bicep). Update works, you only have to use different template for that. The initial (deployment) template has to have AAD admin set as a property of "Microsoft.Sql/servers" resource. The "update" template would have to have "Microsoft.Sql/servers/administrators" resource. @juho-turunen-tampuuri I've just tested deploying bicep template with User A as AAD admin, changing to user B via portal, changing back to User A via portal and then deploying the bicep template again. I've got no error messages. Maybe it's the API version? I'm using 'Microsoft.Sql/servers@2022-05-01-preview' |
Thank you for testing, but I can't get it to work. I'm changing the administrator via PowerShell command Set-AzSqlServerActiveDirectoryAdministrator. The original admin is managed identity with principal type Application and the temporary admin is a human user. I thought I tried it with portal, too, but I'm not sure anymore. Might be some other problem with my setup but I'm pretty sure I've narrowed it down to this. The reason for using a managed identity as admin is to give application access to the database without needing a human to create a user for it. But if a need arises to check out the database by a person you would need to change the administrator at least for the duration of creating an additional user for the database. At the moment I don't have possibility to let Service principal create additional users and I'm trying to stick with AAD only authentication. Here's my template for reference. I've tried principalType Group. I don't know if the principalType has any meaning. PowerShell always changes it to Group. resource sqlserver 'Microsoft.Sql/servers@2022-05-01-preview' = {
name: 'somename'
properties: {
publicNetworkAccess: 'Enabled'
administrators: {
azureADOnlyAuthentication: true
administratorType: 'ActiveDirectory'
principalType: 'Application'
sid: adminSid
login: adminName
}
restrictOutboundNetworkAccess: 'Disabled'
minimalTlsVersion: '1.2'
}
identity: {
type: 'SystemAssigned'
}
location: location
}EDIT: It seems it works sometimes. I can't really pinpoint what's happening. This might be related to time somehow. Perhaps chnaging the administrator takes some time and if deploying too soon after a change it fails? |
This would not be a solution. Scenario: You do a check if sqlserver exist, Btw this can be done within bicep with existing and checking if the resource is null. i dont see why we need to use that whole powershell command to check if a resource exist. You deploy initial sql server. Great Week later you figure you need to change one parameter and redeploy. Won't work because the script detect sql exist and therefor it will not do an incremental update of the sql server. |
Wiat what? Is that really a sipported feature? |
Yeah, resource sql_server 'Microsoft.Sql/servers@2022-05-01-preview' existing = module deploy_db './modules/sql_db_.bicep' = if (sql_server ) { { see https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/existing-resource |
|
… > If you attempt to reference a resource that doesn't exist, you get the NotFound error and your deployment fails. Check the name and scope of the resource you're trying to reference.
--
Janne Kujanpää
From: Menno Laan ***@***.***>
Reply to: Azure/bicep ***@***.***>
Date: Tuesday 14. March 2023 at 11.09
To: Azure/bicep ***@***.***>
Cc: Janne Kujanpää ***@***.***>, Mention ***@***.***>
Subject: Re: [Azure/bicep] Microsoft.Sql/servers not supporting changing AAD admin with templating (Issue Azure/bicep-types-az#2320)
You do a check if sqlserver exist, Btw this can be done within bicep with existing and checking if the resource is null. i dont see why we need to use that whole powershell command to check if a resource exist.
Wiat what? Is that really a sipported feature?
Yeah, resource sql_server ***@***.***' existing =
{
name: sql_server_name
scope: reg_name
}
module deploy_db './modules/sql_db_.bicep' = if (sql_server ) {
{
see https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/existing-resource
—
Reply to this email directly, view it on GitHub, or unsubscribe.
You are receiving this because you were mentioned.Message ID: ***@***.***>
|
Yes and no! let me explain. If you do try to reference a resource that doesn't exist like they do with accessing a sub property, yes it fails! How ever if(sqlServe) is a bool return on the check. So yes and no, this wont fail. But they created a solution for this! So in this case you can do an inline check to see if a value from slqExisting is null, if not you can access the value, otherwise use a different value |
Are you sure this works? It would have made my life easier. But in the provided link it says "If you attempt to reference a resource that doesn't exist, you get the NotFound error and your deployment fails." |
|
FYI, some existing work: |
That refers to the output. So yes if the resource doesn’t exist it will fail as the output can’t handle null reference |
|
This issue seems documented now. From https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/2022-05-01-preview/servers?pivots=deployment-language-bicep
This is the bicep we are using (might help someone): // https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/2022-05-01-preview/servers?pivots=deployment-language-bicep
resource sqlServerResource 'Microsoft.Sql/servers@2022-05-01-preview' = {
location: location
name: serverName
properties: {
minimalTlsVersion: '1.2'
administratorLogin: 'sqladmin'
// administratorLoginPassword: required for server creation - just set to dummy value, will be disabled by deploying '/azureADOnlyAuthentications' later.
administratorLoginPassword: guid(serverName)
// administrators: The Azure Active Directory administrator of the server. This can only be used at server create time. If used for server update, it will be ignored or it will result in an error. For updates individual APIs will need to be used.
administrators: {}
version: '12.0'
}
}
// https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/administrators?pivots=deployment-language-bicep
resource sqlAdminsResource 'Microsoft.Sql/servers/administrators@2022-05-01-preview' = {
parent: sqlServerResource
name: 'ActiveDirectory'
properties: {
administratorType: 'ActiveDirectory'
login: 'managedIdentity.name'
sid: 'managedIdentity.properties.principalId'
tenantId: 'managedIdentity.properties.tenantId'
}
}
// https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/azureadonlyauthentications?pivots=deployment-language-bicep
resource sqlAzureAdOnly 'Microsoft.Sql/servers/azureADOnlyAuthentications@2022-05-01-preview' = {
name: 'Default'
parent: sqlServerResource
properties: {
azureADOnlyAuthentication: true
}
dependsOn:[sqlAdminsResource]
} |
Thanks. Which use-cases work with that template:
All or only some? |
I'm not the author, but I would think that works for every situation. But if the api changes in the future versions in a way that administrators-property is also respected in updates, this might cause an issue where administrator is removed and then re-added in the next step. This might go unnoticed for a long while, so you should check this every time you change to a newer api-version. Seems unlikely that this behaviour would change, though. At the moment we're checking if the server is allready deployed and have and condition in the bicep file for that. But that's a bit of a hassle and cant' be done in bicep alone. So maby we'll switch to jikuja's solution. |
Things I tested :
All had expected outcome. |
Sad to report that this is not the case (anymore?), on initial deployment everything is fine. Subsequent deploys of the template work when you set the |
|
We've also just hit the same, that re-deployment w/o changes breaks the pipeline all of a sudden. |
|
is there a fix for this? I tried this and it did not work at all I open a ticket but I want to know if someone is able to update their Admin Group and SID without this error {"code":"InvalidParameterValue","message":"Invalid value given for parameter ExternalAdministratorLoginName. Specify a valid parameter value."}]}]}} |
|
well I opened a support ticket and they just called me that they'll back with me in Next Monday or Tuesday 💯 so let's wait |
|
this was their answer I hope you are doing well today. After checking internally, I can confirm that this is not a supported operation. Your best bet to accomplish this will be to try removing the admin and adding a new one. This is based on the well-known understanding that Azure SQL Database allows you to remove the server admin and add a new one. Therefore, you may change the code to: 1 - Get the current user admin 2 - Remove the current user admin 3 - Add the new one Last, as previously informed, Microsoft Support teams mostly provide support for break and fix scenarios, and we do our best on advisory cases. Hence, if the recommendation above does not work for your environment, we will not be able to go further. |
|
First level support just does not understand concept of IaC and idempotency, not even with a reasoning. MSFT seriously should start pushing importance on IaC the current state is just a joke. I recommend that you try to escalate ticket to PG or to someone whi understand why IaC should be used instead of clickops. |
Bicep version
Bicep CLI version 0.4.1008 (223b8d227a)
Describe the bug
Re-deployment Microsoft.Sql/servers [2020-11-01-preview and 2021-05-01-preview and probably others] does not change AD admin property(properties.administrators).
To Reproduce
Deploy template once:
params:
{ "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#", "contentVersion": "1.0.0.0", "parameters": { "administrators": { "value": { "administratorType": "ActiveDirectory", "azureADOnlyAuthentication": false, "principalType": "Group", "tenantId": "0a8edbb9-xxxx-xxxx-xxxx-2cd241f8236f", "sid": "5bc4b275-xxxx-xxxx-xxxx-2fb5b5ff5fe1", "login": "SQL Admin2" } } } }Change sid and login to other AAD group and run What-If analysis. Results:
Re-deploy template with new parameters and check AAD admin value on the portal:
Additional context
Running API request changes AAD admin value:
The text was updated successfully, but these errors were encountered: