Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,693,552 hits
  • Select GETDATE()

    November 2019
    M T W T F S S
    « Apr    
     123
    45678910
    11121314151617
    18192021222324
    252627282930  

SQL Server 2019 – Installation Error: An error occurred for a dependency of the feature causing the setup process for the feature to fail.

Posted by blakhani on November 8, 2019


After release of SQL Server 2019, one of our customer was trying to install SQL Server 2019 and encountered an error message. Since I was able to reproduce the issue in my lab, I contacted SSMS product group and they were kind enough to fix this issue very quickly. As of now, this is already called out in the release notes. The purpose of this blog is to show how to confirm if you are hitting the same issue. We would learn how to look at various log file and what are the error messages we need to look.

Issue Description:

Installation of SQL Server 2019 from removable media fails if SQL Server Management Studio 18.3 or earlier is installed. In my lab, I downloaded ISO from download center and mounted it. Here is the final screen shot showing installation failure.

image

When we click on the hyperlink on the screen, it opens up setup summary file which has this information about failed component. (Database Engine Services)

Detailed results:
  Feature:                       Database Engine Services
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, and then try the setup process again.

Next step is to look at Detail.txt file for any hint about dependent feature. (Search for “at Microsoft” keyword)

Target package: "E:\1033_ENU_LP\x64\setup\x64\sqlncli.msi"
InstallPackage: MsiInstallProduct returned the result code 1602.

