search

Azure / azure-powershell

Microsoft Azure PowerShell
Other
4.26k stars 3.86k forks source link

Set-AzSqlServerActiveDirectoryAdministrator not passing api-version in AD lookup #16612

Open allencasteran opened 2 years ago

allencasteran commented 2 years ago

Description

Calling Set-AzSqlServerActiveDirectoryAdministrator to set the AD Administrator to an AD group is failing with a "BadRequest"

Debug output shows the second REST call within the process is not including the api-version param and failing.

Testing the REST call in a browser duplicates the error. After adding the api-version to the REST call in the browser it no longer reports the missing api-version error but reports Access token missing or malformed as expected for that test.

Issue script & Debug output

Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $RG -ServerName $AzSQLInstance -DisplayName "$AzSQLADGroup" -Debug

DEBUG: ============================ HTTP REQUEST ============================

HTTP Method:
GET

Absolute Uri:
https://management.azure.com/subscriptions/**redacted**/resourceGroups/**redacted**/providers/Microsoft.Sql/servers/**redacted**/administrators/ActiveDirectory?api-version=2020-11-01-preview

Headers:
x-ms-client-request-id        : ae2affdf-9b59-4865-bccf-6e7a7dd6ff8b
Accept-Language               : en-US

Body:

DEBUG: ============================ HTTP RESPONSE ============================

Status Code:
NotFound

Headers:
Cache-Control                 : no-cache
Pragma                        : no-cache
x-ms-request-id               : 3a529398-14f9-4b10-a77b-3c5646018b85
Server                        : Microsoft-HTTPAPI/2.0
x-ms-ratelimit-remaining-subscription-reads: 11999
x-ms-correlation-request-id   : 6b9c0d71-5a7a-4009-b61a-917bb63b5b22
x-ms-routing-request-id       : CENTRALUS:20211209T175358Z:6b9c0d71-5a7a-4009-b61a-917bb63b5b22
Strict-Transport-Security     : max-age=31536000; includeSubDomains
X-Content-Type-Options        : nosniff
Date                          : Thu, 09 Dec 2021 17:53:58 GMT

Body:
{
  "error": {
    "code": "ResourceNotFound",
    "message": "The requested resource of type 'Microsoft.Sql/servers/administrators' with name 'ActiveDirectory' was not found."
  }
}

DEBUG: [Common.Authentication]: Authenticating using Account: 'ac@blah.com', environment: 'AzureCloud', tenant: **redacted**
DEBUG: 12:54:03 PM - [SilentAuthenticator] Calling SharedTokenCacheCredential.GetTokenAsync - TenantId:**redacted**, Scopes:'https://graph.windows.net//.default', AuthorityHost:'https://login.microsoftonline.com/', UserId:**redacted**
DEBUG: SharedTokenCacheCredential.GetToken invoked. Scopes: [ https://graph.windows.net//.default ] ParentRequestId:
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Azure region was not configured or could not be discovered. Not using a regional authority.
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03] Found 1 cache accounts and 0 broker accounts
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03] Returning 1 accounts
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] MSAL MSAL.NetCore with assembly version '4.30.1.0'. CorrelationId(50b96244-dbb3-4c5f-bccb-81259da3fda6)
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] === AcquireTokenSilent Parameters ===
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] LoginHint provided: False
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Account provided: True
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] ForceRefresh: False
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ]
=== Request Data ===
Authority Provided? - True
Scopes - https://graph.windows.net//.default
Extra Query Params Keys (space separated) -
ApiId - AcquireTokenSilent
IsConfidentialClient - False
SendX5C - False
LoginHint ? False
IsBrokerConfigured - False
HomeAccountId - False
CorrelationId - **redacted**

DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] === Token Acquisition (SilentRequest) started:

        Authority Host: login.microsoftonline.com
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Azure region was not configured or could not be discovered. Not using a regional authority.
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Access token is not expired. Returning the found cache entry. [Current time (12/09/2021 17:54:03) - Expiration Time (12/09/2021 18:16:28 +00:00) - Extended Expiration Time (12/09/2021 18:16:28 +00:00)]
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Returning access token found in cache. RefreshOn exists ? False
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Fetched access token from host login.microsoftonline.com.
DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] === Token Acquisition finished successfully. An access token was returned with Expiration Time: 12/09/2021 18:16:28 +00:00 and Scopes https://graph.windows.net//**redacted** https://graph.windows.net//.default
DEBUG: SharedTokenCacheCredential.GetToken succeeded. Scopes: [ https://graph.windows.net//.default ] ParentRequestId:  ExpiresOn: 2021-12-09T18:16:28.0000000+00:00
DEBUG: [Common.Authentication]: Received token with LoginType 'User', Tenant: **redacted**, UserId: **redacted**
DEBUG: ============================ HTTP REQUEST ============================

HTTP Method:
GET

Absolute Uri:
https://graph.windows.net/v1.0/groups?$filter=displayName eq %27Group Name%27

Headers:
x-ms-client-request-id        : ae2affdf-9b59-4865-bccf-6e7a7dd6ff8b
Accept-Language               : en-US

Body:

DEBUG: ============================ HTTP RESPONSE ============================

Status Code:
BadRequest

Headers:
ocp-aad-diagnostics-server-name: bfof8cxltiEHPAJGPnKMvwOVeCDvbRCDeDFM/iqvJrE=
request-id                    : c865ac98-2fab-49fe-9f2b-97e3c426fab1
client-request-id             : ae2affdf-9b59-4865-bccf-6e7a7dd6ff8b
X-Powered-By                  : ASP.NET
Strict-Transport-Security     : max-age=31536000; includeSubDomains
Access-Control-Allow-Origin   : *
Duration                      : 179976
Date                          : Thu, 09 Dec 2021 17:54:02 GMT

Body:
{
  "odata.error": {
    "code": "Request_DataContractVersionMissing",
    "message": {
      "lang": "en",
      "value": "The specified api-version is invalid. The value must exactly match a supported version."
    }
  }
}

Set-AzSqlServerActiveDirectoryAdministrator: Operation returned an invalid status code 'BadRequest'
DEBUG: AzureQoSEvent: Module: Az.Sql:3.6.0; CommandName: Set-AzSqlServerActiveDirectoryAdministrator; PSVersion: 7.1.2; IsSuccess: False; Duration: 00:00:09.9041982; Exception: Operation returned an invalid status code 'BadRequest';
DEBUG: Finish sending metric.
DEBUG: 12:54:04 PM - SetAzureSqlServerActiveDirectoryAdministrator end processing.

Environment data

Name                           Value
----                           -----
PSVersion                      7.1.2
PSEdition                      Core
GitCommitId                    7.1.2
OS                             Microsoft Windows 10.0.14393
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Module versions

ModuleType Version    PreRelease Name                                ExportedCommands
---------- -------    ---------- ----                                ----------------
Script     2.7.0                 Az.Accounts                         {Add-AzEnvironment, Clear-AzContext, Clear-AzDefa…
Script     3.6.0                 Az.Sql                              {Add-AzSqlDatabaseToFailoverGroup, Add-AzSqlElast…

Error output

Message        : Operation returned an invalid status code 'BadRequest'
StackTrace     :    at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.GroupsOperations.ListGroupWithHttpMess
                 agesAsync(String consistencyLevel, Nullable`1 top, Nullable`1 skip, String search, String filter,
                 Nullable`1 count, IList`1 orderby, IList`1 select, IList`1 expand, Dictionary`2 customHeaders,
                 CancellationToken cancellationToken)
                    at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.GroupsOperationsExtensions.ListGroupAs
                 ync(IGroupsOperations operations, String consistencyLevel, Nullable`1 top, Nullable`1 skip, String
                 search, String filter, Nullable`1 count, IList`1 orderby, IList`1 select, IList`1 expand,
                 CancellationToken cancellationToken)
                    at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.GroupsOperationsExtensions.ListGroup(I
                 GroupsOperations operations, String consistencyLevel, Nullable`1 top, Nullable`1 skip, String search,
                 String filter, Nullable`1 count, IList`1 orderby, IList`1 select, IList`1 expand)
                    at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.MicrosoftGraphClientExtensions.FilterGroups(I
                 MicrosoftGraphClient client, MicrosoftObjectFilterOptions options)
                    at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Services.AzureSqlServerActiveDir
                 ectoryAdministratorAdapter.GetActiveDirectoryInformation(String displayName, Guid objectId)
                    at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Services.AzureSqlServerActiveDir
                 ectoryAdministratorAdapter.UpsertServerActiveDirectoryAdministrator(String resourceGroup, String
                 serverName, AzureSqlServerActiveDirectoryAdministratorModel model)
                    at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Cmdlet.SetAzureSqlServerActiveDi
                 rectoryAdministrator.PersistChanges(IEnumerable`1 entity)
                    at
                 Microsoft.Azure.Commands.Sql.Common.AzureSqlCmdletBase`2.<>c__DisplayClass16_0.<ExecuteCmdlet>b__0()
                    at Microsoft.WindowsAzure.Commands.Utilities.Common.AzurePSCmdlet.ConfirmAction(String
                 processMessage, String target, Action action)
                    at Microsoft.Azure.Commands.Sql.Common.AzureSqlCmdletBase`2.ExecuteCmdlet()
                    at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Cmdlet.AzureSqlServerActiveDirec
                 toryAdministratorCmdletBase.ExecuteCmdlet()
                    at Microsoft.WindowsAzure.Commands.Utilities.Common.CmdletExtensions.<>c__3`1.<ExecuteSynchronously
                 OrAsJob>b__3_0(T c)
                    at
                 Microsoft.WindowsAzure.Commands.Utilities.Common.CmdletExtensions.ExecuteSynchronouslyOrAsJob[T](T
                 cmdlet, Action`1 executor)
                    at
                 Microsoft.WindowsAzure.Commands.Utilities.Common.CmdletExtensions.ExecuteSynchronouslyOrAsJob[T](T
                 cmdlet)
                    at Microsoft.WindowsAzure.Commands.Utilities.Common.AzurePSCmdlet.ProcessRecord()
Exception      : Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.Models.OdataErrorException
InvocationInfo : {Set-AzSqlServerActiveDirectoryAdministrator}
Line           : Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $RG -ServerName $AzSQLInstance
                 -DisplayName "$AzSQLADGroup" -Debug
Position       : At line:1 char:1
                 + Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $RG -S …
                 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
dingmeng-xue commented 2 years ago

Thanks for reporting. We believe this inconsistency should be resolve by service side. I reply team member to look into it further.

SatishBoddu-MSFT commented 2 years ago

Hello @allencasteran Sorry for the delayed response. Just checking if you were able to use the AD security group in this case as per the requirement?

https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqlserveractivedirectoryadministrator?view=azps-7.0.0#description image

allencasteran commented 2 years ago

The example given came from the attempt to set an AD security group via the Powershell command. The same group can be set via the Azure Portal UI.
As shown in the examples, the last REST call made by the module was not formed correctly and missing the api-version param.

ghost commented 2 years ago

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @azureSQLGitHub.

Issue Details
### Description Calling Set-AzSqlServerActiveDirectoryAdministrator to set the AD Administrator to an AD group is failing with a "BadRequest" Debug output shows the second REST call within the process is not including the api-version param and failing. Testing the REST call in a browser duplicates the error. After adding the api-version to the REST call in the browser it no longer reports the missing api-version error but reports Access token missing or malformed as expected for that test. ### Issue script & Debug output ```PowerShell Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $RG -ServerName $AzSQLInstance -DisplayName "$AzSQLADGroup" -Debug DEBUG: ============================ HTTP REQUEST ============================ HTTP Method: GET Absolute Uri: https://management.azure.com/subscriptions/**redacted**/resourceGroups/**redacted**/providers/Microsoft.Sql/servers/**redacted**/administrators/ActiveDirectory?api-version=2020-11-01-preview Headers: x-ms-client-request-id : ae2affdf-9b59-4865-bccf-6e7a7dd6ff8b Accept-Language : en-US Body: DEBUG: ============================ HTTP RESPONSE ============================ Status Code: NotFound Headers: Cache-Control : no-cache Pragma : no-cache x-ms-request-id : 3a529398-14f9-4b10-a77b-3c5646018b85 Server : Microsoft-HTTPAPI/2.0 x-ms-ratelimit-remaining-subscription-reads: 11999 x-ms-correlation-request-id : 6b9c0d71-5a7a-4009-b61a-917bb63b5b22 x-ms-routing-request-id : CENTRALUS:20211209T175358Z:6b9c0d71-5a7a-4009-b61a-917bb63b5b22 Strict-Transport-Security : max-age=31536000; includeSubDomains X-Content-Type-Options : nosniff Date : Thu, 09 Dec 2021 17:53:58 GMT Body: { "error": { "code": "ResourceNotFound", "message": "The requested resource of type 'Microsoft.Sql/servers/administrators' with name 'ActiveDirectory' was not found." } } DEBUG: [Common.Authentication]: Authenticating using Account: 'allen.casteran@markel.com', environment: 'AzureCloud', tenant: **redacted** DEBUG: 12:54:03 PM - [SilentAuthenticator] Calling SharedTokenCacheCredential.GetTokenAsync - TenantId:**redacted**, Scopes:'https://graph.windows.net//.default', AuthorityHost:'https://login.microsoftonline.com/', UserId:**redacted** DEBUG: SharedTokenCacheCredential.GetToken invoked. Scopes: [ https://graph.windows.net//.default ] ParentRequestId: DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Azure region was not configured or could not be discovered. Not using a regional authority. DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03] Found 1 cache accounts and 0 broker accounts DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03] Returning 1 accounts DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] MSAL MSAL.NetCore with assembly version '4.30.1.0'. CorrelationId(50b96244-dbb3-4c5f-bccb-81259da3fda6) DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] === AcquireTokenSilent Parameters === DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] LoginHint provided: False DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Account provided: True DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] ForceRefresh: False DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] === Request Data === Authority Provided? - True Scopes - https://graph.windows.net//.default Extra Query Params Keys (space separated) - ApiId - AcquireTokenSilent IsConfidentialClient - False SendX5C - False LoginHint ? False IsBrokerConfigured - False HomeAccountId - False CorrelationId - **redacted** DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] === Token Acquisition (SilentRequest) started: Authority Host: login.microsoftonline.com DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Azure region was not configured or could not be discovered. Not using a regional authority. DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Access token is not expired. Returning the found cache entry. [Current time (12/09/2021 17:54:03) - Expiration Time (12/09/2021 18:16:28 +00:00) - Extended Expiration Time (12/09/2021 18:16:28 +00:00)] DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Returning access token found in cache. RefreshOn exists ? False DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] Fetched access token from host login.microsoftonline.com. DEBUG: (False) MSAL 4.30.1.0 MSAL.NetCore Microsoft Windows 10.0.14393 [12/09/2021 17:54:03 - ] === Token Acquisition finished successfully. An access token was returned with Expiration Time: 12/09/2021 18:16:28 +00:00 and Scopes https://graph.windows.net//**redacted** https://graph.windows.net//.default DEBUG: SharedTokenCacheCredential.GetToken succeeded. Scopes: [ https://graph.windows.net//.default ] ParentRequestId: ExpiresOn: 2021-12-09T18:16:28.0000000+00:00 DEBUG: [Common.Authentication]: Received token with LoginType 'User', Tenant: **redacted**, UserId: **redacted** DEBUG: ============================ HTTP REQUEST ============================ HTTP Method: GET Absolute Uri: https://graph.windows.net/v1.0/groups?$filter=displayName eq %27Group Name%27 Headers: x-ms-client-request-id : ae2affdf-9b59-4865-bccf-6e7a7dd6ff8b Accept-Language : en-US Body: DEBUG: ============================ HTTP RESPONSE ============================ Status Code: BadRequest Headers: ocp-aad-diagnostics-server-name: bfof8cxltiEHPAJGPnKMvwOVeCDvbRCDeDFM/iqvJrE= request-id : c865ac98-2fab-49fe-9f2b-97e3c426fab1 client-request-id : ae2affdf-9b59-4865-bccf-6e7a7dd6ff8b X-Powered-By : ASP.NET Strict-Transport-Security : max-age=31536000; includeSubDomains Access-Control-Allow-Origin : * Duration : 179976 Date : Thu, 09 Dec 2021 17:54:02 GMT Body: { "odata.error": { "code": "Request_DataContractVersionMissing", "message": { "lang": "en", "value": "The specified api-version is invalid. The value must exactly match a supported version." } } } Set-AzSqlServerActiveDirectoryAdministrator: Operation returned an invalid status code 'BadRequest' DEBUG: AzureQoSEvent: Module: Az.Sql:3.6.0; CommandName: Set-AzSqlServerActiveDirectoryAdministrator; PSVersion: 7.1.2; IsSuccess: False; Duration: 00:00:09.9041982; Exception: Operation returned an invalid status code 'BadRequest'; DEBUG: Finish sending metric. DEBUG: 12:54:04 PM - SetAzureSqlServerActiveDirectoryAdministrator end processing. ``` ### Environment data ```PowerShell Name Value ---- ----- PSVersion 7.1.2 PSEdition Core GitCommitId 7.1.2 OS Microsoft Windows 10.0.14393 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0 ``` ### Module versions ```PowerShell ModuleType Version PreRelease Name ExportedCommands ---------- ------- ---------- ---- ---------------- Script 2.7.0 Az.Accounts {Add-AzEnvironment, Clear-AzContext, Clear-AzDefa… Script 3.6.0 Az.Sql {Add-AzSqlDatabaseToFailoverGroup, Add-AzSqlElast… ``` ### Error output ```PowerShell Message : Operation returned an invalid status code 'BadRequest' StackTrace : at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.GroupsOperations.ListGroupWithHttpMess agesAsync(String consistencyLevel, Nullable`1 top, Nullable`1 skip, String search, String filter, Nullable`1 count, IList`1 orderby, IList`1 select, IList`1 expand, Dictionary`2 customHeaders, CancellationToken cancellationToken) at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.GroupsOperationsExtensions.ListGroupAs ync(IGroupsOperations operations, String consistencyLevel, Nullable`1 top, Nullable`1 skip, String search, String filter, Nullable`1 count, IList`1 orderby, IList`1 select, IList`1 expand, CancellationToken cancellationToken) at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.GroupsOperationsExtensions.ListGroup(I GroupsOperations operations, String consistencyLevel, Nullable`1 top, Nullable`1 skip, String search, String filter, Nullable`1 count, IList`1 orderby, IList`1 select, IList`1 expand) at Microsoft.Azure.Commands.Common.MSGraph.Version1_0.MicrosoftGraphClientExtensions.FilterGroups(I MicrosoftGraphClient client, MicrosoftObjectFilterOptions options) at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Services.AzureSqlServerActiveDir ectoryAdministratorAdapter.GetActiveDirectoryInformation(String displayName, Guid objectId) at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Services.AzureSqlServerActiveDir ectoryAdministratorAdapter.UpsertServerActiveDirectoryAdministrator(String resourceGroup, String serverName, AzureSqlServerActiveDirectoryAdministratorModel model) at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Cmdlet.SetAzureSqlServerActiveDi rectoryAdministrator.PersistChanges(IEnumerable`1 entity) at Microsoft.Azure.Commands.Sql.Common.AzureSqlCmdletBase`2.<>c__DisplayClass16_0.b__0() at Microsoft.WindowsAzure.Commands.Utilities.Common.AzurePSCmdlet.ConfirmAction(String processMessage, String target, Action action) at Microsoft.Azure.Commands.Sql.Common.AzureSqlCmdletBase`2.ExecuteCmdlet() at Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Cmdlet.AzureSqlServerActiveDirec toryAdministratorCmdletBase.ExecuteCmdlet() at Microsoft.WindowsAzure.Commands.Utilities.Common.CmdletExtensions.<>c__3`1.b__3_0(T c) at Microsoft.WindowsAzure.Commands.Utilities.Common.CmdletExtensions.ExecuteSynchronouslyOrAsJob[T](T cmdlet, Action`1 executor) at Microsoft.WindowsAzure.Commands.Utilities.Common.CmdletExtensions.ExecuteSynchronouslyOrAsJob[T](T cmdlet) at Microsoft.WindowsAzure.Commands.Utilities.Common.AzurePSCmdlet.ProcessRecord() Exception : Microsoft.Azure.Commands.Common.MSGraph.Version1_0.Groups.Models.OdataErrorException InvocationInfo : {Set-AzSqlServerActiveDirectoryAdministrator} Line : Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $RG -ServerName $AzSQLInstance -DisplayName "$AzSQLADGroup" -Debug Position : At line:1 char:1 + Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName $RG -S … + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ```
Author: allencasteran
Assignees: -
Labels: `SQL`, `Service Attention`, `bug`, `question`, `customer-reported`
Milestone: -
SatishBoddu-MSFT commented 2 years ago

Re-Directing to the Service team for further action on this issue!

hanshoekselwerk commented 2 years ago

Hi @SatishBoddu-MSFT , is there any information when this will be resolved ?

baaschi commented 2 years ago

This issue hit use after Az 7. was released on Microsoft Hosted Azure agents. I hope there will be a fix for this soon.

rselie commented 2 years ago

I face still this issue. Is there already any news?

g-raskar commented 2 years ago

I am also facing this issue. However, pinning Az.Sql to version 3.5.0 works. ` Install-Module -Name Az.Sql -RequiredVersion 3.5.0 -Force

Import-Module SQLServer

Import-Module -Name Az.Sql -RequiredVersion 3.5.0 `

catalinpopa247 commented 2 years ago

I can also confirm that this issue is still happening. It would be great if this would be addressed properly soon.

Mwalima commented 2 years ago

I have the same error in pipeline deployment. Set-AzSqlServerActiveDirectoryAdministrator : Operation returned an invalid status code 'BadRequest' 2022-03-05T15:41:23.0027044Z ##[debug]At D:\a\1\s\RGDeployment\RGDeployment\Scripts\Modules\Get-SQLFunctions.psm1:115 char:5 2022-03-05T15:41:23.0059534Z ##[debug]+ Set-AzSqlServerActiveDirectoryAdministrator -ServerName $AzureSQL ... 2022-03-05T15:41:23.0104675Z ##[debug]+ ~~~~~~~~~~~~~ 2022-03-05T15:41:23.0126404Z ##[debug] + CategoryInfo : CloseError: (:) [Set-AzSqlServer...ryAdministrator], OdataErrorException 2022-03-05T15:41:23.0147351Z ##[debug] + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ServerActiveDirectoryAdministrator.Cmdlet.SetAzureSqlServer ActiveDirectoryAdministrator

Mwalima commented 2 years ago

I had 'C:\Modules\az_7.1.0\Az.Sql\3.7.0\Microsoft.Azure.PowerShell.Cmdlets.Sql.dll'. installed in the pipeline. now reverting to version 3.5 fixed the issue in the yml.

- stage: 'Artifact'
    displayName: 'Artifact Build'
    dependsOn: TestARMTemplates
    jobs: 
      - template: ${{ variables.artifactTemplate }}
      - job: 
        steps:
        - task: AzurePowerShell@5
          inputs:
            azureSubscription: '###############'
            ScriptType: 'InlineScript'
            Inline: 'Install-Module -Name Az.Sql -RequiredVersion 3.5.0 -Force'
            FailOnStandardError: true
            azurePowerShellVersion: 'LatestVersion'
JCDeist commented 2 years ago

@azureSQLGitHub Is this being looked at or reviewed? We are experiencing this issue as well and forcing version 3.5 of az.sql is less than ideal.

briansmyk commented 2 years ago

This was resolved in AZ 7.5.0 which has Az.Sql v 3.8.0