[Modules] Extend Microsoft.Sql\servers with administrators child resource

[eriqua]

Create a new child module for the Microsoft.Sql/servers/administrators resource type as per the service documentation and our contribution guide.

[AlexanderSehr]

Hey @bryansan-msft ,
I just migrated this issue over from CARML. Please take a look and triage if still relevant :)

[peterbud]

There is already an administrators property for the server.

I'm I right, that this can be set up only at server creation, and your issue is that in order to be able to modify administrators after creation, we'd need a separate module for that?

[AlexanderSehr]

Hey @peterbud,
it may be one of them unfortunate cases where a resource provider has multiple ways to deploy a single feature (like with the subnets for a vnet, or routes for a route table). In these cases there is both a property (as you called out) and a child-module available.
Now, for the aforementioned cases I'd recommend to not use the property but instead a dedicated child module because

1. The child module will eventually be published as its own instance
2. The dedicated modules often (not always) come with additional features

However, as per your statement, I presume this will not necessarily be an option for the SQL-Sever module, correct? I'm basing this statement on the sentence 'can be set up only at server creation'.
We can of course also have both the property & the child-module - but - this would only make sense if the child-module does indeed have additional features implemented, that the property wouldn't have. If not, one could still argue it would be nice to publish the extra module eventually - but I'm not sure if it would be worth the extra deployment.

Looking forward to your thoughts on the matter :)

[peterbud]

Hi @AlexanderSehr ,

I have checked, and the server's administrators property is different than the server/administrator child module, so I believe we need to have both available.

[peterbud]

I have spent more time to understand this specific administrators child resource together with the azureADOnlyAuthentications child resource (see here)

It seems that at the creation of a server you MUST use one of the two inline properties:

• administratorLogin together with administratorLoginPassword
• or inline administrators with sid etc.

If you don't use one of the two above you cannot create a server, even when you supply the same data at the child administrators resource plus the child 'azureADOnlyAuthentications'. You'll only get an error Invalid value given for parameter Login. Specify a valid parameter value

More you can read also at the Bicep issue here

So it seems to me that the administrators and azureADOnlyAuthentications child resources are ONLY suitable to change an existing server deployment, like changing the administrators or flipping the AAD only authentication.

So if the above is true: does it make sense to create these child resources, knowing that they could be only used in a standalone way?

[AlexanderSehr]

Hey @peterbud,
thanks for the comprehensive explanation. It is a good question. In this case do lean towards having both - but not because it may do us any good now, but rather that these child modules would indeed be published as standalone modules once we are able to enable the feature in the future.
However, me leaning towards something in the future does not mean you have to agree 😄 We could just as well keep this issue open and tag it with the long term label to implement it once child-modules are actually published. So, I guess it's about closing it either in the short- or long term.
It's really unfortunate to hear that the RP is implemented in this way, i.e., that the child-resources can only be used to update an existing instance as opposed to being a replacement for the property. In any case, if the two overlap heavilty, we may be able to implement the change in a way that the same property does not have to be specified twice, but that the user can specify an administrator object/array as an input parameter which would be passed into the resource deployment, and later, when there may be an administrator child module, pass the same object (optionally with more properties if available) to that one.

[peterbud]

Ok, let's tag it with a long term label.

[tony-box]

Wanted to throw in a comment that i'm using this module for SQL server deployment and I came across the issue of not being able to update EntraID administrators for an existing deployment. That led me to discovering you have to use the server/administrators child resource to update an existing adminstrators property on a SQL server. I was sad to see the SQL Server module only uses the parent administrators property so you can't update admins :(

It looks like the properties for the child module are identical to the parent administrators property.

Is it possible to use some kind of logc within the module to see if this is a new or existing deployment?

if deployment = new then use the parent administrators property.
If deployment != new use the database/administrators child module.

[peterbud]

@tony-box : let me see how I can achieve that, I think that would be a good solution.

[peterbud]

@AlexanderSehr a little help would be needed here. I have combed through the repo, and I could not find an example where the module would detect / differentiate between new deployment and deployment updates.

The closest I have found was this "upsert" module, but that would mean the input to decide whether to do a create or update deployment would come from the external source as a parameter, and that would be not useful here.

Do you know any example how this can be achieved which we could use here for such differentiation?

[AlexanderSehr]

Hey @peterbud,
as of today, there is no way to check if a resource exists (ref) - aside from maybe using a deployment script, but that would be an overkill as far as I'm concerned.
As Alex Frankel points out in the beginning of the thread, for most resources (and this includes this case) this check 'should' not be required as the deployment 'should' be idempotent. In this specific case, it 'should' work to have both the property & child-module where the former would create the administrator and the later would update it. But then again, I don't know if the property fails if you start changing anything about the administrator (i.e., if only the child resource can be used after the first deployment to update settings as described by @tony-box).

If it does not work in an idempontent way, it 'should' be considered a provider bug and be raised as such. And just to put the icing on the cake, I recently learned of another provider that was suprised that the concept of 'idempotency' should apply to IaC templates.

While the PG 'fixes' the issue (if you happen to create a bug item for this), an intermediate, be it ugly, solution would be to implement a toggle the consumer of the module could set to tell the module it's not deploying for the first time. But as we can I guess all agree, this is just working around the issue and also provides a bad user experience.

[peterbud]

Many thanks @AlexanderSehr for the quick reply. Also I have found this issue, which has ben open since 2021 (!) on the very same subject, concluding with the same outcome: the API is not idempotent currently, and all the opened issues to PG has been dismissed so far.

So I'm afraid we'll stuck with the current setup: only the server resource can set this property in an idempotent way (and hence no update), and no update via the child administrators resource is exposed.