The required IACCEPTMSODBCSQLLICENSETERMS=YES command-line parameter is missing.

[mrswadge]

Cookbook version

5.5.0

Chef-client version

14.11.21

Platform Details

Windows Server 2016 connecting to SQL Server 2016 on another host.

Scenario:

Cannot install SQL Server Native Client as the terms constant for the MsiExec installer are not the same as in the recipe\client.rb file.

Steps to Reproduce:

Created a cookbook with a dependency on the sql_server cookbook via metadata.rb.

Added Attributes:

default['sql_server']['accept_eula'] = 'true'

default['sql_server']['native_client']['url'] = Addressable::URI.convert_path( File.join( Chef::Config[:file_cache_path], 'cookbooks', 'otbo_sql_server', 'files', 'default', 'msodbcsql.msi' ) ).to_s.gsub(/#/, '%23')
default['sql_server']['native_client']['checksum'] = '9f21af4bd88c25fa4c4a8763eb81c1a5f92af20cbf2f2391a2293da2bca90cdf'
default['sql_server']['native_client']['package_name'] = 'Microsoft SQL Server - Native Client'

default['sql_server']['command_line_utils']['url'] = Addressable::URI.convert_path( File.join( Chef::Config[:file_cache_path], 'cookbooks', 'otbo_sql_server', 'files', 'default', 'MsSqlCmdLnUtils.msi' ) ).to_s.gsub(/#/, '%23')
default['sql_server']['command_line_utils']['checksum'] = '9479810a2ea8954e0cacd3d47fe0bcb81647b9cfb5efd6ab79b76a6ccadcefe7'
default['sql_server']['command_line_utils']['package_name'] = 'Microsoft SQL Server - Command Line Tools'

Recipe simply calls through to sql_server::client cookbook via include_recipe.

include_recipe 'sql_server::client'

Expected Result:

Successful installation of SQL Server Native Client and SQL Server Command Line Utilities.

Actual Result:

myhost Recipe: sql_server::client
myhost   * windows_package[Microsoft SQL Server - Native Client] action install[2019-04-09T16:00:26+01:00] INFO: Processing windows_package[Microsoft SQL Server - Native Client] action install (sql_server::client line 29)
myhost
myhost   Recipe: <Dynamically Defined Resource>
myhost     * remote_file[c:\chef\cache\package\msodbcsql.msi] action create[2019-04-09T16:00:26+01:00] INFO: Processing remote_file[c:\chef\cache\package\msodbcsql.msi] action create (dynamically defined)
myhost  (up to date)
myhost
myhost
myhost     ================================================================================
myhost     Error executing action `install` on resource 'windows_package[Microsoft SQL Server - Native Client]'
myhost     ================================================================================
myhost
myhost     Mixlib::ShellOut::ShellCommandFailed
myhost     ------------------------------------
myhost     Expected process to exit with [0], but received '1603'
myhost     ---- Begin output of msiexec /qn /i "c:\chef\cache\package\msodbcsql.msi"  IACCEPTSQLNCLILICENSETERMS=YES ----
myhost     STDOUT:
myhost     STDERR:
myhost     ---- End output of msiexec /qn /i "c:\chef\cache\package\msodbcsql.msi"  IACCEPTSQLNCLILICENSETERMS=YES ----
myhost     Ran msiexec /qn /i "c:\chef\cache\package\msodbcsql.msi"  IACCEPTSQLNCLILICENSETERMS=YES returned 1603
myhost
myhost     Resource Declaration:
myhost     ---------------------
myhost     # In c:/chef/cache/cookbooks/sql_server/recipes/client.rb
myhost
myhost      29:   package node['sql_server'][pkg]['package_name'] do # ~FC009
myhost      30:     source node['sql_server'][pkg]['url']
myhost      31:     checksum node['sql_server'][pkg]['checksum']
myhost      32:     installer_type :msi
myhost      33:     options "IACCEPTSQLNCLILICENSETERMS=#{node['sql_server']['accept_eula'] ? 'YES' : 'NO'}"
myhost      34:     action :install
myhost      35:   end
myhost      36: end
myhost
myhost     Compiled Resource:
myhost     ------------------
myhost     # Declared in c:/chef/cache/cookbooks/sql_server/recipes/client.rb:29:in `block in from_file'
myhost
myhost     windows_package("Microsoft SQL Server - Native Client") do
myhost       package_name "Microsoft SQL Server - Native Client"
myhost       action [:install]
myhost       default_guard_interpreter :default
myhost       declared_type :package
myhost       cookbook_name "sql_server"
myhost       recipe_name "client"
myhost       installer_type :msi
myhost       source "file:///c:/chef/cache/cookbooks/otbo_sql_server/files/default/msodbcsql.msi"
myhost       options "IACCEPTSQLNCLILICENSETERMS=YES"
myhost       checksum "9f21af4bd88c25fa4c4a8763eb81c1a5f92af20cbf2f2391a2293da2bca90cdf"
myhost       version "12.2.5543.11"
myhost     end
myhost
myhost     System Info:
myhost     ------------
myhost     chef_version=14.11.21
myhost     platform=windows
myhost     platform_version=10.0.14393
myhost     ruby=ruby 2.5.3p105 (2018-10-18 revision 65156) [x64-mingw32]
myhost     program_name=C:/opscode/chef/bin/chef-client
myhost     executable=C:/opscode/chef/bin/chef-client
myhost
myhost [2019-04-09T16:00:26+01:00] INFO: Running queued delayed notifications before re-raising exception
myhost
myhost Running handlers:
myhost [2019-04-09T16:00:26+01:00] ERROR: Running exception handlers
myhost Running handlers complete
myhost [2019-04-09T16:00:26+01:00] ERROR: Exception handlers complete
myhost Chef Client failed. 6 resources updated in 02 minutes 08 seconds
myhost [2019-04-09T16:00:26+01:00] INFO: Sending resource update report (run-id: bcc567ca-2b58-4dec-97ef-c069b761e8b5)
myhost [2019-04-09T16:00:26+01:00] FATAL: Stacktrace dumped to c:/chef/cache/chef-stacktrace.out
myhost [2019-04-09T16:00:26+01:00] FATAL: Please provide the contents of the stacktrace.out file if you file a bug report
myhost [2019-04-09T16:00:26+01:00] FATAL: Mixlib::ShellOut::ShellCommandFailed: windows_package[Microsoft SQL Server - Native Client] (sql_server::client line 29) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '1603'
myhost ---- Begin output of msiexec /qn /i "c:\chef\cache\package\msodbcsql.msi"  IACCEPTSQLNCLILICENSETERMS=YES ----
myhost STDOUT:
myhost STDERR:
myhost ---- End output of msiexec /qn /i "c:\chef\cache\package\msodbcsql.msi"  IACCEPTSQLNCLILICENSETERMS=YES ----
myhost Ran msiexec /qn /i "c:\chef\cache\package\msodbcsql.msi"  IACCEPTSQLNCLILICENSETERMS=YES returned 1603
ERROR: Failed to execute command on myhost return code 1

Further investigation shows that the provided IACCEPTSQLNCLILICENSETERMS=YES is incorrect as it should be IACCEPTMSODBCSQLLICENSETERMS=YES

Action start 16:16:11: SNAC_IAcceptMSODBCSQLLicenseTerms.
MSI (s) (C4:04) [16:16:11:832]: Product: Microsoft ODBC Driver 11 for SQL Server -- The required IACCEPTMSODBCSQLLICENSETERMS=YES command-line parameter is missing. By specifying this parameter, you acknowledge that you accept the end user license terms for the Microsoft ODBC Driver 11 for SQL Server.

The required IACCEPTMSODBCSQLLICENSETERMS=YES command-line parameter is missing. By specifying this parameter, you acknowledge that you accept the end user license terms for the Microsoft ODBC Driver 11 for SQL Server.
Action ended 16:16:11: SNAC_IAcceptMSODBCSQLLicenseTerms. Return value 3.
Action ended 16:16:11: INSTALL. Return value 3.

You cannot install the Command Line Utilities without the ODBC drivers.

MSI (s) (E8:5C) [16:32:58:425]: Product: Microsoft Command Line Utilities 13 for SQL Server -- Setup is missing an installation prerequisite:
 -Microsoft ODBC Driver 11 for SQL Server. To continue, install Microsoft ODBC Driver 11 for SQL Server and 
then run the Setup operation again

The Microsoft website also states "there is no SQL Server 2016 Native Client." (https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-2017)

[welcomebot]

Hey There
It looks like this is the first issue you've filed against the chef-cookbooks project. I'm here to offer you a bit of extra help to make sure we can quickly get back to you.
Make sure you've filled out all the fields in our issue template. Make sure you've provided us with the version of chef-client you're running, your operating system and the version of the cookbook. If you're not using the most up to date version of the cookbook then please make sure to update first. Lots of things change between versions even if you're issue isn't listed in the changelog. Finally please give us a detailed description of the issue you're having. The more we know about what you're trying to do, what actually happens, and how you can reproduce the problem, the better.

If you're looking for more immediate troubleshooting help make sure to check out #general on the Chef Community Slack. There's plenty of folks there willing to lend a helping hand. Thanks for the first issue. We hope we can get back to you soon with a solution.

[TheLunaticScripter]

@mrswadge Thank you for opening this issue. We will see if we can get that issue reproduced and a sensible fix out. Thanks again for taking the time to track that down.

[mrswadge]

Thanks for the swift response @TheLunaticScripter

I've done some further digging and it appears the error messages in the Command Line Utilities installer regarding dependencies are a mess.

If you install ODBC Driver 11, the Command Line Utilities still complains about missing ODBC Driver 11. The error message is incorrect and it actually depends upon ODBC Driver 13.1.

I figured that I would try to install newer versions of the tools, but ODBC 17 (link) has an additional dependency on Visual C++ 2017 (link). This combination is compatible with Command Line Utilities 15 (link).

Perhaps going a little off-topic for this bug report, but I'm not sure that I need all of the client software installed, I only need ODBC and the Command Line Utilities. What I'm attempting to do via my cookbook is allow an offline (aka air-gapped) install of the clients, but there doesn't appear to be a way of limiting the install to a couple of packages as the list is hardcoded in recipe/client.rb %w( native_client command_line_utils clr_types smo ps_extensions ).each do |pkg|. Adding some flexibility here would be a nice addition to the cookbook.

Thanks,
Stuart

[mrswadge]

To keep my project moving forward I have done the following within my cookbook which is essentially aimed towards wrapping the sql_server cookbook to provide offline installer packages.

I have created recipe\client.rb which calls to a similarly styled wrapper of the vcruntime cookbook. It reduces the scope of the client install to ODBC drivers and Command Line Utilities only and uses an attribute to define the term used for the user agreement. I guess the dependency management could be better handled for the VC Runtime.

include_recipe 'otbo_vcruntime::vc15'

%w( odbc command_line_utils ).each do |pkg|
  package node['sql_server'][pkg]['package_name'] do # ~FC009
    source node['sql_server'][pkg]['url']
    checksum node['sql_server'][pkg]['checksum']
    installer_type :msi
    options "#{node['sql_server'][pkg]['terms']}=#{node['sql_server'][pkg]['accept_eula'] ? 'YES' : 'NO'}"
    action :install
  end
end

# update path
windows_path "#{node['sql_server']['install_dir']}\\#{SqlServer::Helper.install_dir_version(node['sql_server']['version'])}\\Tools\\Binn" do
  action :add
end

The attributes that I use in my cookbooks attributes\client.rb are as follows.

default['sql_server']['odbc']['url'] = Addressable::URI.convert_path( File.join( Chef::Config[:file_cache_path], 'cookbooks', 'otbo_sql_server', 'files', 'default', 'msodbcsql_17.3.1.1_x64.msi' ) ).to_s.gsub(/#/, '%23')
default['sql_server']['odbc']['checksum'] = 'cdff489db121cead78f87bc33f9fdb072432d637fb9c2905d0cbda2310f87086'
default['sql_server']['odbc']['package_name'] = 'Microsoft SQL Server - ODBC Driver 17.3.1.1'
default['sql_server']['odbc']['terms'] = 'IACCEPTMSODBCSQLLICENSETERMS'
default['sql_server']['odbc']['accept_eula'] = true

default['sql_server']['command_line_utils']['url'] = Addressable::URI.convert_path( File.join( Chef::Config[:file_cache_path], 'cookbooks', 'otbo_sql_server', 'files', 'default', 'MsSqlCmdLnUtils.msi' ) ).to_s.gsub(/#/, '%23')
default['sql_server']['command_line_utils']['checksum'] = '36d06ecf77ddb75a68f6bf3e0a7cec1ff5f3db1432672eb2950301bec2500bc1'
default['sql_server']['command_line_utils']['package_name'] = 'Microsoft SQL Server - Command Line Utilities 15'
default['sql_server']['command_line_utils']['terms'] = 'IACCEPTMSSQLCMDLNUTILSLICENSETERMS'
default['sql_server']['command_line_utils']['accept_eula'] = true

Apart from getting the install directory, I suppose that I'm not really using the sql_server cookbook, but perhaps in the future I'll be able to make heavier use of it.

Hope this is useful in some way.

Cheers,
Stuart

[TheLunaticScripter]

@mrswadge Sweet I'm glad you were able to get a workaround. So the direction of this cookbook is to move as much of the current recipe based content into custom resources. Currently, the server installation has been moved over but there hasn't been a demand to move the client install pieces over yet. I think using the library and then your own use of the package install is the best implementation and is going to give you the most flexibility. I'm going to keep this issue open just to track the bug but it will probably be a while before I can get around to that custom resource.