SQL Server Management Studio throws exception when using Always Encrypted

Question

With the latest version of SSMS (v18.10), when we choose to sign in to Azure to use key vault, it always gives this exception:

Rotate (Microsoft.SqlServer.Management.ServiceManagement)

And the error details:

===================================

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
Program Location:

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at Microsoft.SqlServer.Management.ActionHandlers.DialogBasedActionHandler.RunTaskFormThread(Object contextObject)

===================================

Rotate (Microsoft.SqlServer.Management.ServiceManagement)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.ServiceManagement.ResourceManagement.AzureKeyVaultKeyPermissionEnumConverter.ReadJson(JsonReader reader, Type objectType, Object existingValue, JsonSerializer serializer)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.DeserializeConvertable(JsonConverter converter, JsonReader reader, Type objectType, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateList(IList list, JsonReader reader, JsonArrayContract contract, JsonProperty containerProperty, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateList(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, Object existingValue, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConverter propertyConverter, JsonContainerContract containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConverter propertyConverter, JsonContainerContract containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateList(IList list, JsonReader reader, JsonArrayContract contract, JsonProperty containerProperty, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateList(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, Object existingValue, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConverter propertyConverter, JsonContainerContract containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue(JsonProperty property, JsonConverter propertyConverter, JsonContainerContract containerContract, JsonProperty containerProperty, JsonReader reader, Object target)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)
   at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)
   at Newtonsoft.Json.JsonConvert.DeserializeObject(String value, Type type, JsonSerializerSettings settings)
   at Newtonsoft.Json.JsonConvert.DeserializeObject[T](String value, JsonSerializerSettings settings)
   at Microsoft.SqlServer.Management.ServiceManagement.ResourceManagement.AzureKeyVaultMethods.GetAzureKeyVault(ServiceSubscription subscription, String resourceGroupName, String keyVaultName, AzureKeyVaultResource& keyVault)
   at Microsoft.SqlServer.Management.ServiceManagement.ResourceManagement.ResourceManagement.GetAzureKeyVault(String resourceGroupName, String keyVaultName, AzureKeyVaultResource& keyVault)
   at Microsoft.SqlServer.Management.AlwaysEncryptedCommonControl.ColumnMasterKeyStoreCommonControl.AzureKeyVaultComboBox_SelectedValueChanged(Object sender, EventArgs e)
   at System.Windows.Forms.ListControl.OnSelectedValueChanged(EventArgs e)
   at System.Windows.Forms.ComboBox.OnSelectedValueChanged(EventArgs e)
   at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
   at System.Windows.Forms.ComboBox.set_SelectedIndex(Int32 value)
   at Microsoft.SqlServer.Management.AlwaysEncryptedCommonControl.ColumnMasterKeyStoreCommonControl.SetAdalCredential(Object sender, AzureAuthenticationUIEventArgs e)
   at Microsoft.SqlServer.Management.Azure.UI.CommonUserControl.OnSessionLoggedInEventSet(AzureAuthenticationUIEventArgs e)
   at Microsoft.SqlServer.Management.Azure.UI.CommonUserControl.authenticationWorker_RunWorkerCompleted(Object sender, RunWorkerCompletedEventArgs e)
   at System.ComponentModel.BackgroundWorker.OnRunWorkerCompleted(RunWorkerCompletedEventArgs e)
   at System.ComponentModel.BackgroundWorker.AsyncOperationCompleted(Object arg)

Looks like it does not support the newly added Rotate permission of Azure key vault keys.

Just FYI, I don't have an answer for you, but I ran into this issue while trying to set up Always Encrypted on a database we have, but the interesting thing is that _only one_ key vault of ours is throwing this exception. I haven't managed to track down why (and this key vault is actually supposed to be created identically to another key vault that works and was created _after_ the "broken" one, because it's part of our automated deployment process), but it may be possible to work around this issue if you can create a different key vault in the interim. — Chris Cain, Feb 18 '22 at 15:35
@ChrisCain yes, I do notice similar situation. However, the problem is, it never gives me a chance to create/choose another, because the SSMS dialog just crashed when it loads the key vault list to the dropdown list. That said, I had same issue before, what I did is I know which was causing the trouble, so I deleted it and created again, all fine then. — unruledboy, Feb 20 '22 at 09:56

score 1 · Answer 1 · answered Mar 01 '22 at 20:14

A newer version of SSMS was released and, as a part of the v18.11 release notes, it describes fixing an issue similar to this one:

Always Encrypted - Fixes issue with the New Column Master Key wizard freezing up when creating Always Encrypted column master key using Azure Key Vault as the key store, with the key vault having any of the Rotate key permissions set.

Prior to this update, I was receiving the same error as you ran into with one of my key vaults, and it appears that the update has fixed the error.

score 0 · Answer 2 · answered Feb 08 '22 at 08:10

0

Uncheck Rotation policy permissions for the Azure key in access policies for the user

answered Feb 08 '22 at 08:10

navincumar

1
1

I was suspecting that, but I verified all of the key vaults in the drop down list in SSMS are all without any rotation related access checked – unruledboy Feb 09 '22 at 09:45

SQL Server Management Studio throws exception when using Always Encrypted

2 Answers2