search

Azure / azure-powershell

Microsoft Azure PowerShell
Other
4.27k stars 3.87k forks source link

New-SqlColumnEncryptionKey fails with generic error on first execution #7195

Open Adrian89 opened 6 years ago

Adrian89 commented 6 years ago

Description

I'm having a problem using SQL Server Always Encrypted with Azure Sql Databases. I have an Azure Key Vault set up and the powershell script to create the CMK executes without error. The powershell script to create the CEK fails when executing the cmdlet 'New-SqlColumnEncryptionKey'.

The error output is: 

One or more errors occurred.
An Error Occurred
New-SqlColumnEncryptionKey : One or more errors occurred.
At C:\users\<username>\desktop\2-GenerateColumnKeys.ps1:87 char:4
+             New-SqlColumnEncryptionKey -Name $cekName -InputObject $d ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [New-SqlColumnEncryptionKey], AggregateException
    + FullyQualifiedErrorId : ReflectionError,Microsoft.SqlServer.Management.PowerShell.AlwaysEncrypted.NewSqlColumnEn
   cryptionKey

Script/Steps for Reproduction

The following script fails the first time you run it. If you run it again in the same powershell window, it will execute successfully. We're using VSTS to orchestrate the execution of this script so it needs to run first time.

I have also created an Azure VM with Windows Server 2016 on it, installed sql-server module and ran the script. The same error occurred


<#
.Synopsis

.DESCRIPTION
   NA
.EXAMPLE
   NA
#>
param
(

[Parameter(Mandatory=$true)]     
[string] $sqlServer,

[Parameter(Mandatory=$true)]     
[string] $databaseName,

[Parameter(Mandatory=$False)] 
[string] $sqlDbUserName, 

[Parameter(Mandatory=$False)]
[string] $sqlDbPassword,

[Parameter(Mandatory=$False)]
[string] $clientId ,

[Parameter(Mandatory=$False)]
[string] $secret,

[Parameter(Mandatory=$False)]
[string] $tenent

)

Begin
{

       function Setup-ColumnKeys([string] $sqlServer, [string] $databaseName, [string] $sqlDbUserName, [string] $sqlDbPassword, [string] $clientId, [string] $secret, [string] $tenent)
       {

              Import-Module -Name SqlServer

              $connStr = "server=$sqlServer; database=$databaseName; User Id=$sqlDbUserName; Password=$sqlDbPassword;"

              $connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

              $connection.ConnectionString = $connStr

              $connection.Connect()

              $server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)

              $database = $server.Databases[$databaseName]

              $cekName = "CEK"

              $cmkName = "CMK"

             Add-SqlAzureAuthenticationContext -ClientId $clientId -Secret $secret -Tenant $tenent
Write-Host -ForegroundColor Green "Creating CEK"
              New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKeyName $cmkName -Verbose
              Write-Host -ForegroundColor Green "Column key created."

       }

       $ErrorActionPreference = 'Stop'

       try{
              Write-Host -ForegroundColor Green "Executing Generate Column Keys"
              Setup-ColumnKeys -sqlServer $sqlServer -databaseName $databaseName -sqlDbUserName "$sqlDbUserName" -sqlDbPassword "$sqlDbPassword" -clientId "$clientId" -secret "$secret" -tenent "$tenent"
              Write-Host -ForegroundColor Green "Complete - No Errors"
       } catch {
              Write-Host -ForegroundColor Red $_
              Write-Host -ForegroundColor Red "An Error Occurred"
              throw $_
       }      

}

Workaround

Current workaround it to put a try catch block round the cmdlet and repeat the step:

$cekName = "CEK"
try { 
Add-SqlAzureAuthenticationContext -ClientId $clientid -Secret $secret -Tenant $tenant 
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKeyName $cmkName  
} catch { 
      Add-SqlAzureAuthenticationContext -ClientId $clientid -Secret $secret -Tenant $tenant 
       New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKeyName $cmkName  
}

Module Version

