search

sous-chefs / sql_server

Development repository for the sql_server cookbook
https://supermarket.chef.io/cookbooks/sql_server
Apache License 2.0
63 stars 124 forks source link

SQL 2016 install fails due to registry key unloaded #132

Closed msaxbury closed 5 years ago

msaxbury commented 5 years ago

Cookbook version

[Version of the cookbook where you are encountering the issue] 5.5.0

Chef-client version

[Version of chef-client in your environment] chef-client 14.12.9-1-x64.msi installed in vm template. chef-server 14.11.21 with manage

Platform Details

[Operating system distribution and release version. Cloud provider if running in the cloud] -Windows 2016 DataCenter -vSphere 6.7.0.30000 machine template. -vRA 7.5.0 (Build: 10053500) -Chef Server plug-in for vRealize Orchestrator 1.0.0 - chef/vmware collaboration. -Default workflow that came with the Chef server plugin - "CHEF\Windows Node Management\VMTools\Windows VMTools Chef-client first run Validatorless workflow" -SQL Server enterprise core 2016

Scenario:

[What you are trying to achieve and you can't?] Execute vRA request which provisions a VM and then calls the vRO chef plugin workflow to add the provisioned VM to Chef server as a client and node (which occurs successfully) and then execute a runlist containing this cookbook to install sql server 2016 enterprise core.

Insallation fails with the following errors:

1.SQL core installer complains about WMI service cannot be started. There are COM+ registry errors in the windows event viewer at the exact time or just seconds before associated with my local administrator AD domain account not having permissions to the runtimebroker com+ application. The event viewer also shows that the SQL Server ScenarioEngine.exe registry keys in use leaked, which results in windows closing the handle before SQL Server ScenarioEngine.exe is done using it for the install.

  1. "SQL Server Setup was not able to access domain 'WinNT://XXXX' to validate user account 'XXXXX'. The SQL Server service account login or password is not valid.” However, the service account password is correct and validated using run as powershell credentials test running notepad or some other app as that user. SQL installer is trying to connect to AD via the runtimebroker COM+ app to validate the service account the SQL configuration.ini unattend file is setting on the windows sql services, but cannot access runtimebroker due to the registry unloading the key necessary as indicated in the error above. It seems to pretty consistently alternate between these 2 errors. I can run it and get WMI error, then run again and get user account access error, then run again and get WMI, then run again and get user account access error.

  2. I occasionally will get "could not read key from registry" without getting either of the first 2 errors.

IF I login to the VM prior to the server.rb recipe running as the local admin AD domain user account chef is running the cookbook under, the sql installer completes successfully without errors.

IF set the security policy "do not forcefully unload registry when user logs off" in the registry of the template, or via a reg key resource before the package resource in the server.rb recipe, the installation completes successfully without errors.

Steps to Reproduce:

[If you are filing an issue what are the things we need to do in order to repro your problem? How are you using this cookbook or any resources it includes?] I am only using the server.rb and configure.rb recipes from the sql_server cookbook. I have commented out options that we don't need in the server.rb recipe, and had to change the OPTIONS string to the following for it to work: "/Q /ACTION=#{node['cs_sql_server']['setup_action']} #{passwords_options} /ConfigurationFile=#{config_file_path} /IAcceptSQLServerLicenseTerms=#{node['cs_sql_server']['accept_eula']}"

I added 2 custom recipes, one to run first which bootstraps the vm with custom applications, then server.rb followed by configure.rb, followed by custom recipe to execute my custom apps.

ALL recipes run successfully and complete without issue when executed via chef-solo command logged into the vm as admin.

  1. I have a windows template in vSphere that contains the chef-client installed.
  2. vRA blueprint clones the template and powers on, joins domain, adds local users specified to admins, updates vmtools.
  3. vRA executes the vRO Chef Server plug-in for vRealize Orchestrator's default worklfow named "CHEF\Windows Node Management\PowerShell\Windows PS Chef-client first run Validatorless workflow" and passes required parameters.
  4. The workflow successfully executes my custom recipe first and then server.rb
  5. Server.rb completes with errors.
  6. Login and review SQL installation logs to find the errors listed above.

IF I login to the VM prior to the server.rb recipe running as the local admin AD domain user account chef is running the cookbook under, the sql installer completes successfully without errors.

IF set the security policy "do not forcefully unload registry when user logs off" in the registry of the template, or via a reg key resource before the package resource in the server.rb recipe, the installation completes successfully without errors.

Expected Result:

[What are you expecting to happen as the consequence of above reproduction steps?] I expect the sql installer to error with the errors indicated.

Actual Result:

[What actually happens after the reproduction steps? Include the error output or a link to a gist if possible.]

DCOM errors at the time of the sql install error in the logs:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 
{D63B10C5-BB46-4990-A94F-E40B9D520160}
and APPID 
{9CA88EE3-ACB7-47C8-AFC4-AB702511C276}
to the user DOMAIN\USER SID (S-1-5-21-221447426-1814128558-2251880862-1653) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

Registry key leaked event viewer errors: `Windows detected your registry file is still in use by other applications or services. The file will be unloaded now. The applications or services that hold your registry file may not function properly afterwards. No user action is required.

DETAIL - 11 user registry handles leaked from \Registry\User\S-1-5-21-221447426-1814128558-2251880862-1653: Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653 Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Policies Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Policies\Microsoft\Windows\CurrentVersion\Internet Settings Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Microsoft\Internet Explorer\Main Process 2836 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\setup.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Microsoft\Windows NT\CurrentVersion Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\Control Panel\International Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Microsoft\Internet Explorer\Security Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap

1 user registry handles leaked from \Registry\User\S-1-5-21-221447426-1814128558-2251880862-1653_Classes: Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653_Classes

1 user registry handles leaked from \Registry\User\S-1-5-21-221447426-1814128558-2251880862-1653: Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653\SOFTWARE\Microsoft\Windows NT\CurrentVersion

2 user registry handles leaked from \Registry\User\S-1-5-21-221447426-1814128558-2251880862-1653_Classes: Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653_Classes Process 1672 (\Device\HarddiskVolume4\00.Install\SQL Server 2016 Enterprise Core\x64\ScenarioEngine.exe) has opened key \REGISTRY\USER\S-1-5-21-221447426-1814128558-2251880862-1653_Classes `

SQL install log errors: `C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190509_104359\Detail.txt (01) 2019-05-09 10:44:04 Slp: Running Action: ValidateSettingsAgainstScenario (01) 2019-05-09 10:44:04 Slp: Scenario: Install (01) 2019-05-09 10:44:04 Slp: Completed Action: ValidateSettingsAgainstScenario, returned True (01) 2019-05-09 10:44:04 Slp: ---------------------------------------------------------------------- (01) 2019-05-09 10:44:04 Slp: Running Action: FinalCalculateSettings (06) 2019-05-09 10:44:04 Slp: The directory monitor thread is interrupted due to the following exception while monitoring the notifications for directory 'C:\Program Files (x86)' (06) 2019-05-09 10:44:04 Slp: The following is an exception stack listing the exceptions in outermost to innermost order (06) 2019-05-09 10:44:04 Slp: Inner exceptions are being indented (06) 2019-05-09 10:44:04 Slp: (06) 2019-05-09 10:44:04 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.MonitorThreadInterruptedException (06) 2019-05-09 10:44:04 Slp: Message: (06) 2019-05-09 10:44:04 Slp: The directory monitor thread is interrupted. Message: 'The directory monitor thread is interrupted due to a StopMonitor call.' (06) 2019-05-09 10:44:04 Slp: HResult : 0x86a40016 (06) 2019-05-09 10:44:04 Slp: FacilityCode : 1700 (6a4) (06) 2019-05-09 10:44:04 Slp: ErrorCode : 22 (0016) (06) 2019-05-09 10:44:04 Slp: Data: (06) 2019-05-09 10:44:04 Slp: ErrorMessage = The directory monitor thread is interrupted due to a StopMonitor call. (06) 2019-05-09 10:44:04 Slp: WatsonData = Microsoft.SqlServer.Configuration.Sco.MonitorThreadInterruptedException@22 (06) 2019-05-09 10:44:04 Slp: Stack: (06) 2019-05-09 10:44:04 Slp: at Microsoft.SqlServer.Configuration.Sco.DirectoryMonitor.Monitor()

…….. (01) 2019-05-09 10:44:04 Slp: Initializing rule : Windows Management Instrumentation (WMI) service (01) 2019-05-09 10:44:04 Slp: Rule is will be executed : True (01) 2019-05-09 10:44:04 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.WmiServiceInfo (01) 2019-05-09 10:44:05 Slp: Cluster Service is not running : Exception is as follows System.Runtime.InteropServices.COMException (0x80040150): Could not read key from registry

at System.Management.ThreadDispatch.Start() at System.Management.ManagementScope.Initialize() at System.Management.ManagementObjectSearcher.Initialize() at System.Management.ManagementObjectSearcher.Get() at Microsoft.SqlServer.Configuration.SetupExtension.WmiServiceInfo.Microsoft.SqlServer.Configuration.RulesEngineExtension.IRuleInitialize.Init(String ruleId) at Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngine.Execute(Boolean stopOnFailure) (01) 2019-05-09 10:44:05 Slp: Rule initialization failed - hence the rule result is assigned as Failed (01) 2019-05-09 10:44:05 Slp: Initializing rule : Consistency validation for SQL Server registry keys (01) 2019-05-09 10:44:05 Slp: Rule is will be executed : True (01) 2019-05-09 10:44:05 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.AclPermissionsFacet (01) 2019-05-09 10:44:05 Slp: Launching external tool: e:\00.install\sql server 2016 enterprise core\x64\FixSqlRegistryKey_x64.exe (01) 2019-05-09 10:44:05 Slp: ------------------------------------------------------------ (08) 2019-05-09 10:44:05 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server - Set SE_DACL_AUTO_INHERITED. (08) 2019-05-09 10:44:05 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130 - Set SE_DACL_AUTO_INHERITED. (01) 2019-05-09 10:44:05 Slp: ------------------------------------------------------------ (01) 2019-05-09 10:44:05 Slp: e:\00.install\sql server 2016 enterprise core\x64\FixSqlRegistryKey_x64.exe exit code: 0 (01) 2019-05-09 10:44:05 Slp: Launching external tool: e:\00.install\sql server 2016 enterprise core\x64\FixSqlRegistryKey_x86.exe (01) 2019-05-09 10:44:05 Slp: ------------------------------------------------------------ (09) 2019-05-09 10:44:05 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server - OK. (09) 2019-05-09 10:44:05 Slp: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130 - OK. (01) 2019-05-09 10:44:05 Slp: ------------------------------------------------------------ (01) 2019-05-09 10:44:05 Slp: e:\00.install\sql server 2016 enterprise core\x64\FixSqlRegistryKey_x86.exe exit code: 0 (01) 2019-05-09 10:44:05 Slp: Rule 'AclPermissionsFacet' detection result: AreSqlAclPermissionsCanonical= True (01) 2019-05-09 10:44:05 Slp: Evaluating rule : AclPermissionsFacet (01) 2019-05-09 10:44:05 Slp: Rule running on machine: HOSTNAME (01) 2019-05-09 10:44:05 Slp: Rule evaluation done : Succeeded (01) 2019-05-09 10:44:05 Slp: Rule evaluation message: SQL Server registry keys are consistent and can support SQL Server installation or upgrade. (01) 2019-05-09 10:44:05 Slp: Initializing rule : Long path names to files on SQL Server installation media (01) 2019-05-09 10:44:05 Slp: Rule is will be executed : True (01) 2019-05-09 10:44:05 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.MediaPathLenghtFacet (01) 2019-05-09 10:44:05 Slp: Rule 'MediaPathLength' INSTALLMEDIAPATH length is 56 (01) 2019-05-09 10:44:05 Slp: Evaluating rule : MediaPathLength (01) 2019-05-09 10:44:05 Slp: Rule running on machine: HOSTNAME (01) 2019-05-09 10:44:05 Slp: Rule evaluation done : Succeeded (01) 2019-05-09 10:44:05 Slp: Rule evaluation message: The SQL Server installation media is not too long. (01) 2019-05-09 10:44:05 Slp: Initializing rule : SQL Server Setup Product Incompatibility (01) 2019-05-09 10:44:05 Slp: Rule is will be executed : True (01) 2019-05-09 10:44:05 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.SetupCompatibilityCheck (01) 2019-05-09 10:44:05 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine (01) 2019-05-09 10:44:05 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\130\Bootstrap\Incompatibilities (01) 2019-05-09 10:44:05 Slp: Searching for reg path SOFTWARE\Microsoft\Microsoft SQL Server\130\Bootstrap\Incompatibilities. Open subkey result is False (01) 2019-05-09 10:44:05 Slp: Regkey not present. No incompatibilities (01) 2019-05-09 10:44:05 Slp: Incompatibility detected = False (01) 2019-05-09 10:44:05 Slp: Evaluating rule : SetupCompatibilityCheck (01) 2019-05-09 10:44:05 Slp: Rule running on machine: HOSTNAME (01) 2019-05-09 10:44:05 Slp: Rule evaluation done : Succeeded (01) 2019-05-09 10:44:05 Slp: Rule evaluation message: Setup has not detected any incompatibilities. (01) 2019-05-09 10:44:05 Slp: Initializing rule : .NET 2.0 and .NET 3.5 Service Pack 1 update for Windows 2008 R2 and Windows 7 (KB958488) (01) 2019-05-09 10:44:05 Slp: Rule is will be executed : True (01) 2019-05-09 10:44:05 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.NoRebootPackageFacet (01) 2019-05-09 10:44:05 Slp: Evaluating rule : NoRebootPackage (01) 2019-05-09 10:44:05 Slp: Rule running on machine: HOSTNAME (01) 2019-05-09 10:44:05 Slp: Rule evaluation done : Succeeded (01) 2019-05-09 10:44:05 Slp: Rule evaluation message: This computer has the required update package. (01) 2019-05-09 10:44:05 Slp: Initializing rule : Edition WOW64 platform (01) 2019-05-09 10:44:05 Slp: Rule is will be executed : True (01) 2019-05-09 10:44:05 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.FacetWOW64PlatformCheck (01) 2019-05-09 10:44:05 Slp: Current machineArchitecture = x64 (01) 2019-05-09 10:44:05 Slp: Setup package is 64 bit, Marking check for WOW Supported32bitSetup as passed (01) 2019-05-09 10:44:05 Slp: Current machineArchitecture is 64bit (01) 2019-05-09 10:44:05 Slp: Rule 'FacetWOW64PlatformCheck' results: Supported32bitSetup=True (01) 2019-05-09 10:44:05 Slp: Evaluating rule : FacetWOW64PlatformCheck (01) 2019-05-09 10:44:05 Slp: Rule running on machine: HOSTNAME (01) 2019-05-09 10:44:05 Slp: Rule evaluation done : Succeeded (01) 2019-05-09 10:44:05 Slp: Rule evaluation message: SQL Server Setup is supported on this operating system platform. (01) 2019-05-09 10:44:05 Slp: Saved to directory.......C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190509_104359\SystemConfigurationCheck_Report.htm (01) 2019-05-09 10:44:05 Slp: Sco: Attempting to create directory C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190509_104359\resources (01) 2019-05-09 10:44:05 Slp: Sco: Attempting to create directory C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190509_104359\resources, security descriptor (01) 2019-05-09 10:44:05 Slp: Sco: Directory 'C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190509_104359\resources' already exists, skipping creation, moving to set security descriptor. (01) 2019-05-09 10:44:05 Slp: Sco: Attempting to set security descriptor for directory C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190509_104359\resources, security descriptor (01) 2019-05-09 10:44:05 Slp: Sco: SDDL is NULL/empty and overwriteSecurityDescriptor is false, so nothing to do, return. (01) 2019-05-09 10:44:05 Slp: Rules evaluation report is available now at C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20190509_104359\SystemConfigurationCheck_Report.htm (01) 2019-05-09 10:44:05 Slp: Error: Action "Microsoft.SqlServer.Configuration.RulesEngineExtension.RunRulesAction" threw an exception during execution. (01) 2019-05-09 10:44:05 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: The Windows Management Instrumentation (WMI) service cannot be started. To continue with the installation, you must troubleshoot and repair your Windows Management Instrumentation (WMI) service. ---> Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngineRuleFailureException: The Windows Management Instrumentation (WMI) service cannot be started. To continue with the installation, you must troubleshoot and repair your Windows Management Instrumentation (WMI) service. (01) 2019-05-09 10:44:05 Slp: at Microsoft.SqlServer.Configuration.RulesEngineExtension.RunRulesAction.ExecuteAction(String actionId) `

TheLunaticScripter commented 5 years ago

@msaxbury Thank you for opening this issue. There is a lot of information but one of the things I did notice that you are using the server and configure recipes. These recipes are legacy and are not currently being maintained. It is recommended that instead of using these recipes that you use the sql_server_install custom resource in a wrapper cookbook. I also noticed that you reference a security configuration to unload the user registry key after log off, this doesn't appear to be a common requirement and we have the capabilities to test for that use case at this time. Currently, this cookbook is lightly maintained as we recommend managing SQL server installations and SQL configuration with Habitat.

msaxbury commented 5 years ago

@TheLunaticScripter Thanks for the reply. There is no sql_server_install custom resource in the resource folder? This cookbook also does not handle SQL state very well because it just attempts to re-install SQL which is a bad idea on an active running instance as it could cause problems with the running instance. I will look at the Habitat link.

TheLunaticScripter commented 5 years ago

@msaxbury The install.rb file is the custom resource in your wrapper cookbook to call it that resource you would use sql_server_install. Could you describe a bit more what you mean by it not handling state? This resource uses the package resource and is looking for a specific name for the SQL installation. An example of using the resource can be found in the https://github.com/chef-cookbooks/sql_server/blob/master/test/cookbooks/test/recipes/enterprise.rb file.

msaxbury commented 5 years ago

@TheLunaticScripter

Using the recipe package resource to install sql server, it just attempts to reinstall sql. I posted on Chef Mailing list and @jnalewak replied, "The way that Chef Infra works is that the chef-client runs and gathers Ohai data from the node. The client compares the state of resources (https://docs.chef.io/resource.html) against what the desired state is as defined by the cookbooks applied to that node. This is a "test-and-repair" model that is idempotent; meaning that if the running state of a resource matches the desired state, the client skips applying the resource."

The recipe package resource components don't match any of the Ohai data that comes back for installed programs related to SQL Server. So, it is not able to determine that SQL Server is installed using the Ohai and resource component data and therefore skip that resource. The test enterprise.rb wrapper resource does not either, so I presume it will execute the resource and try to reinstall it rather than skip that resource.

TheLunaticScripter commented 5 years ago

@msaxbury So that's partially true. The SQL install uses the package resource the package resource needs the name to be exactly as it appears in

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Uninstall
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall

Chances are the node['sql_server']['server']['package_name'] is not correct and that is why it is attempting to reinstall.

msaxbury commented 5 years ago

@TheLunaticScripter Thanks for working with me. Is it looking for the DisplayName string value in the "Microsoft SQL Server SQLServer2016" key or the key name itself? BTW that DisplayName is also on the "Microsoft SQL Server 13" key. And what about the other entry without "(64-bit)"?

I tried setting the name to exactly that in the recipe, without wrapping it, as well as setting a version. The version is actually the version on the Setup.exe sql bootstrap installer file. SQL is installed correctly but still tries to reinstall it when the client runs again.

windows_package 'Microsoft SQL Server 2016(64-bit)' do
  installer_type :custom
  source package_url
  options "/Q /ACTION=#{node['cs_sql_server']['setup_action']} #{passwords_options} /ConfigurationFile=#{config_file_path} /IAcceptSQLServerLicenseTerms=#{node['cs_sql_server']['accept_eula']}"
  action :install
  version "13.0.1601.5"
  returns [0, -2068643838, -2147467259, 42, 127, 3010]
end

Here is the Windows Programs and Features entries: image

A new install folder is produced here with timestamp - "C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log" and the Summary.txt is updated to say "No features were installed during the setup execution. The requested features may already be installed. Please review the summary.txt log for further details."

TheLunaticScripter commented 5 years ago

It should be looking for the DisplayName. I would highly recommend using the custom resource. Trying to understand where the attributes get set can be difficult to troubleshoot and often lead to a bad experience. As a note, it is not a recommended practice to fork the cookbook but to use a wrapper profile. We try to make updates to the cookbook so that it can support the latest version of the chef-client as they are released and we can't really fully troubleshoot issues from a fork.

msaxbury commented 5 years ago

@TheLunaticScripter I updated my recipe to call that install name from the registry key and the programs and features entry using package.

package 'Microsoft SQL Server 2016(64-bit)' do
  installer_type :custom
  source package_url
  options "/Q /ACTION=#{node['cs_sql_server']['setup_action']} #{passwords_options} /ConfigurationFile=#{config_file_path} /IAcceptSQLServerLicenseTerms=#{node['cs_sql_server']['accept_eula']}"
  action :install
  returns [0, -2068643838, -2147467259, 42, 127, 3010]
end

It still tries to reinstall sql by executing the setup.exe again on the next client run: image

I don't see a requirement for the wrapper if the wrapper is going to call package anyway?? why would Chef be able to compare the wrapper code to Ohai data better than the package code??

msaxbury commented 5 years ago

@TheLunaticScripter Habitat seems like a lot of rework right now so I am not sure that is right route for us at this point.

I created my recipe off the install.rb resource. It pulls the properties from data bags and attributes file; I skip DREPLAY_CLT, security mode check and netfx35 checks; I do use config_file_path, x86_64 and sql_sys_admin_list; template_config builds the confi.ini file correctly; I use package_url but just pull in the package_name from attributes and skip checksum; I use the same passwords resource; and the package resource above matches the one in the install.rb. It is installing and creating the config.ini file all successfully. It works.

SO, why is a wrapper necessary just for chef to compare the package_name against the Ohai data when everything else works successfully???

You stated: "The recipe package resource components don't match any of the Ohai data that comes back for installed programs related to SQL Server." and "...the package resource needs the name to be exactly as it appears in [registry entries]." My setup does exactly that, and yet Chef is not comparing the package resource component name to the Ohai data but installing successfully???

TheLunaticScripter commented 5 years ago

Awesome I'm glad using the resource worked for you.

Habitat isn't really a lot of rework. We already have a package for sqlserver so getting it installed is as simple as hab svc load core/sqlserver --strategy at-once. In fact doing more complicated things like clustering, which is very difficult with Chef, is also just as simple. But I understand I also started with writing my own SQL server cookbook and it became monstrous to manage.

Let me be a bit more clear in my statements. First, there have been many patterns for the use of chef cookbooks that have evolved over the years. One of the most popular and successful patterns is the wrapper cookbook pattern. One of the key elements of Chef is its ability to reduce the amount of code that needs to be written by a customer. The Wrapper cookbook pattern helps with this by only having the code, usually attributes, modified in the customer's cookbook. This allows you to get updates to features in a community cookbook much faster. One of the drawbacks of this process is attribute precedence can be tricky as there are numerous levels of precedence for attributes. That is why community cookbooks have moved to a resource-based approach. This removes attribute precedence as the customer just uses the resource the same way they would use any of the other resources that ship with the chef-client. The only way for both these methods to be successful and for you to get the latest updates is through a wrapper cookbook. You could just copy and paste the resources or the recipes into a cookbook and then run them but it is then on you to maintain all that code as it changes. With a wrapper, all you need to maintain is the 10 or so lines of the resource.

I'm super confused as to what Ohai attributes you are talking about. Ohai is a tool that is built into the chef-client to pull system information automatically. Some times you can choose to use those attributes or you can just see the information in a system like Automate. In the recipe we are using the node['sql_server']['server']['package_name'] for the package resource but this is found in the attributes directory server.rb file. This is not an Ohai attribute but is set in a wrapper cookbook's attributes file or uses the helper in the lib directory to help build it.

msaxbury commented 5 years ago

@TheLunaticScripter I have not had a problem getting the sql cookbook to install sql successfully, that has always worked, regardless of not using a wrapper with this cookbook.

The issue I am having is that the recipe I built using the code from the install.rb custom resource in your cookbook, does not validate that SQL is installed and therefore skip the package resource that installs it. Instead it just tried to execute the SQL installer every client run.

According to your responses in this thread, it is my understanding that chef uses the name I define in the "package_name" attribute OR the name I put between single quotes in the package resource do statement - "package 'Microsoft SQL Server 2016(64-bit)' do" - to verify whether SQL is installed or not by comparing that value to the value in the registry hive you posted???

The package resource is not validating that SQL is installed, and I am trying to figure out why? The name in my attribute and package resource do statement, matches what is in the registry and the program and features install window.

msaxbury commented 5 years ago

@TheLunaticScripter any thoughts? going to pursue a support ticket next if I can't get any analysis on this here.

msaxbury commented 5 years ago

sorry i found the discrepancy.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\ had 2 keys with the same value:

Microsoft SQL Server 13 - "Microsoft SQL Server 2016 (64-bit)" Microsoft SQL Server SQLServer2016 - "Microsoft SQL Server 2016 (64-bit)"

Package resource was using "Microsoft SQL Server 2016(64-bit)" NO space between 2016 and (64-bit).