Open ecdemomaniaKay opened 11 months ago
binaryent
commented
11 months ago I have found exactly the same issue, only started happening recently. However, I can successfully set the SQL Administrator via the Azure Portal.
In case it is relevant, the user I'm trying to set is a Managed Identity. Please let me know if this gets resolved.
jordi2k
commented
10 months ago Seems the new API call does not send the property "administratorType": "ActiveDirectory" which the old version did. You can use the REST API to do this, or wait until a fix. Issue persists in Az.Sql 4.12.0 which is what I tested with locally. Luckily the Windows-2022 DevOps Agent uses older 4.2.0 for now.
wmboyles
commented
8 months ago This issue still seems to be happening in Az.Sql 4.13.0. I can get around it by calling az sql server ad-admin create
aavdberg
commented
8 months ago Have the same problem with version 4.12.0
jabeci
commented
7 months ago 4.14.0 same problem. Is there no resolution?
shahidsayyed89
commented
7 months ago Use az module version 9.3.0 in azure powershell task which uses az.sql 4.12.0
DanielOverdevest
commented
7 months ago With the upgrade of Az module in windows-2022 image this problem becomes more urgent. See release: [Ubuntu, Windows] Az Powershell module will be updated to v11.3.1 on March 18 #9445 @azureSQLGitHub can you give this issue more priority to fix? It seems an easy fix mentioned by @jordi2k .
nofield
commented
6 months ago Thank you, folks, for reporting this issue! The SQL Identity and Authentication team has identified the problem and solution. We're working to deploy the fix as soon as possible; will keep you posted when that goes live.
habibhammy
commented
5 months ago Hi all, Any updates on the subject ? I have the same issue using the 4.14.1 version of the Az.SQL module (V11.6.0 of the Az module). Many Thanks.
mvvsubbu
commented
5 months ago @nofield this issue is hitting our team as well, do you have any ETA on when the fix is going to be deployed?
fast-it-solutions
commented
5 months ago @nofield any ETA?
nofield
commented
5 months ago Folks, this fix has now been fully deployed worldwide! The fix should not require any library changes; the command should just now work. If anyone is still experiencing this issue, please let us know, thanks!
DanielOverdevest
commented
5 months ago Dear @nofield, thanks for the update and the fix. Will test it this week if now all runs smooth.
AlexNolasco
commented
4 months ago Folks, this fix has now been fully deployed worldwide! The fix should not require any library changes; the command should just now work. If anyone is still experiencing this issue, please let us know, thanks!
Odd, I just tried it and it still ails
(InvalidResourceIdSegment) The 'parameters.properties.sid' segment in the url is invalid.
Code: InvalidResourceIdSegment
Message: The 'parameters.properties.sid' segment in the url is invalid.
Exception Details: (InvalidResourceIdSegment)
Code: InvalidResourceIdSegment
Message:
Target: parameters.properties.sid
core 2.61.0
telemetry 1.1.0
Dependencies:
msal 1.28.0
azure-mgmt-resource 23.1.1Steps suggested from https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cefcore%2Cdotnet
nofield
commented
2 months ago Hi @AlexNolasco, the error message you shared has a different signature than the original one on this issue- it's the SID (Security Identifier) that is not being properly assigned in your request. Our change removed a previous requirement of including the AdministratorType in the request, which is not necessary. The SID, however, is very necessary. You must provide the correct object ID / client ID for the Microsoft Entra identity you're trying to assign as the admin.
Also, I'm not seeing where in the tutorial you linked it talks about powershell, could you share more details?
jabeci
commented
2 months ago I would say it's fixed even though I now have a new error.
nofield
commented
2 months ago @jabeci what's the error you're getting now?
jabeci
commented
2 months ago @jabeci what's the error you're getting now?
Set-AzSqlServerActiveDirectoryAdministrator : Operation returned an invalid status code 'Forbidden'
ecdemomaniaKay
commented
2 months ago I just tested with Ac 12.2.0 & Az.SQL 5.2.0 and no more errors. Thanks!
jabeci
commented
2 months ago @jabeci what's the error you're getting now?
Could this be why?
Microsoft Entra authentication only
Only Microsoft Entra ID will be used to authenticate to the server. SQL authentication will be disabled, including SQL Server administrators and users. Learn more
Support only Microsoft Entra authentication for this server
devdeer-alex
commented
1 month ago @jabeci Probably not. I've got the same problem in BICEP deployments. If I pass in a valid SID but leave azureADOnlyAuthentication set to false it'll bring the segment error. This does not change when I set the option to true.
I think this is the same issue and is related to MS Graph. So only the portal can do this currently.
BTW the Bicep error message is the same:
The 'parameters.properties.administrators.sid' segment in the url is invalid. (Code: InvalidResourceIdSegment)
Whats also interesting to me is the fact that when you look into the JSON View in Azure portal you'll see something like this after the settingg is applied manually (e.g. in the portal):
"kind": "v12.0",
"properties": {
"administrators": {
"administratorType": "ActiveDirectory",
"principalType": "Group",
"login": "MyGroupName",
"sid": "[OBJECTID_AS_GUID_AND_NOT_THE_SID]",
"tenantId": "[GUID_OF_TENANT]"
},
}So after the setting is applied correctly it will NOT show the SID but the Object ID (GUID) of the Entra object (a group in my case). This is inconclusive IMHO. I've tried to pass the object id in my BICEP which also fails with another error:
The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'
This is the BICEP version of "I don't know what happened but its bad".
Both error messages (the one with the segments and the last one with terminal state) will disappear in BICEP if the setting was previously made in the portal! So no idempotency here as well.
After one applied the setting in the portal it will nowhere show you the SID at any stage of the process. I'm guessing that this points to the issue that SID is a relict from Windows-times and they broke something in Graph during the latest switches and clearings towards "Entra". But I could be totally wrong as I already often was 😒
Hope this helps somehow.
nofield
commented
2 weeks ago Hey @devdeer-alex , @jabeci ,
After one applied the setting in the portal it will nowhere show you the SID at any stage of the process.
The GUID shown next to the Admin name in the Azure portal is the "sid" as defined by the APIs:
The Administrators API allow you to use either the object ID of your identity, or the client ID in the case of an application (or managed identity), to specify which identity in the tenant you are selecting for your admin.
The error you are getting is different than the original error message, the segment is important: The 'parameters.properties.administrators.sid' segment in the url is invalid. (Code: InvalidResourceIdSegment)
I'd request that you ensure the GUID you're passing in your bicep / ARM templates is exactly the Microsoft Entra object ID or client ID of the identity you're attempting to set as the admin.
Both error messages (the one with the segments and the last one with terminal state) will disappear in BICEP if the setting was previously made in the portal! So no idempotency here as well.
I'm curious to look into this, we may not be executing/validating the entirety of the Administrators segment of the request if we have some indication the administrator hasn't been changed (e.g., the name hasn't changed).
Description
When running Set-AzSqlServerActiveDirectoryAdministrator command, I am getting error
The 'parameters.properties' segment in the url is invalidwhen using Az.Sql 4.11.0.Running the command with exact same parameters using Az.Sql 4.10.0 is fine. No errors and works as expected.
Issue script & Debug output
Environment data
Module versions
Error output