Error running Get-AzSqlCapability, 'Value can not be null'

[rtcn2]

Description

Unable to use Get-AzSqlCapability to obtain information on skus available in region.

According to the documentation (https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/Get-AzureRmSqlCapability?view=azurermps-6.13.0) should be able to run:

 Get-AzureRmSqlCapability -LocationName "Central US"

Same fault is present using Az rather than AzureRm command,
Tried various machines and Cloud Shell, all seem to have the same issue.

Info obtained from Cloud Shell

Script/Steps for Reproduction

Get-AzureRmSqlCapability -LocationName "Central US"

Module Version

Get-Module -ListAvailable

PS Azure:\> Get-Module -ListAvailable


    Directory: /usr/local/share/powershell/Modules


ModuleType Version    Name                                PSEdition ExportedCommands
---------- -------    ----                                --------- ----------------
Script     1.1.0      Az.Accounts                         Core,Desk {Disable-AzDataCollection, Disable-AzContextAutosave, Enable-AzDataCollection, Enable-AzContextAutosave...}
Script     1.0.0      Az.Aks                              Core,Desk {Get-AzAks, New-AzAks, Remove-AzAks, Import-AzAksCredential...}
Script     1.0.0      Az.AnalysisServices                 Core,Desk {Resume-AzAnalysisServicesServer, Suspend-AzAnalysisServicesServer, Get-AzAnalysisServicesServer, Remove-AzAnalysisServicesServer...}
Script     1.0.0      Az.ApiManagement                    Core,Desk {Add-AzApiManagementRegion, Get-AzApiManagementSsoToken, New-AzApiManagementCustomHostnameConfiguration, New-AzApiManagementSystemCertificate...}
Script     1.0.0      Az.ApplicationInsights              Core,Desk {Get-AzApplicationInsights, New-AzApplicationInsights, Remove-AzApplicationInsights, Set-AzApplicationInsightsPricingPlan...}
Script     1.0.0      Az.Automation                       Core,Desk {Get-AzAutomationHybridWorkerGroup, Remove-AzAutomationHybridWorkerGroup, Get-AzAutomationJobOutputRecord, Import-AzAutomationDscNodeConfiguration...}
Script     1.0.0      Az.Batch                            Core,Desk {Remove-AzBatchAccount, Get-AzBatchAccount, Get-AzBatchAccountKeys, New-AzBatchAccount...}
Script     1.0.0      Az.Billing                          Core,Desk {Get-AzBillingInvoice, Get-AzBillingPeriod, Get-AzEnrollmentAccount, Get-AzConsumptionBudget...}
Script     1.0.0      Az.Cdn                              Core,Desk {Get-AzCdnProfile, Get-AzCdnProfileSsoUrl, New-AzCdnProfile, Remove-AzCdnProfile...}
Script     1.0.0      Az.CognitiveServices                Core,Desk {Get-AzCognitiveServicesAccount, Get-AzCognitiveServicesAccountKey, Get-AzCognitiveServicesAccountSkus, Get-AzCognitiveServicesAccountType...}
Script     1.1.0      Az.Compute                          Core,Desk {Remove-AzAvailabilitySet, Get-AzAvailabilitySet, New-AzAvailabilitySet, Update-AzAvailabilitySet...}
Script     1.0.0      Az.ContainerInstance                Core,Desk {New-AzContainerGroup, Get-AzContainerGroup, Remove-AzContainerGroup, Get-AzContainerInstanceLog}
Script     1.0.0      Az.ContainerRegistry                Core,Desk {New-AzContainerRegistry, Get-AzContainerRegistry, Update-AzContainerRegistry, Remove-AzContainerRegistry...}
Script     1.0.0      Az.DataFactory                      Core,Desk {Set-AzDataFactoryV2, Update-AzDataFactoryV2, Get-AzDataFactoryV2, Remove-AzDataFactoryV2...}
Script     1.0.0      Az.DataLakeAnalytics                Core,Desk {Get-AzDataLakeAnalyticsDataSource, New-AzDataLakeAnalyticsCatalogCredential, Remove-AzDataLakeAnalyticsCatalogCredential, Set-AzDataLakeAnalyticsC...
Script     1.0.1      Az.DataLakeStore                    Core,Desk {Get-AzDataLakeStoreTrustedIdProvider, Remove-AzDataLakeStoreTrustedIdProvider, Remove-AzDataLakeStoreFirewallRule, Set-AzDataLakeStoreTrustedIdPro...
Script     1.0.0      Az.DevTestLabs                      Core,Desk {Get-AzDtlAllowedVMSizesPolicy, Get-AzDtlAutoShutdownPolicy, Get-AzDtlAutoStartPolicy, Get-AzDtlVMsPerLabPolicy...}
Script     1.0.0      Az.Dns                              Core,Desk {Get-AzDnsRecordSet, New-AzDnsRecordConfig, Remove-AzDnsRecordSet, Set-AzDnsRecordSet...}
Script     1.1.0      Az.EventGrid                        Core,Desk {New-AzEventGridTopic, Get-AzEventGridTopic, Set-AzEventGridTopic, New-AzEventGridTopicKey...}
Script     1.0.0      Az.EventHub                         Core,Desk {New-AzEventHubNamespace, Get-AzEventHubNamespace, Set-AzEventHubNamespace, Remove-AzEventHubNamespace...}
Script     1.0.0      Az.HDInsight                        Core,Desk {Get-AzHDInsightJob, New-AzHDInsightSqoopJobDefinition, Wait-AzHDInsightJob, New-AzHDInsightStreamingMapReduceJobDefinition...}
Script     1.0.1      Az.IotHub                           Core,Desk {Add-AzIotHubKey, Get-AzIotHubEventHubConsumerGroup, Get-AzIotHubConnectionString, Get-AzIotHubJob...}
Script     1.0.0      Az.KeyVault                         Core,Desk {Add-AzKeyVaultCertificate, Update-AzKeyVaultCertificate, Stop-AzKeyVaultCertificateOperation, Get-AzKeyVaultCertificateOperation...}
Script     1.1.0      Az.LogicApp                         Core,Desk {Get-AzIntegrationAccountAgreement, Get-AzIntegrationAccountCallbackUrl, Get-AzIntegrationAccountCertificate, Get-AzIntegrationAccount...}
Script     1.0.0      Az.MachineLearning                  Core,Desk {Move-AzMlCommitmentAssociation, Get-AzMlCommitmentAssociation, Get-AzMlCommitmentPlanUsageHistory, Remove-AzMlCommitmentPlan...}
Script     1.0.0      Az.MarketplaceOrdering              Core,Desk {Get-AzMarketplaceTerms, Set-AzMarketplaceTerms}
Script     1.0.0      Az.Media                            Core,Desk {Sync-AzMediaServiceStorageKeys, Set-AzMediaServiceKey, Get-AzMediaServiceKeys, Get-AzMediaServiceNameAvailability...}
Script     1.0.0      Az.Monitor                          Core,Desk {Get-AzMetricDefinition, Get-AzMetric, Remove-AzLogProfile, Get-AzLogProfile...}
Script     1.0.0      Az.Network                          Core,Desk {Add-AzApplicationGatewayAuthenticationCertificate, Get-AzApplicationGatewayAuthenticationCertificate, New-AzApplicationGatewayAuthenticationCertif...
Script     1.0.0      Az.NotificationHubs                 Core,Desk {Get-AzNotificationHub, Get-AzNotificationHubAuthorizationRules, Get-AzNotificationHubListKeys, Get-AzNotificationHubPNSCredentials...}
Script     1.0.0      Az.OperationalInsights              Core,Desk {New-AzOperationalInsightsAzureActivityLogDataSource, New-AzOperationalInsightsCustomLogDataSource, Disable-AzOperationalInsightsLinuxCustomLogColl...
Script     1.0.0      Az.PolicyInsights                   Core,Desk {Get-AzPolicyEvent, Get-AzPolicyState, Get-AzPolicyStateSummary, Get-AzPolicyRemediation...}
Script     1.0.0      Az.PowerBIEmbedded                  Core,Desk {Remove-AzPowerBIWorkspaceCollection, Get-AzPowerBIWorkspaceCollection, Get-AzPowerBIWorkspaceCollectionAccessKeys, Get-AzPowerBIWorkspace...}
Script     1.0.0      Az.RecoveryServices                 Core,Desk {Get-AzRecoveryServicesBackupProperty, Get-AzRecoveryServicesVault, Get-AzRecoveryServicesVaultSettingsFile, New-AzRecoveryServicesVault...}
Script     1.0.0      Az.RedisCache                       Core,Desk {Remove-AzRedisCachePatchSchedule, New-AzRedisCacheScheduleEntry, Get-AzRedisCachePatchSchedule, New-AzRedisCachePatchSchedule...}
Script     1.0.0      Az.Relay                            Core,Desk {New-AzRelayNamespace, Get-AzRelayNamespace, Set-AzRelayNamespace, Remove-AzRelayNamespace...}
Script     1.1.0      Az.Resources                        Core,Desk {Get-AzProviderOperation, Remove-AzRoleAssignment, Get-AzRoleAssignment, New-AzRoleAssignment...}
Script     1.0.0      Az.ServiceBus                       Core,Desk {New-AzServiceBusNamespace, Get-AzServiceBusNamespace, Set-AzServiceBusNamespace, Remove-AzServiceBusNamespace...}
Script     1.0.0      Az.ServiceFabric                    Core,Desk {Add-AzServiceFabricApplicationCertificate, Add-AzServiceFabricClientCertificate, Add-AzServiceFabricClusterCertificate, Add-AzServiceFabricNode...}
Script     1.0.1      Az.SignalR                          Core,Desk {New-AzSignalR, Get-AzSignalR, Get-AzSignalRKey, New-AzSignalRKey...}
Script     1.0.1      Az.Sql                              Core,Desk {Get-AzSqlDatabaseTransparentDataEncryption, Get-AzSqlDatabaseTransparentDataEncryptionActivity, Set-AzSqlDatabaseTransparentDataEncryption, Get-Az...
Script     1.0.1      Az.Storage                          Core,Desk {Get-AzStorageAccount, Get-AzStorageAccountKey, New-AzStorageAccount, New-AzStorageAccountKey...}
Script     1.0.0      Az.StreamAnalytics                  Core,Desk {Get-AzStreamAnalyticsFunction, Get-AzStreamAnalyticsDefaultFunctionDefinition, New-AzStreamAnalyticsFunction, Remove-AzStreamAnalyticsFunction...}
Script     1.0.0      Az.TrafficManager                   Core,Desk {Add-AzTrafficManagerCustomHeaderToEndpoint, Remove-AzTrafficManagerCustomHeaderFromEndpoint, Add-AzTrafficManagerCustomHeaderToProfile, Remove-AzT...
Script     1.0.1      Az.Websites                         Core,Desk {Get-AzAppServicePlan, Set-AzAppServicePlan, New-AzAppServicePlan, Remove-AzAppServicePlan...}
Script     0.0.0.9    AzureAD.Standard.Preview            Desk      {Get-AzureADUserExtension, Remove-AzureADDeviceRegisteredUser, Get-AzureADApplicationServiceEndpoint, Set-AzureADObjectSetting...}
Script     0.9.3      AzurePSDrive                        Desk
Manifest   1.0.342    MicrosoftPowerBIMgmt                Desk
Binary     1.0.342    MicrosoftPowerBIMgmt.Data           Desk      {Add-PowerBIDataset, Set-PowerBITable, New-PowerBIDataset, New-PowerBITable...}
Binary     1.0.342    MicrosoftPowerBIMgmt.Profile        Desk      {Connect-PowerBIServiceAccount, Disconnect-PowerBIServiceAccount, Invoke-PowerBIRestMethod, Get-PowerBIAccessToken...}
Binary     1.0.342    MicrosoftPowerBIMgmt.Reports        Desk      {Get-PowerBIReport, New-PowerBIReport, Export-PowerBIReport, Get-PowerBIDashboard...}
Binary     1.0.342    MicrosoftPowerBIMgmt.Workspaces     Desk      {Get-PowerBIWorkspace, Add-PowerBIWorkspaceUser, Remove-PowerBIWorkspaceUser, Set-PowerBIWorkspace...}
Script     0.9.3      PSCloudShellUtility                 Desk      {Enter-AzVM, Get-AzCommand, Invoke-AzVMCommand, Enable-AzVMPSRemoting...}
Binary     0.8.1      SHiPS                               Desk
Script     21.1.18068 SqlServer                           Desk      {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlAzureAuthenticationContext...}


    Directory: /opt/microsoft/powershell/6/Modules


ModuleType Version    Name                                PSEdition ExportedCommands
---------- -------    ----                                --------- ----------------
Manifest   1.2.2.0    Microsoft.PowerShell.Archive        Desk      {Compress-Archive, Expand-Archive}
Manifest   6.1.0.0    Microsoft.PowerShell.Host           Core      {Start-Transcript, Stop-Transcript}
Manifest   6.1.0.0    Microsoft.PowerShell.Management     Core      {Add-Content, Clear-Content, Clear-ItemProperty, Join-Path...}
Manifest   6.1.0.0    Microsoft.PowerShell.Security       Core      {Get-Credential, Get-ExecutionPolicy, Set-ExecutionPolicy, ConvertFrom-SecureString...}
Manifest   6.1.0.0    Microsoft.PowerShell.Utility        Core      {Format-List, Format-Custom, Format-Table, Format-Wide...}
Script     1.1.7.2    PackageManagement                   Desk      {Find-Package, Get-Package, Get-PackageProvider, Get-PackageSource...}
Script     1.6.7      PowerShellGet                       Desk      {Find-Command, Find-DSCResource, Find-Module, Find-RoleCapability...}
Script     0.0        PSDesiredStateConfiguration         Desk      {ValidateNoNameNodeResources, Test-NodeManager, New-DscChecksum, Set-PSDefaultConfigurationDocument...}
Script     2.0.0      PSReadLine                          Desk      {Get-PSReadLineKeyHandler, Set-PSReadLineKeyHandler, Remove-PSReadLineKeyHandler, Get-PSReadLineOption...}
Binary     1.1.2      ThreadJob                           Desk      Start-ThreadJob

Environment Data

$PSVersionTable

PS Azure:> $PSVersionTable

Name Value

---

PSVersion 6.1.1
PSEdition Core
GitCommitId 6.1.1
OS Linux 4.15.0-1036-azure #38~16.04.1-Ubuntu SMP Fri Dec 7 03:21:52 UTC 2018
Platform Unix
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0`

Debug Output

...
                    "unit": "Terabytes"
                  },
                  "scaleSize": {
                    "limit": 32,
                    "unit": "Gigabytes"
                  },
                  "status": "Visible",
                  "reason": "Managed Instance creation is not available for the selected subscription type. Please try again with a different subscription, or request the subscription upgrade through Microsoft Support."
                }
              ],
              "status": "Visible",
              "reason": "Managed Instance creation is not available for the selected subscription type. Please try again with a different subscription, or request the subscription upgrade through Microsoft Support."
            }
          ],
          "status": "Visible",
          "reason": "Managed Instance creation is not available for the selected subscription type. Please try again with a different subscription, or request the subscription upgrade through Microsoft Support."
        }
      ],
      "status": "Visible",
      "reason": "Managed Instance creation is not available for the selected subscription type. Please try again with a different subscription, or request the subscription upgrade through Microsoft Support."
    }
  ],
  "status": "Available"
}


Get-AzureRmSqlCapability : Value cannot be null.
Parameter name: source
At line:1 char:1
+ Get-AzureRmSqlCapability -LocationName "Central US"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : CloseError: (:) [Get-AzSqlCapability], ArgumentNullException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.Location_Capabilities.Cmdlet.GetAzureSqlCapability

DEBUG: AzureQoSEvent: CommandName - Get-AzSqlCapability; IsSuccess - False; Duration - 00:00:03.4379794; Exception - System.ArgumentNullException: Value cannot be null.
Parameter name: source
   at System.Linq.Enumerable.Select[TSource,TResult](IEnumerable`1 source, Func`2 selector)
   at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateSupportedSLOModel(ServiceObjectiveCapability s)
   at System.Linq.Enumerable.SelectListIterator`2.ToList()
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateSupportedEditionModel(EditionCapability e)
   at System.Linq.Enumerable.SelectListIterator`2.ToList()
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateSupportedVersionsModel(ServerVersionCapability v)
   at System.Linq.Enumerable.SelectListIterator`2.ToList()
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateLocationCapabilityModel(LocationCapabilities resp)
   at Microsoft.Azure.Commands.Sql.Location_Capabilities.Cmdlet.GetAzureSqlCapability.ExecuteCmdlet()
   at Microsoft.WindowsAzure.Commands.Utilities.Common.AzurePSCmdlet.ProcessRecord();
DEBUG: Finish sending metric.
DEBUG: 10:40:37 PM - GetAzureSqlCapability end processing.
DEBUG: 10:40:37 PM - GetAzureSqlCapability end processing.

PS Azure:\> Resolve-AzError


   HistoryId: 17


Message        : Value cannot be null.
                 Parameter name: source
StackTrace     :    at System.Linq.Enumerable.Select[TSource,TResult](IEnumerable`1 source, Func`2 selector)
                    at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateSupportedSLOModel(ServiceObjectiveCapability s)
                    at System.Linq.Enumerable.SelectListIterator`2.ToList()
                    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
                    at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateSupportedEditionModel(EditionCapability e)
                    at System.Linq.Enumerable.SelectListIterator`2.ToList()
                    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
                    at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateSupportedVersionsModel(ServerVersionCapability v)
                    at System.Linq.Enumerable.SelectListIterator`2.ToList()
                    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
                    at Microsoft.Azure.Commands.Sql.Location_Capabilities.Services.AzureSqlCapabilitiesAdapter.CreateLocationCapabilityModel(LocationCapabilities resp)
                    at Microsoft.Azure.Commands.Sql.Location_Capabilities.Cmdlet.GetAzureSqlCapability.ExecuteCmdlet()
                    at Microsoft.WindowsAzure.Commands.Utilities.Common.AzurePSCmdlet.ProcessRecord()
Exception      : System.ArgumentNullException
InvocationInfo : {Get-AzSqlCapability}
Line           : Get-AzureRmSqlCapability -LocationName "Central US"
Position       : At line:1 char:1
                 + Get-AzureRmSqlCapability -LocationName "Central US"
                 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HistoryId      : 17

[maddieclayton]

@jaredmoo Can you take a look at this issue?

[jaredmoo]

Taking a look.

[johnwc]

When will this update be published to PowerShell Gallery? If not soon, can we at least get a "preview" release published? Can't use this command at all at the moment.

[jaredmoo]

Doesn't this version have the fix?

https://www.powershellgallery.com/packages/Az.Sql/1.2.0

Release Notes

• Add Get/Set AzSqlDatabaseBackupShortTermRetentionPolicy
• Fix issue where not being logged into Azure account would result in nullref exception when executing SQL cmdlets
• Fixed null ref exception in Get-AzSqlCapability

[johnwc]

Whoa!! You're right @jaredmoo, I was looking at the parent AzureRM module, it was updated three months ago. Should have remembered that the AzureRM was a parent-child reference module.

[johnwc]

@jaredmoo Wait, AzureRM does not reference Az.SQL. It references AzureRM.SQL, which still has old release. Is Az the replacement for AzureRM?

[jaredmoo]

Yes, that's right. You can see the announcement form the Azure PowerShell team here: https://azure.microsoft.com/en-us/blog/azure-powershell-cross-platform-az-module-replacing-azurerm/ :)