Secret Engine SQL Server for AWS RDS

Vault
1

Hi community, I’m trying to implement a secrets using Vault and secret engine to SQL Server for AWS RDS instances for all databases

Until now we created a user inside AWS RDS for vault to create the random users/password

Right now i can reach to get access with random user and random password but only to engine the engine, AWS RDS for SQL Servers has a particularity issue, when we import some database we need to give the dbo owner to this dbmsadmin1 to can manage after. (this is not a problem because we do this manually when some database is imported)

the query in Vault we use to get access and for revoke are theses:

        vault_creation_statements_ge2012: !unsafe
          - "USE [master];"
          - "CREATE LOGIN [{{name}}] WITH PASSWORD=N'{{password}}', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;"
          - "ALTER SERVER ROLE [processadmin] ADD MEMBER [{{name}}];"
          - "ALTER SERVER ROLE [setupadmin] ADD MEMBER [{{name}}];"
          - "GRANT ALTER ANY CONNECTION TO [{{name}}];"
          - "GRANT ALTER ANY LOGIN TO [{{name}}] WITH GRANT OPTION;"
          - "GRANT ALTER SERVER STATE TO [{{name}}];"
          - "GRANT ALTER TRACE TO [{{name}}];"
          - "GRANT CONNECT SQL TO [{{name}}];"
          - "GRANT CREATE ANY DATABASE TO [{{name}}];"
          - "GRANT VIEW ANY DATABASE TO [{{name}}];"
          - "GRANT VIEW ANY DEFINITION TO [{{name}}];"
          - "GRANT VIEW SERVER STATE TO [{{name}}];"
          - "USE [msdb];"
          - "CREATE USER [{{name}}] FOR LOGIN [{{name}}];"
          - "GRANT EXECUTE ON msdb.dbo.rds_backup_database TO [{{name}}];"
          - "GRANT EXECUTE ON msdb.dbo.rds_restore_database TO [{{name}}];"
          - "GRANT EXECUTE ON msdb.dbo.rds_task_status TO [{{name}}];"
          - "GRANT EXECUTE ON msdb.dbo.rds_cancel_task TO [{{name}}];"
          - "GRANT SELECT ON dbo.sysjobs TO [{{name}}];"
          - "GRANT SELECT ON dbo.sysjobhistory TO [{{name}}];"
          - "GRANT SELECT ON msdb.dbo.sysjobactivity TO [{{name}}];"
          - "ALTER ROLE [SQLAgentUserRole] ADD MEMBER [{{name}}];"
          - "GRANT ALTER ON ROLE::[SQLAgentOperatorRole] to [{{name}}];"

        vault_revocation_statements: !unsafe >-     ### beware: remove all spaces on each EOL
          USE [msdb]
          IF EXISTS (SELECT name FROM sys.database_principals WHERE name = N'{{name}}')
          BEGIN
            DROP USER [{{name}}]
          END
          USE [master]
          IF EXISTS (SELECT name FROM master.sys.server_principals WHERE name = N'{{name}}')
          BEGIN
            DECLARE @kill varchar(8000) = ''
            SELECT @kill = @kill + 'kill ' + CAST(session_id as varchar(200)) FROM sys.dm_exec_sessions WHERE login_name = '{{name}}'
            EXEC(@kill)
            DROP LOGIN [{{name}}]
          END

But… we want to create a random user/password for databases already created before so we need to add this new SQL Query script to solve wich is works in AWS RDS but when we need to insert inside to Vault is where we are stuck.

      - "DECLARE @command varchar(8000)"
      - "SELECT @command = 'USE [master]"
      - "IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''Resource'', ''distribution'' , ''reportserver'', ''reportservertempdb'', ''rdsadmin'')"
      - "BEGIN"
      - "  USE ?"
      - "  EXEC(''CREATE USER [dbmsadmin1] FOR LOGIN [dbmsadmin1] WITH DEFAULT_SCHEMA = [dbo]'')"
      - "  EXEC(''sp_change_users_login @Action=''''update_one'''', @UserNamePattern=''''dbmsadmin1'''', @LoginName=''''dbmsadmin1'''';'')"
      - "  EXEC(''sp_addrolemember N''''db_owner'''', N''''dbmsadmin1'''''')"
      - "END'"
      - "EXEC sp_MSforeachdb @command"

maybe some ’ " escapes can’t solve it because we are getting this error when ask to vault to read the creds to role

Error reading secret-engine-sqlserver/creds/Mssql1Admin: Error making API request.

URL: GET https://vault.domain.com/v1/secret-engine-sqlserver/creds/MssqlAdmin1
Code: 500. Errors:

* 1 error occurred:
        * mssql: Incorrect syntax near 'USE [master]'.

the script to run in AWS RDS fine is this one:

DECLARE @command varchar(8000)
SELECT @command = 'USE [master]
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''Resource'', ''distribution'' , ''reportserver'', ''reportservertempdb'', ''rdsadmin'')
BEGIN
  USE ?
  EXEC(''CREATE USER [dbmsadmin1] FOR LOGIN [dbmsadmin1] WITH DEFAULT_SCHEMA = [dbo]'')
  EXEC(''sp_change_users_login @Action=''''update_one'''', @UserNamePattern=''''dbmsadmin1'''', @LoginName=''''dbmsadmin1'''';'')
  EXEC(''sp_addrolemember N''''db_owner'''', N''''dbmsadmin1'''''')
END'
EXEC sp_MSforeachdb @command

I’m trying to putting scapes using more quotes " or singles ’ or scaping \ . any help ???

thanks in advance

2

The problem I see is that you’re breaking a single SELECT command into multiple commands (each line starting with - is interpreted as a new statement).
You could try using heredoc syntax for that specific statement.

3

Hi @macmiranda
thanks for reply

yes is possible would be interrupt in someway the 'SELECT line with other line…
i’m using ansible to make this deployment and have to integrate even heredoc with yaml syntax so… I can reach to create the random user/password and delete with revoke statements

had to implement other way similar heredoc in ansible way.