Over the past few weeks, we've explored Ansible Vault, learning about its purpose and how it can be utilized.  You can create fully encrypted files and perform granular, inline encryption on single variables.  Your secrets, hopefully, are no longer stored in plain text.

But there's still one manual step holding us back: every time we run a playbook, we have to type --ask-vault-pass and manually enter the password.

This is fine for interactive work and testing, but it's a showstopper for real-world automation.  How are you supposed to set up a self-service deployment of SQL Server if a DBA needs to be involved to type in a password?  How does a CI/CD pipeline deploy a database if it gets stuck waiting for human input?

To achieve true automation, we need to remove the final manual step.  Today, you'll learn how to do that by using a vault password file.

Scenario: Server Builds

Last week we used an example of building 25 new SQL Server 2025 instances.  In that example you were still required to use the ansible-vault password interactively.  But what if you had multiple environments to build, or were testing changes to the variables and wanted to test multiple times?  You'd need to enter the password each time.  That gets annoying after a few times.

Here's the example variable file we built in the previous post:

# The method to install PowerShell modules. Can be 'gallery' from PowerShell Gallery or 'local' from a local path on the Ansible controller.
manage_powershell_modules_install_method: 'local'

# If 'manage_powershell_modules_install_method' is set to 'local', specify the path on the Ansible controller where the module files are located.
# Example: '/path/to/your/modules'
manage_powershell_modules_local_path: '/home/lcampbell/'

# A temporary folder used for staging the modules before installation.
manage_powershell_modules_temp_folder: 'C:\temp\modules'

sql_install_edition: "Enterprise Developer"
sql_install_issvcaccount: NT Service\MsDtsServer170
sql_install_features: SQLENGINE,REPLICATION,FULLTEXT,IS
sql_install_instance_name: MSSQLSERVER
sql_install_installshareddir: C:\Program Files\Microsoft SQL Server
sql_install_installsharedwowdir: C:\Program Files (x86)\Microsoft SQL Server
sql_install_sqlcollation: SQL_Latin1_General_CP1_CI_AS
sql_install_installdb_path: "E:"
sql_install_instance_dir: C:\Program Files\Microsoft SQL Server
sql_install_userdb_path: E:\SQLDATA
sql_install_userdblog_path: F:\SQLDATA
sql_install_tempdb_path: T:\SQLDATA
sql_install_tempdblog_path: T:\SQLDATA
sql_install_backup_path: T:\BACKUP
sql_install_tempdblogfilesize: 256
sql_install_tempdblogfilegrowth: 64 # setting to 64mb to take advantage of IFI for log files in SQL Server 2022 and later.
sql_install_tempdbfilecount: 4
sql_install_tempdbfilesize: 256
sql_install_tempdbfilegrowth: 256

sql_install_sql_svc_account: 'SANDBOX\svc_sqlServer'
sql_install_sql_svc_password: !vault |
          $ANSIBLE_VAULT;1.1;AES256
          64333631666632313337393161373462626465626131633037366636623935663763393939363838
          3232326639343736316261356262356164613639656265350a663034613562323663663636663262
          33386566643862646363366437333135366330383332393261656565626666633230353039353131
          3861303731626366640a636434353535636134383233666532616466313734643131383661363466
          3330
sql_install_agent_svc_account: 'SANDBOX\svc_sqlagt'
sql_install_agent_svc_password: !vault |
          $ANSIBLE_VAULT;1.1;AES256
          63353630626234343565626666646132386339383466343165316635363036306334386134663731
          6364366631356336373938353535633966613237323836300a353531663231333466623636353530
          64363333636436363466646362363339343364633963633932373736386666666131353731636431
          3833376538333137350a623334303533323264393535656136346663633433653532343236313335
          38396639643037393163663530666330336266326664666262626161386265616438

What is a vault password file?

The vault password file is a simple text file that contains your vault password, which you can then reference from the command line.  Instead of prompting you, Ansible reads the password directly from the file.

You should treat these files just as you would ssh keys.  Keep them secure and out of source control.  

We'll reuse the simple test_inline_vault.yml playbook we built in the previous post.  But, this time we'll leverage a password file and skip the --ask-vault-pass parameter at runtime.

Step 1: Create the Vault Password File

I'd recommend keeping this file separate from your Ansible project folder to avoid accidentally committing it to source control.  I'll create a new file, named vault_pass.txt, in the .ansible folder in my home directory.  This folder is automatically created by Ansible to store local runtime data.  It's not part of your playbooks or collections.

From the terminal, run touch ~/.ansible/vault_pass.txt.

Inside this file, add only one thing: the vault password you created in the previous posts.  The file should contain just the password string and nothing else.  Save it.

Step 2: Secure the Password File

If you don't secure it, you've defeated the entire purpose of using a vault.  You must restrict its permissions so that only you (or the account running Ansible) can read it. 

At the terminal run chmod 600 ~/.ansible/vault_pass.txt.  This will set read/write permissions for the owner only (you).  Not familiar with chmod?  I've found this site to come in handy - https://chmodcommand.com/chmod-600/.

Next, run ls -l ~/.ansible/vault_pass.txt to verify the permissions.  You should see -rw yourusername yourusername in the output.

Don't skip this step.  This file is just as dangerous as a hardcoded password in a script if not secured.  Consider full disk encryption or encrypting your home directory to ensure your personal data is secure.

Step 3: Run the Playbook with the Password File

Now, let's run the playbook from the last post, test_inline_vault.yml.  If you were deploying SQL Server, using the automatesql.mssql.sql_install role, you'd run the associated playbook for that role along with the password file.

ansible-playbook test_inline_vault.yml --vault-password-file ~/.ansible/vault_pass.txt

Step 4: Verify 

Look at the output.  The playbook runs exactly as before, but without prompting you for a password.  Ansible read the password from your secured file, decrypted the secrets in memory, and ran the playbook without requiring any interaction.  You can now use this command in any scheduling tool or build loop to achieve true, unattended automation.

You now have a secure, repeatable, and fully automatable process for managing secrets. This approach works great for individual DBAs and testing environments. For production systems, consider graduating to enterprise secret management tools like HashiCorp Vault, Ansible Automation Platform, or cloud provider secret managers for added security, audit logging, and team collaboration features.

But can we make it even more seamless?  What if you didn't have to type --vault-password-file ~/.ansible/vault_pass.txt every single time?

In the final post of this series, we'll cover a best practice that does just that: using environment variables to make Ansible automatically find your vault password.

See you next week!