Get-Module -ListAvailable

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Script     5.1.2      Azure                               {Get-AzureAutomationCertificate, Get-AzureAutomationConnec...
Manifest   0.5.0      Azure.AnalysisServices              {Add-AzureAnalysisServicesAccount, Restart-AzureAnalysisSe...
Script     0.4.0      Azure.AnalysisServices              {Add-AzureAnalysisServicesAccount, Restart-AzureAnalysisSe...
Script     4.2.1      Azure.Storage                       {Get-AzureStorageTable, New-AzureStorageTableSASToken, New...
Binary     2.0.0.131  AzureAD                             {Add-AzureADApplicationOwner, Get-AzureADApplication, Get-...
Script     0.1        AzureDiagnosticsAndLogAnalytics     {Add-AzureDiagnosticsToLogAnalytics, Add-AzureDiagnosticsT...
Script     5.7.0      AzureRM
Script     0.6.6      AzureRM.AnalysisServices            {Resume-AzureRmAnalysisServicesServer, Suspend-AzureRmAnal...
Script     5.1.2      AzureRM.ApiManagement               {Add-AzureRmApiManagementRegion, Get-AzureRmApiManagementS...
Script     0.1.3      AzureRM.ApplicationInsights         {Get-AzureRmApplicationInsights, New-AzureRmApplicationIns...
Script     4.3.2      AzureRM.Automation                  {Get-AzureRMAutomationHybridWorkerGroup, Get-AzureRmAutoma...
Script     4.0.4      AzureRM.Backup                      {Backup-AzureRmBackupItem, Enable-AzureRmBackupContainerRe...
Script     4.0.6      AzureRM.Batch                       {Remove-AzureRmBatchAccount, Get-AzureRmBatchAccount, Get-...
Script     0.14.1     AzureRM.Billing                     {Get-AzureRmBillingInvoice, Get-AzureRmBillingPeriod, Get-...
Script     4.2.2      AzureRM.Cdn                         {Get-AzureRmCdnProfile, Get-AzureRmCdnProfileSsoUrl, New-A...
Script     0.9.4      AzureRM.CognitiveServices           {Get-AzureRmCognitiveServicesAccount, Get-AzureRmCognitive...
Script     4.6.0      AzureRM.Compute                     {Remove-AzureRmAvailabilitySet, Get-AzureRmAvailabilitySet...
Script     0.3.1      AzureRM.Consumption                 Get-AzureRmConsumptionUsageDetail
Script     0.2.5      AzureRM.ContainerInstance           {New-AzureRmContainerGroup, Get-AzureRmContainerGroup, Rem...
Script     1.0.4      AzureRM.ContainerRegistry           {New-AzureRmContainerRegistry, Get-AzureRmContainerRegistr...
Script     4.2.2      AzureRM.DataFactories               {Remove-AzureRmDataFactory, Get-AzureRmDataFactoryRun, Get...
Script     0.5.3      AzureRM.DataFactoryV2               {Set-AzureRmDataFactoryV2, Update-AzureRmDataFactoryV2, Ge...
Script     4.2.3      AzureRM.DataLakeAnalytics           {Get-AzureRmDataLakeAnalyticsDataSource, New-AzureRmDataLa...
Script     5.2.0      AzureRM.DataLakeStore               {Get-AzureRmDataLakeStoreTrustedIdProvider, Remove-AzureRm...
Script     4.0.4      AzureRM.DevTestLabs                 {Get-AzureRmDtlAllowedVMSizesPolicy, Get-AzureRmDtlAutoShu...
Script     4.1.2      AzureRM.Dns                         {Get-AzureRmDnsRecordSet, New-AzureRmDnsRecordConfig, Remo...
Script     0.3.2      AzureRM.EventGrid                   {New-AzureRmEventGridTopic, Get-AzureRmEventGridTopic, Set...
Script     0.6.3      AzureRM.EventHub                    {New-AzureRmEventHubNamespace, Get-AzureRmEventHubNamespac...
Script     4.1.2      AzureRM.HDInsight                   {Get-AzureRmHDInsightJob, New-AzureRmHDInsightSqoopJobDefi...
Script     4.0.4      AzureRM.Insights                    {Get-AzureRmMetricDefinition, Get-AzureRmMetric, Remove-Az...
Script     3.1.2      AzureRM.IotHub                      {Add-AzureRmIotHubKey, Get-AzureRmIotHubEventHubConsumerGr...
Script     4.3.0      AzureRM.KeyVault                    {Add-AzureKeyVaultCertificate, Set-AzureKeyVaultCertificat...
Script     4.0.3      AzureRM.LogicApp                    {Get-AzureRmIntegrationAccountAgreement, Get-AzureRmIntegr...
Script     0.17.2     AzureRM.MachineLearning             {Move-AzureRmMlCommitmentAssociation, Get-AzureRmMlCommitm...
Script     0.4.2      AzureRM.MachineLearningCompute      {Get-AzureRmMlOpCluster, Get-AzureRmMlOpClusterKey, Test-A...
Script     0.2.1      AzureRM.MarketplaceOrdering         {Get-AzureRmMarketplaceTerms, Set-AzureRmMarketplaceTerms}
Script     0.9.2      AzureRM.Media                       {Sync-AzureRmMediaServiceStorageKeys, Set-AzureRmMediaServ...
Script     5.4.2      AzureRM.Network                     {Add-AzureRmApplicationGatewayAuthenticationCertificate, G...
Script     4.1.1      AzureRM.NotificationHubs            {Get-AzureRmNotificationHub, Get-AzureRmNotificationHubAut...
Script     4.3.2      AzureRM.OperationalInsights         {New-AzureRmOperationalInsightsAzureActivityLogDataSource,...
Script     4.1.4      AzureRM.PowerBIEmbedded             {Remove-AzureRmPowerBIWorkspaceCollection, Get-AzureRmPowe...
Script     4.6.0      AzureRM.Profile                     {Disable-AzureRmDataCollection, Disable-AzureRmContextAuto...
Script     4.1.2      AzureRM.RecoveryServices            {Get-AzureRmRecoveryServicesBackupProperty, Get-AzureRmRec...
Script     4.1.2      AzureRM.RecoveryServices.Backup     {Backup-AzureRmRecoveryServicesBackupItem, Get-AzureRmReco...
Script     0.2.4      AzureRM.RecoveryServices.SiteRec... {Edit-AzureRmRecoveryServicesAsrRecoveryPlan, Get-AzureRmR...
Script     4.1.2      AzureRM.RedisCache                  {Remove-AzureRmRedisCachePatchSchedule, New-AzureRmRedisCa...
Script     0.3.3      AzureRM.Relay                       {New-AzureRmRelayNamespace, Get-AzureRmRelayNamespace, Set...
Script     5.5.2      AzureRM.Resources                   {Get-AzureRmProviderOperation, Remove-AzureRmRoleAssignmen...
Script     0.16.3     AzureRM.Scheduler                   {Disable-AzureRmSchedulerJobCollection, Enable-AzureRmSche...
Script     4.1.2      AzureRM.ServerManagement            {Invoke-AzureRmServerManagementPowerShellCommand, Get-Azur...
Script     0.6.4      AzureRM.ServiceBus                  {New-AzureRmServiceBusNamespace, Get-AzureRmServiceBusName...
Script     0.3.4      AzureRM.ServiceFabric               {Add-AzureRmServiceFabricApplicationCertificate, Add-Azure...
Script     5.0.6      AzureRM.SiteRecovery                {Get-AzureRmSiteRecoveryFabric, New-AzureRmSiteRecoveryFab...
Script     4.4.0      AzureRM.Sql                         {Get-AzureRmSqlDatabaseTransparentDataEncryption, Get-Azur...
Script     4.2.3      AzureRM.Storage                     {Get-AzureRmStorageAccount, Get-AzureRmStorageAccountKey, ...
Script     4.0.4      AzureRM.StreamAnalytics             {Get-AzureRmStreamAnalyticsFunction, Get-AzureRmStreamAnal...
Script     4.0.1      AzureRM.Tags                        {Remove-AzureRmTag, Get-AzureRmTag, New-AzureRmTag}
Script     4.0.3      AzureRM.TrafficManager              {Disable-AzureRmTrafficManagerEndpoint, Enable-AzureRmTraf...
Script     4.0.2      AzureRM.UsageAggregates             Get-UsageAggregates
Script     4.2.2      AzureRM.Websites                    {Get-AzureRmAppServicePlan, Set-AzureRmAppServicePlan, New...
Script     1.12       Microsoft.ADAL.PowerShell           {Clear-ADALAccessTokenCache, Get-ADALAccessToken}
Script     1.0.1      Microsoft.PowerShell.Operation.V... {Get-OperationValidation, Invoke-OperationValidation}
Manifest   1.0.262    MicrosoftPowerBIMgmt
Binary     1.0.262    MicrosoftPowerBIMgmt.Data           {Get-PowerBIDataset, Get-PowerBIDatasource}
Binary     1.0.262    MicrosoftPowerBIMgmt.Profile        {Connect-PowerBIServiceAccount, Disconnect-PowerBIServiceA...
Binary     1.0.262    MicrosoftPowerBIMgmt.Reports        {Get-PowerBIReport, Export-PowerBIReport, Get-PowerBIDashb...
Binary     1.0.262    MicrosoftPowerBIMgmt.Workspaces     {Get-PowerBIWorkspace, Add-PowerBIWorkspaceUser, Remove-Po...
Manifest   1.1.183.8  MSOnline                            {Get-MsolDevice, Remove-MsolDevice, Enable-MsolDevice, Dis...
Manifest   1.1.166.0  MSOnline                            {Get-MsolDevice, Remove-MsolDevice, Enable-MsolDevice, Dis...
Binary     1.0.0.1    PackageManagement                   {Find-Package, Get-Package, Get-PackageProvider, Get-Packa...
Script     3.4.0      Pester                              {Describe, Context, It, Should...}
Script     2.0.1.5    PowerBIPS                           {Get-PBIAuthToken, Set-PBIGroup, Get-PBIGroup, Get-PBIGrou...
Script     1.0.0.1    PowerShellGet                       {Install-Module, Find-Module, Save-Module, Update-Module...}
Script     1.2        PSReadline                          {Get-PSReadlineKeyHandler, Set-PSReadlineKeyHandler, Remov...
Script     21.0.17279 SqlServer                           {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...
Manifest   21.0.17152 SqlServer                           {Add-SqlColumnEncryptionKeyValue, Complete-SqlColumnMaster...

Environment Data

Name                           Value
----                           -----
PSVersion                      5.1.15063.1235
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.15063.1235
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Debug Output

DEBUG: (New-SqlColumnEncryptionKey) In method ProcessRecord. Resolving targets.

Confirm
Continue with this operation?
[Y] Yes  [A] Yes to All  [H] Halt Command  [S] Suspend  [?] Help (default is "Y"): A
DEBUG: (New-SqlColumnEncryptionKey) Resolving targets: Parameter Set = 'ByObject'
DEBUG: (New-SqlColumnEncryptionKey) Resolved target [<database name>]
DEBUG: (New-SqlColumnEncryptionKey) GetShouldProcessTargetString in SqlCmdlet.
DEBUG: (New-SqlColumnEncryptionKey) Validating Target
DEBUG: (New-SqlColumnEncryptionKey) ValidateTarget in SqlCmdlet.  Returning true.
DEBUG: (New-SqlColumnEncryptionKey) Target is valid.  Calling BeginTargetProcessing.
DEBUG: (New-SqlColumnEncryptionKey) In BeginTargetProcessing
DEBUG: (New-SqlColumnEncryptionKey) Subscribing to server events: InfoMesage, StatementExecuted
DEBUG: (New-SqlColumnEncryptionKey) Done with BeginTargetProcessing.  Calling ProcessTarget.
DEBUG: (New-SqlColumnEncryptionKey) Initializing object: Type =
'Microsoft.SqlServer.Management.Smo.ColumnEncryptionKey', Name = 'CEK'
DEBUG: (New-SqlColumnEncryptionKey) Exception occurred System.Management.Automation.PipelineStoppedException: The
pipeline has been stopped.
   at System.Management.Automation.MshCommandRuntime.ThrowTerminatingError(ErrorRecord errorRecord)
   at System.Management.Automation.Cmdlet.ThrowTerminatingError(ErrorRecord errorRecord)
   at Microsoft.SqlServer.Management.PowerShell.AlwaysEncrypted.NewSqlColumnEncryptionKey.InitializeObject(Database
db)
   at Microsoft.SqlServer.Management.PowerShell.SmoCreateCmdlet`2.ProcessTarget(SmoRecordContext target)
   at Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.ProcessRecord()
DEBUG: (New-SqlColumnEncryptionKey) Calling EndProcessing.
DEBUG: (New-SqlColumnEncryptionKey) In EndTargetProcessing
DEBUG: (New-SqlColumnEncryptionKey) Unsubscribing from server events: InfoMesage, StatementExecuted
New-SqlColumnEncryptionKey : One or more errors occurred.
At C:\users\<username>\desktop\2-GenerateColumnKeys.ps1:87 char:4
+             New-SqlColumnEncryptionKey -Name $cekName -InputObject $d ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [New-SqlColumnEncryptionKey], AggregateException
    + FullyQualifiedErrorId : ReflectionError,Microsoft.SqlServer.Management.PowerShell.AlwaysEncrypted.NewSqlColumnEn
   cryptionKey
maddieclayton commented 6 years ago

@jaredmoo Can you take a look at this issue?

microsoft-github-policy-service[bot] commented 1 year ago

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