Installing SQL 2016 using Ansible

Over the years we have used a number of methods to orchestrate our server builds.  Supporting both windows and linux environments for our clients we have used a mixture of technologies including Ansible for Linux.  Earlier this year we started to use Ansible for windows and have had some success.  However until today one installation has eluded us, is an idempotent install of SQL 2016 Server.  We have read many article found via Google with users having similar issues.

This blog assumes you have the same issue and makes some presumptions.

  1. You can successfully run a silent install of SQL server outside of ansible
  2. You have enabled ‘CredSSP’ for authentication
  3. You can successfully run an install of SQL server using psexec within Ansible (not idempotent)

Errors

Ansible reported:

TASK [dbtier : Install SQL2016 from newly mounted ISO] *******************************
fatal: [ip-address]: FAILED! => {"changed": false, "exit_code": -2022834173, "failed": true, "msg": "The return code -2022834173 was not expected. Configuration is likely not correct", "name": "E:\\setup.exe"}

where E: drive was the location of our ISO mounted installation.

SQL Installer Reported:

Logon to server and view

C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\summary.txt

 Data: 
 WorkflowId = SEARCHUPDATES
 WatsonData = Microsoft.SqlServer.Chainer.ExtensionCommon.AsyncWorkflowExecutionException@1
 Inner exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException
 Message: 
 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

References

The were many articles we have read in the solving of this issue, the main ones we referenced are listed below.