My Journey of Microsoft SQL Server Installs with Ansible Automation Platform

A practical guide to silent, repeatable SQL Server deployments across multiple versions — driven by Ansible Automation Platform.

Installing SQL Server by hand is one of those tasks that seems straightforward until you’ve done it a dozen times across different environments. Click through the wizard, pick your features, set your paths, wait fifteen minutes, and hope nothing went sideways. Multiply that by three SQL versions, a handful of target servers, and a mix of dev, UAT, and production environments — and it becomes a real operational burden.

This post walks through a complete automation solution: a PowerShell silent install script wrapped in an Ansible playbook structure, orchestrated through Ansible Automation Platform (AAP), with secrets managed properly and multi-version support baked in from the start.

The core approach

The solution is built around a master playbook that loads version-specific variable files at runtime, then delegates to a set of shared task files. AAP presents the operator with a simple survey question — which SQL version to install — and the rest resolves automatically.

sql_install/
├── install_sqlserver.yml       # master playbook
├── vars/
│   ├── common.yml              # shared defaults
│   ├── sql2019.yml             # 2019-specific paths & flags
│   ├── sql2022.yml
│   └── sql2025.yml
├── tasks/
│   ├── pre_install.yml         # checks & prep
│   ├── install.yml             # execution
│   ├── reboot.yml              # conditional reboot
│   └── post_install.yml        # validation
└── files/
    └── Install-SQLServer.ps1   # the install script

Adding a new SQL version in the future means creating one new vars file and adding one entry to the AAP survey. Nothing else changes.

The PowerShell layer

The install script handles the parts that Ansible can’t do natively on Windows: mounting ISOs, mapping UNC share paths with explicit credentials, assembling the setup.exe argument list, and capturing the exit code. SQL Server setup returns 0 for success and 3010 for success-with-reboot-required — anything else is a failure.

One real-world detail worth calling out: the ISO lives on a central file share rather than being copied to every target server. The script maps a temporary network drive using share credentials, runs the install, then cleans up — keeping target servers lean and avoiding the problem of stale ISO copies scattered across your fleet.

Handling secrets properly

A naive approach would put the SA password in a vars file or pass it as an extra variable. Both approaches leak secrets into job logs and source control. Instead, secrets are managed through a custom AAP credential type that injects values as environment variables at runtime:

env:
  SQL_SA_PASSWORD:    '{{ sql_sa_password }}'
  SQL_SHARE_USER:     '{{ sql_share_user }}'
  SQL_SHARE_PASSWORD: '{{ sql_share_password }}'

AAP automatically masks any field marked secret: true in job output. The install task itself also sets no_log: true to prevent argument values from being recorded. The result is that secrets exist only in AAP’s encrypted credential store and in the runtime environment of the job — never in logs, never in your repo.

💡 Tip: Use ansible.windows.win_shell rather than win_powershell when you need to check exit codes. The win_powershell module does not return an rc value — win_shell does.

A bug worth documenting

During testing, setup failed with a System.Security.Cryptography.CryptographicException: Access is denied error wrapped inside the generic “error generating the XML document” message. The stack trace pointed to ProtectedData.Protect being called when SQL Server tried to serialize the Agent service account password.

The root cause: Windows DPAPI requires a loaded user profile and access to the machine key store. WinRM sessions don’t load the user profile by default, so DPAPI has nowhere to write. The cleanest fix is to run setup via a scheduled task under the SYSTEM account, which has full DPAPI access. A quicker workaround is to use built-in accounts like NT AUTHORITY\NETWORK SERVICE for the Agent service — built-in accounts have no password, so DPAPI is never invoked.

What the operator experience looks like

From the AAP side, the operator opens the job template, sees a single survey dropdown asking for the SQL version, picks 2022 (or 2019 or 2025), and launches. AAP handles credential injection, WinRM connection, script deployment, ISO mounting, installation, conditional reboot, and post-install service validation — all logged and auditable in the job output.

The post-install validation confirms the SQL Server service is running and calls SELECT @@VERSION via sqlcmd to verify the correct build was installed. If anything fails, the setup bootstrap logs are fetched back to the AAP controller automatically.

Takeaways

The pattern here — master playbook, version-specific vars files, shared task files, secrets via custom credential types — scales cleanly beyond SQL Server. It works for any Windows software that has a silent install mode and version-specific configuration. The investment in getting the structure right pays off every time a new server needs to be provisioned or a version upgrade rolls through your environment.