Ansible By Example - How I Use Ansible Vault

Edition: Saturday, June 29th, 2024

Storing passwords in plain text files is a big problem.  Such as the SA password when working with SQL Server.   But how do you automate the installation of SQL Server and store the password securely?

If you're using Ansible, it's easy.  

Let's jump in. 

What is Ansible vault?

Ansible vault is a feature within Ansible that allows you to keep sensitive data such as passwords, API keys, and other credentials securely encrypted.  This is very useful for managing sensitive information that needs to be shared across playbooks without exposing it in plain text.  

For example, if you're building a playbook, or role, to install SQL Server across multiple servers using mixed mode authentication.

You would need a way to set the SA password during the installation.  The password could be provided in the --extra-vars argument at the command line. 

Or stored in a var file that the playbook uses.  But var files are plain text yaml.  Not secure and if added to a code repository, your SA password is available for everyone to see.

Vault uses AES256 encryption to encrypt whole files or sensitive strings.  

Key Features:

• Encryption and Decryption
  
  •  You can encrypt entire files or specific variables within your playbooks.
  • The encrypted data is stored securely and can only be decrypted using a specific password (vault password) or encryption key.
• Command Line Interface:
  • Ansible Vault provides commands to create, edit, encrypt, decrypt, and rekey vault files.  You can view these commands, along with a helpful description, by running ansible-vault -h at the command line.
    • 
• Vault Password Management:
  • You can manage passwords used for encryption by storing them in files or passing them via command line or environment variables.  This adds flexibility in how you manage and share the vault password securely.  See Managing vault passwords — Ansible Community Documentation for more details on vault password management.
• Integration with Playbooks:
  • Vault files can be seamlessly integrated into your playbooks.  When running a playbook that references encrypted files, Ansible will prompt for the vault password, decrypt the files in memory, and proceed with playbook execution.

How I use Ansible Vault

Here's a quick example of how I'm using Ansible vault.  

I have a playbook, playbook_installSQLServer_vault.yml which uses two var files. 

• sql_install.yml - Sets the number of tempdb files.
• sql_install_sensitive.yml - Contains the following variables. 
  • rl_mssql_sapwd: "Re@llyS4cureP@ss"
    rl_mssql_sysadminaccount: "HOMELAB\\Luke"

Encrypting the var file and running the playbook

During installation, I'm using the second var file to set the SA password and add my domain account to the sysadmin role.  It's possible that I could be building numerous instances at once, all having the same SA password and increasing the importance of securing this file.

Let's encrypt sql_install_sensitive.yml and take a look at the result.

Command - ansible-vault encrypt vars/sql_install_sensitive.yml

Next, we're prompted to provide a vault password (remember this as you'll need it to run the playbook or decrypt the file).

If we look at the sql_install_sensitive.yml file we'll see the contents have been encrypted.

When running the playbook, playbook_installSQLServer_vault.yml, we'll need to provide the vault password.  We do that by adding --ask-vault-pass argument.  There's other ways to do this, such as using an environment variable or password file but it's not covered here.

ansible-playbook playbook_installSQLServer_vault.yml -u [email protected] --ask-pass  --ask-vault-pass

Decrypting the var file

To decrypt the var file, you can use ansible-vault decrypt vars/sql_install_sensitive.yml.  Provide the password when prompted.

Below is the lab environment I'm using, built using the automated sandbox framework.

Environment:

Here's how I have my lab configured.  If you build one to follow along in the examples, be sure to create snapshots first.  This way you can easily reset your environment for the next walkthrough.

• Hardware - Geekom A7 Mini PC Ryzen - 64 GB RAM / 2 TB drive
• Hypervisor - VMware Workstation Pro 17.5 (get this for free if you're using it for personal use - VMware Workstation Pro: Now Available Free for Personal Use)
• Virtual Machines - All built using a single Windows Server 2022 Standard (eval) Hashicorp Packer image and managed with Vagrant.
  • DC1 - Domain Controller (HOMELAB.LOCAL)
  • CA1 - Certificate Authority
  • SRV1 - SQL Server (eventually) - Contains the OS drive and 3 uninitialized NVMe disks.
  • SRV2 - SQL Server (eventually) - Contains the OS drive and 3 uninitialized NVMe disks.
  • SRV3 - Client machine - Windows Subsystem for Linux (Ubuntu) - Ansible Control Node.  Visual Studio Code is installed and used to create the playbooks throughout this series.

Conclusion:

As DBAs, we're tasked with securing one of the most important assets for companies we work for; data.  In fact, we're responsible for more servers, instances, and cloud resources than ever. 

I don't think that's going to change.  Learning how to automate much of the management and configuration of SQL Server is paramount.  But, we must do it securely.

You can grab the example code at Ansible-for-SQL-Server-DBAs/Ansible-Vault.

If you want to see these steps in a video, check out Episode 5 in the Ansible For SQL Server DBAs playlist - Episode 5 - How to use Ansible Vault (youtube.com)

 Ansible For SQL Server DBAs (join the wait list) - https://www.automatesql.com/ansible. 

Thanks for reading!  Have a great week!