Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

FIX: Error – We experienced issues in connecting to Azure – While using SSMS to configure Always Encrypted feature using Azure Key Vault

Posted by blakhani on July 21, 2017


I have come across a situation where I was trying to configure Always Encrypted feature using SSMS. I was trying to use Azure Key Vault to configure it but getting an strange error. Here are few link which you can use to read more about the subject.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-wizard
https://blogs.msdn.microsoft.com/sqlsecurity/2015/11/10/using-the-azure-key-vault-key-store-provider-for-always-encrypted/

image

If we click on “Details” then we would see below:

image

Here is the text of the error message. Note that you might see “Backup” or “Restore” instead of Recover below.

TITLE: We experienced issues in connecting to Azure
—————————–
We experienced issues in connecting to Azure
——————————
ADDITIONAL INFORMATION:
Recover (Microsoft.SqlServer.Management.ServiceManagement)
——————————
BUTTONS:
OK
——————————

If we click on “Show Technical Details” icon, we see below stack.

Program Location:

   at Microsoft.SqlServer.Management.ServiceManagement.ResourceManagement.AzureKeyVaultKeyPermissionEnumConverter.ReadJson                                  
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.DeserializeConvertable                                                                    
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateList                                                                              
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateList                                                                                
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal                                                                       
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue                                                                          
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject                                                                            
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject                                                                              
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal                                                                       
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue                                                                          
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject                                                                            
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject                                                                              
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal                                                                       
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateList                                                                              
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateList                                                                                
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal                                                                       
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue                                                                          
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject                                                                            
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject                                                                              
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal                                                                       
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.SetPropertyValue                                                                          
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject                                                                            
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject                                                                              
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateValueInternal                                                                       
    at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize                                                                               
    at Newtonsoft.Json.JsonSerializer.DeserializeInternal                                                                                                   
    at Newtonsoft.Json.JsonConvert.DeserializeObject                                                                                                        
    at Newtonsoft.Json.JsonConvert.DeserializeObject[T]                                                                                                     
    at Microsoft.SqlServer.Management.ServiceManagement.ResourceManagement.AzureKeyVaultMethods.GetAzureKeyVault                                            
    at Microsoft.SqlServer.Management.ServiceManagement.ResourceManagement.ResourceManagement.GetAzureKeyVault                                              
    at Microsoft.SqlServer.Management.AlwaysEncryptedCommonControl.ColumnMasterKeyStoreCommonControl.AzureKeyVaultComboBox_SelectedValueChanged             
    at System.Windows.Forms.ListControl.OnSelectedValueChanged                                                                                              
     at System.Windows.Forms.ComboBox.OnSelectedValueChanged                                                                                                 
    at System.Windows.Forms.ComboBox.OnSelectedIndexChanged                                                                                                 
    at System.Windows.Forms.ComboBox.set_SelectedIndex                                                                                                      
    at Microsoft.SqlServer.Management.AlwaysEncryptedCommonControl.ColumnMasterKeyStoreCommonControl.SetAdalCredential                                      
    at Microsoft.SqlServer.Management.Azure.UI.CommonUserControl.OnSessionLoggedInEventSet                                                                  
    at Microsoft.SqlServer.Management.Azure.UI.CommonUserControl.AuthenticateUser                         

If you are a geek then you can understand that SSMS is trying to get values in the Combo boxes. If there is a working subscription, then we can see that there are two combo boxes there, Subscriptions name(s) and Key Vault name(s).

RESOLUTION

  • Login to portal using the same account which you are using in SSMS.
  • Go to “Key vaults”
  • Look for existing Key vaults defined.
  • Go to “Access policies” blade.
  • Choose the user.