No retry-able MSI return code detected.
ExecuteActionWithRetryHelper.Failed actionToExecute is ‘Install_sqlncli_Cpu64_Action’, stack trace    at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.Workflow.RunWorkflow(WorkflowObject workflowObject, HandleInternalException exceptionHandler)
   at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.Workflow.RunWorkflow(WorkflowObject workflowObject, HandleInternalException exceptionHandler)
   at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClass2_0.<ExecuteActionWithRetryHelper>b__0()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionWithRetryHelper(WorkflowObject metaDb, ActionKey action, ActionMetadata actionMetadata, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
   at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionEngine.RunActionQueue()
   at Microsoft.SqlServer.Setup.Chainer.Workflow.Workflow.RunWorkflow(WorkflowObject workflowObject, HandleInternalException exceptionHandler)
   at Microsoft.SqlServer.Chainer.Setup.Setup.RunRequestedWorkflow()
   at Microsoft.SqlServer.Chainer.Setup.Setup.Run(String[] args)
   at Microsoft.SqlServer.Chainer.Setup.Setup.Start(String[] args)
   at Microsoft.SqlServer.Chainer.Setup.Setup.Main()
Error: Action "Install_sqlncli_Cpu64_Action" failed during execution.
Completed Action: Install_sqlncli_Cpu64_Action, returned False

…Trimmed lines…

MSI (s) (5C:3C) [06:42:01:134]: Note: 1: 1729
MSI (s) (5C:3C) [06:42:01:134]: Product: Microsoft SQL Server 2012 Native Client  — Configuration failed.

MSI (s) (5C:3C) [06:42:01:134]: Windows Installer reconfigured the product. Product Name: Microsoft SQL Server 2012 Native Client. Product Version: 11.4.7462.6. Product Language: 1033. Manufacturer: Microsoft Corporation. Reconfiguration success or error status: 1602.

From here we can see that issue is during installation of SQLNCLI (SQL Server Native Client) and error code is 1602. The log file associate is sqlncli_Cpu64_1

MSI (s) (5C:3C) [06:42:01:009]: Note: 1: 2203 2: E:\sqlncli.msi 3: -2147287038
MSI (s) (5C:3C) [06:42:01:009]: Source is incorrect. Unable to open or validate MSI package E:\sqlncli.msi.
MSI (s) (5C:3C) [06:42:01:009]: Note: 1: 2203 2: E:\sqlncli.msi 3: -2147287038
MSI (s) (5C:3C) [06:42:01:009]: Source is incorrect. Unable to open or validate MSI package E:\sqlncli.msi.
Please insert the disk:

MSI (s) (5C:3C) [06:42:01:025]: Note: 1: 2265 2:  3: -2147287035
MSI (s) (5C:3C) [06:42:01:025]: User policy value ‘DisableRollback’ is 0
MSI (s) (5C:3C) [06:42:01:025]: Machine policy value ‘DisableRollback’ is 0
Action ended 6:42:01: InstallFinalize. Return value 2.
MSI (s) (5C:3C) [06:42:01:025]: Note: 1: 2318 2:
 

There we can see an interesting error : “Please insert the disk”.

If we check control panel, I already have SQL Server Native Client and mind you, this was installed when I installed SSMS 18.1.

image

Solution:

By now you would have understood the reason of the blog. If you are seeing messages in various logs, which are shown above, then you can conclude that you are hitting the issue due to preinstalled SSMS. Here is the screenshot from the release notes.

https://docs.microsoft.com/en-us/sql/sql-server/sql-server-version-15-release-notes

image

Here are my additional notes.

We have seen this issue earlier when we have SSMS 18.3 already installed and SQL 2019 setup is invoked from ISO mounted as drive. It was combination of few situations.

  • SSMS 18.3 already installed AND
  • SQL Server 2019 Setup was running from a mounted ISO.

We have below workarounds:

  • On existing machines, where SSMS 18.3 or lower is already installed.
    • Uninstall SQL Server 2012 Native Client which got installed with SSMS. Then install SQL Server 2019. OR
    • Instead of running setup from mounted ISO, copy the content of media to a local drive or a network share and start setup.exe from those locations.
  • On new machines, where SSMS is NOT installed.
    • First install SQL Server 2019 and then install SSMS 18.3 OR
    • Install version of SSMS greater than 18.3.1 and then install SQL Server 2019

I hope this blog would help someone find solution without contacting Microsoft Product Support team. Please comment and let me know.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Database Engine Services, Installation, SQL Server 2019 | Tagged: , | Leave a Comment »

    Help: SQL Server Always On Availability Group Listener not working in Azure SQL VMs (Using Internal Load Balancer – ILB). How to troubleshoot?

    Posted by blakhani on April 17, 2018


    As many of you might know that currently I have been supporting SQL Server in Azure Virtual Machines. Most of the concepts remains same but one of the common issue which was reported by many customer is about Always On listener connectivity. In this blog we would talk about the possible causes of SQL Server Always On Availability Group Listener not working in Azure SQL VMs.

    The most common issue is that the listener got created successfully but it connects only from the server which is primary replica. Note that this issue will not occur in on-premise because there is no ILB needed.

    Possible Cause # 1

    Many of our customers have come to Microsoft via Azure support ticket, after following below article. (This is the most common issue)

    Configure a load balancer for an Always On availability group in Azure

    Is there something wrong with the article? No, there is nothing wrong there. Its mostly a oversight when customer miss running below script.

    $ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
    $IPResourceName = "<IPResourceName>" # the IP Address resource name
    $ILBIP = "<n.n.n.n>" # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
    [int]$ProbePort = <nnnnn>
    
    Import-Module FailoverClusters
    
    Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"=$ProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
    

    The parameters are explained in the article which I mentioned in the beginning. This is the script which maps probe port and IP which you have defined in the portal. Here is the PowerShell script which I use to figure out above (Thanks to my team member Ghufran)

     
    #--  Probe Check Script:
    #--------------------------------------------------------------
    Clear-Host
    Get-ClusterResource |`
    Where-Object {$_.ResourceType.Name -like "IP Address"} |`
    Get-ClusterParameter |`
    Where-Object {($_.Name -like "Network") -or ($_.Name -like "Address") -or ($_.Name -like "ProbePort") -or ($_.Name -like "SubnetMask")}
    

    Above script shows us the output which can be used to fill in first PowerShell script.

    Possible Cause # 2

    Second possible cause if that Load Balancing rules are not configured correctly. This information is also mentioned in the article.

    image

    Most of the time two values are set incorrectly “Session persistence” and/or “Floating IP (direct server return)”. If we choose any value other than what mentioned in above image, you would get connectivity issue from passive/other nodes.

    Possible Cause # 3

    In few customer cases I have seen that they run PowerShell for “Cluster IP Address” also. There is no real need to

    Possible Cause # 4

    In few cases, we have also seen that probe port was not opened in firewall. You should enable 1433 (SQL Server listening Port), 5022 (Always On Endpoint port), 59999 (Probe Port). If you are using any non-default port then take care of them in firewall.

    Possible Cause # 5

    If you are using NSG in Azure then make sure ports mentioned in #4 is are open.

    This is the list I have compiled based on support cases reported. I will keep adding more items as and when they are reported.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Always On, AlwaysOn, Azure ILB, listener, Microsoft Azure | Tagged: , , , , , | 3 Comments »

    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
      • Posted in Always Encrypted, Azure Key Vault, SQL Server Management Studio, SSMS | Leave a Comment »