Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,050,832 hits

  • Select GETDATE()

    June 2023
    M T W T F S S
     1234
    567891011
    12131415161718
    19202122232425
    2627282930  

Archive for the ‘SSMS’ Category

« Previous Entries

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 AlwaysOn – Paperback, Kindle
      • Advertisement

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

      SQL SERVER – SSMS Database Expand Hang – High waits on PREEMPTIVE_OS_LOOKUPACCOUNTSID

      Posted by blakhani on April 7, 2015


      Recently I have had a friend who reported below issues.

      1. When I expand database, it takes a lot of time.
      2. When I expand Jobs under SQL Server Agent node in SQL Server Management Studio, it freezes and finally it fails with error  
        “An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
        Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”

      The symptom on the server that we were working on was that from Management Studio, when trying to browse SQL agent job, the interface would hang. I have asked to capture Profiler trace to find out which query is taking time and what is the waits for those query which are stuck.

      In profiler trace, and the query to capture currently running queries (refer this blog) I found that it was running sp_help_job stored procedure from MSDB database. This is the procedure to get high level details about all jobs in MSDB database using msdb.dbo.sysjobs_view. When we looked further, we found that SQL Server is running function dbo.SQLAGENT_SUSER_SNAME and got stuck at SELECT @ret = SUSER_SNAME(@user_sid) statement. The wait for the session is was PREEMPTIVE_OS_LOOKUPACCOUNTSID and wait time was increasing. This wait is related to the communication/validation from Active Directory.

      When we debugged further, here is the chain of reaching to function.

      sp_help_job  
                   >> sp_get_composite_job_info 
                           >> Query having – owner = dbo.SQLAGENT_SUSER_SNAME(sjv.owner_sid) 
                                      >>  SELECT @ret = SUSER_SNAME(@user_sid)

      This is getting stuck at PREEMPTIVE_OS_LOOKUPACCOUNTSID. We was identified that the function is used to convert SIDs stored in SQL Server table to the name by making call to Active Directory. Now the challenge was to find why and also was to identify if its happening with particular logins or all login. The complexity here was that SQL Server stores SID in varbinary format not in the format which OS would understand.

      Luckily, I have had a blog post having script to convert the varbinary to well known format. So I have used that to convert SIDs obtained from below query

      select    owner_sid 
from    msdb.dbo.sysjobs_view
where    owner_sid <> 0x01

       

      Once we have SID value in OS understandable format, I used PsGetSID tool from sysinternals to get Windows account name. While running that it was taking a long time and finally it failed with below error.

      Error querying SID:

      The trust relationship between the primary domain and the trusted domain failed

      So, it was something to do with two domains trust which seems to be broken. I asked him to work with this Windows Domain Admin team and networking team to get the issue resolved.

      Same issue might happen during database expand also as, database owner is a SID stored in sys.databases and that has to be converted to name.

      Hope this helps.

    • Cheers,
    • Balmukund Lakhani
    • Twitter @blakhani

      • Posted in SQL Server Management Studio, SSMS, Troubleshooting | Tagged: , | 4 Comments »

      SSMS – FIX – Error – The log file is not using Unicode format

      Posted by blakhani on December 11, 2014


      While playing with SQL Server Agent Log setting, I changed some setting and found that I was not able to read SQLAgent Logs any more. Here is the error which I was getting in SSMS.

      I followed my own blog and executed T-SQL to read the Agent log and got same error.

      Msg 22004, Level 16, State 1, Line 13
      The log file is not using Unicode format.

      I looked around into the properties which I changed and one of them was “Write OEM error log”. As per MSDN documentation, this option writes the error log file as a non-Unicode file. This reduces the amount of disk space consumed by the log file. However, messages that include Unicode data may be more difficult to read when this option is enabled.

      And that’s precisely the error message says – “it’s not in unicode”.

      There are two ways to fix the error.

      Method 1 (UI Based)

      • Connect to Object Explorer to SSMS > Expand “SQL Server Agent” >  Right Click on “Error Logs” and choose “Configure”
      • Over there uncheck the box “Write OEM error log”

      Method 2 (T-SQL Based)

      Execute below query 

      USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @oem_errorlog=0
GO

      Both of the methods have same effect. Once done, restart SQL Agent service so that now the file is written in unicode format. You should be able to view the log in the viewer now.

      Hope this helps.

    • Cheers,
    • Balmukund Lakhani
    • Twitter @blakhani
    • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

      • Posted in SQL Server, SQL Server Agent, SQL Server Management Studio, SSMS | Tagged: , , , , | Leave a Comment »

      « Previous Entries