image

    As per documentation” To generate a column master key in the Azure Key Vault, the user must have the WrapKey, UnwrapKey, Verify, and Sign permissions to the key vault. Users might also need the Get, List, Create, Delete, Update, Import, Backup, and Restore permissions:

    • Once we click on user, I selected below permissions.
      • Under Key Permission (12 selected)
        Key Management Operations: Get, List, Update, Create, Import, Delete, Backup, Restore
        Cryptographic Operations: Unwrap Key, Wrap Key, Verify, Sign
      • Under Secret permissions(0 Selected)
        "Secret Management Operations" : None

    image

    Make sure UI looks like below

    image

      The error message in SSMS might say “Backup” or “Restore” or “Recover” based on what we select under “Secret permissions” There should be NONE selected from there.

      After setting permissions as above make sure you use “save” button to make changes. Hitting OK on previous screen doesn’t make the changes.

      image

      You have to check other Vaults also to make sure those settings are not there.

      I was able to move forward in the wizard and configure Always Encrypted using Azure Key Vault.

      Hope this helps.

    • Cheers,
    • Balmukund Lakhani
    • Twitter @blakhani
    • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
    • Advertisements

      Posted in Always Encrypted, Azure Key Vault, SQL Server Management Studio, SSMS | Leave a Comment »

      Linux Learning : How to get IP Address using command line? What is equivalent of IPCONFIG in Linux?

      Posted by blakhani on August 23, 2016


      Recently, I created a Linux Ubuntu VM in my Hyper-V environment. To connect, I was using PuTTY tool and was getting “Access Denied” error. On the other hand, I was able to connect using Hyper-V console. Here is what I mean

      I knew that I am definitely trying to connect to some other Ubuntu machine in my corporate network. All I need to figure out was the IP Address of my VM and connect using IP Address instead of the name. I took help from in-house expert Pradeep (b | t) and he showed me the magical command.

      In Windows, we are used to use IPCONFIG to know the IP address of a Windows machine. In the same way, IFCONFIG is the command in Linux. Since I was able to connect using Hyper-V console, I ran the magical command.

      It showed the IP Address (10.171.71.87) and then I came back to PuTTy and provided IP to connect and Voilà, I was able to get it!

      This might be something known to many (like Pradeep) but still unknown to few (like me) so sharing via blog.

      Cheers,
      Balmukund

      Posted in Linux Learning | Tagged: , , | 3 Comments »

      Tips and Tricks: Useful Parameters of Get-ClusterLog

      Posted by blakhani on May 20, 2016


      Working with Root Cause Analysis (RCA) is also part of my work at Microsoft. In case of cluster failover RCA, it is very important to get cluster log. Sometimes there are situation where we want to generate cluster log for last few minutes for quicker analysis of live issues. This blog explains some common parameters which I used in my day-to-day troubleshooting.

      I have 4 nodes cluster in my lab named SRV1, SRV2, SRV3, SRV4.

      • Default command – generates Cluster.log file on ALL nodes in C:\Windows\Cluster\Reports folder. File name would be Cluster.log

      Get-ClusterLog 

      • if we want the cluster log to be generated for specific node(s) then we can use –Node parameter. We can put comma separated node names as shown below.

      Get-ClusterLog -Node SRV1, SRV3

      • You might know that the time shown in cluster log is UTC be default. Sometimes its difficult to translate UTC time to local time, specially for time-zones which has daylight saving. Luckily, cluster log can be generated in local time using parameter UseLocalTime . Here is the sample code.

      Get-ClusterLog –UseLocalTime

      • Another useful parameter is to copy the files to specific location. This command would generate logs and also dump on specified location. in below example, I am dumping logs from all nodes to C:\Temp folder.

      Get-ClusterLog –Destination “C:\Temp”

      • TimeSpan is another parameter which can generate cluster log for last number of minutes specified. By default it would generate Cluster.log for complete time. I find it useful when I repro’ed a problem and I want to look at cluster log for last 2 to 3 minutes. Here is the command to generate log for last 3 minutes.

      Get-ClusterLog –TimeSpan 3

      So, this is my favorite command after reproducing cluster issue on local node.

      Get-ClusterLog -Node SRV1 -TimeSpan 2 -UseLocalTime -Destination C:\

      Hopefully it would be useful.

      Cheers,
      Balmukund

      Posted in Cluster, Tips and Tricks, Troubleshooting | Tagged: , , | 4 Comments »