Last week, we discussed the ticking time bomb of hardcoded passwords and why secure secrets management is a non-negotiable skill for DBAs.  We identified Ansible Vault as the right tool for the job, but theory only gets you so far.

Today, we get our hands dirty.

We're going to take our first step toward eliminating plain-text credentials by learning the most fundamental Vault command: ansible-vault create.  This command enables you to create a new, encrypted file from the outset.  It's the perfect way to group related secrets, like all the credentials needed to provision a new SQL Server, into a single, secure container.

Scenario: A Standardized SQL Server Build

You've been tasked with creating a standardized, automated build process for all new SQL Servers in your environment.  To do this, you need a central and secure place to store several critical pieces of information for each new instance:

• The sa account password.
• The Integration Services Catalog password.
•  Windows logins to be added to the sysadmin role (not technically a password but probably something to encrypt nonetheless).

Where do you store this information?  A spreadsheet on a "secure" network share?  A .txt file on your laptop?  Both options are fragile, difficult to audit, and represent a significant security risk.  We need a better way.  A file that is secure by design and can live right alongside your automation code.

From Risky Text File to Secure Vault

This is where ansible-vault create transforms your workflow.  Instead of a high-risk career-limiting configuration file that you have to constantly protect, you will create a sql_secrets.yml file that is completely unreadable without the vault password.

Because it's encrypted, you can confidently check this file into your source control repository alongside your playbooks.  The raw file contains nothing but encrypted gibberish.  Security becomes an integral part of your automation, not a manual, error-prone step you have to remember later.  When auditors ask how you manage account credentials, you can show them a process that is secure, version-controlled, and auditable.

Your First Vault in 5 Steps

Let's walk through creating and using your first vault file.  Open your command-line terminal in your Ansible project directory and follow along (I'm using the built-in terminal within VS Code).

Step 1: Create the Encrypted File

Run the following command.  This tells Ansible you want to create a new encrypted file named sql_secrets.yml.

ansible-vault create sql_secrets.yml

You will immediately be prompted to create a password for this new vault.  Choose a strong one!  You will need this password every time you want to access this file's contents.

Step 2: Add Your Secrets

After you confirm the password, Ansible will open your system's default text editor (usually vi or vim).  It will be a blank file.  Add your SQL Server secrets using standard YAML key-value syntax:

# sql_secrets
sa_password: "YourSuperStrongSAPassword!"
ssisdb_password: "YourOtherSuperStrongPassword!"
sysadminaccount: "SANDBOX\\MyAccount"

Save the file and exit the editor. (In vi, you do this by hitting Esc, then typing :wq and pressing Enter).

Step 3: Verify the Encryption

Now, try to view the file's contents using a standard command like cat.

cat sql_secrets.yml

You won't see your passwords.  Instead, you'll see a block of encrypted text that starts with $ANSIBLE_VAULT.  This is your proof that the file is secure.

Step 4: Safely View and Edit Your Vault

So, how do you work with the file now?  Ansible provides safe commands for that.

• To view the decrypted contents: ansible-vault view sql_secrets.yml
• To edit the decrypted contents: ansible-vault edit sql_secrets.yml

Both commands will prompt you for the vault password before showing you the plain-text secrets.

Step 5: Use the Vault in a Playbook Securely

This is the magic.  How do we actually use these secrets securely?  It's critical to understand that just because a variable comes from a vault, Ansible won't automatically hide it in every situation.  You, the DBA, must be explicit about protecting secrets at the task level.

Let's create a playbook named test_vault.yml to demonstrate the wrong way and the right way to handle a secret variable.

---
- name: Demonstrate Secure vs. Insecure Handling of Vault Secrets
  hosts: localhost
  gather_facts: false
  vars_files:
    - sql_secrets.yml

  tasks:
    - name: "INSECURE: Displaying a secret variable"
      # This task is an example of what NOT to do.
      # The debug module will print the decrypted password to the screen and logs.
      ansible.builtin.debug:
        msg: "The SA password is {{ sa_password }}"

    - name: "SECURE: Using a secret variable with no_log"
      # This is the correct way. The task uses the password, but 'no_log: true'
      # prevents any output, ensuring the secret is never exposed.
      ansible.builtin.debug:
         msg: "The SA password is {{ sa_password }} 
      no_log: true

To run this playbook, use the --ask-vault-pass flag.

ansible-playbook test_vault.yml --ask-vault-pass

Ansible will prompt for the password.  Look closely at the output.  The first task insecurely prints the decrypted password directly to the console.  This is a major security risk.  The second task, however, simply reports "ok."  Because we added no_log: true, Ansible runs the task while completely suppressing its output, ensuring the secret is never exposed.

By adding no_log: true, you ensure that no details about that specific task run, including the secret password, are ever written to the console or logs.  This is the correct and secure way to handle sensitive variables.

Conclusion

You've done it!  You created a secure vault, stored sensitive data inside it, and used that data in a playbook while ensuring it remained a secret.

Encrypting a whole file is a massive step forward, but what if you only need to protect a single value inside a much larger, non-secret file? In the next post, we'll cover just that by learning how to perform granular encryption with ansible-vault encrypt_string. 

See you next week!