Issue with Vault Revocation statement dropping a db user in MS SQL Server

Vault
1

We are using ansible vault to create logins on SQL Server. Vault does not seem to be drop the database role gracefully when the revocation statements are specified as below

creation_statements: ["{% raw %} CREATE LOGIN [{{name}}] WITH PASSWORD = ‘{{password}}’; {% endraw %}
USE [{{ item.database }}];
{% raw %} CREATE USER [{{name}}] FOR LOGIN [{{name}}];{% endraw %}
GRANT {{ item.permission}} ON SCHEMA :: {{ item.schema }} {% raw %} TO [{{name}}]; {% endraw %}"]
revocation_statements: [“USE [{{ item.database }}];
{% raw %} DROP USER [{{name}}];
DROP LOGIN [{{name}}];{% endraw %}”]
token_ttl: “{{ item.token_ttl }}”
with_items:
- “{{ vault_config_sql_server }}”

The login is dropped from sql server but the database user still exists.

When the Revoacation statement is completely omitted,

Successfully dropped login and db user - Only When the Vault root user is sysadmin
Disabled the login and orphan db user still exists - When the Vault root user is not sysadmin

Ideally, we do not want the Vault root user to be sysadmin on the SQL Server.
Any way to fix this problem?

2

I was able to fix this issue. The issue is with a silly semicolon “;” after USE database command in Revocation statements. The connection to the database is being reset as there is a spilt with a semicolon. Strangely, this does not happen in the creation statements.
It works good, after removing the semicolon from the revocation statements.

Hope this helps someone!

3 Likes
3

Thanks for posting that tip. Removing that semicolon from the after USE on revocation worked great! The login and DB user are revoked properly. Only annoying bit is user DB role mapping fails unless the Vault user (MS SQL User) has at least SQL Server role securityadmin AND db_owner role on each individual DB. No other SQL server role or individual DB role or server permission permits the Vault user to see the roles in the DB. Would rather not make Vault user db_owner but the db_accessadmin and db_securityadmin roles specified in HashiCorp doc do not work to map the DB role to the Vault generated SQL user.