Generating credentials for MSSQL on AWS RDS, one host to many databases

[adamgrasso]

Is your feature request related to a problem? Please describe. Our current AWS RDS environment has a single MSSQL RDS instance with multiple databases on it. In the existing workflow, users must request a set of credentials for each database on the RDS instance.

This is fine for end users that only need access to a single database for a single application.
It's not ideal for administrative users who many have to run migrations across multiple databases at once.

Describe the solution you'd like The ideal solution is one of the following -

1 - Vault is made aware of all databases on an MSSQL RDS instance via configuration, or can generate a list of all user (non-system) databases. Once that list is known, Vault can create a set of credentials with the same level of access (readonly, read/write, owner) to all databases on the list without further prompting.

2 - Vault allows the creation_statement to contain more complex T-SQL code, so that we can build the appropriate commands ourselves to do the same process, similar to the following

` creation_statements = <<-SQL USE [master];

  CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
  DECLARE @dbname VARCHAR(50)   
  DECLARE @statement NVARCHAR(max)

  DECLARE db_cursor CURSOR 
  LOCAL FAST_FORWARD
  FOR  
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master','model','msdb','tempdb','distribution','rdsaadmin')  
  AND state_desc = 'ONLINE'
  OPEN db_cursor  
  FETCH NEXT FROM db_cursor INTO @dbname  
  WHILE @@FETCH_STATUS = 0  
  BEGIN  

  SELECT @statement = N'use ['+ @dbname +'];'+ 'CREATE USER ['+ {{name}} +'] FOR LOGIN [' + {{name}} +'];EXEC sp_addrolemember N''db_owner'', [' + {{name}} +']' 
  exec sp_executesql @statement

  FETCH NEXT FROM db_cursor INTO @dbname  
  END  
  CLOSE db_cursor  
  DEALLOCATE db_cursor 

  ALTER SERVER ROLE [localadmin] ADD MEMBER [{{name}}];

  SQL

`

Describe alternatives you've considered We have attempted option 2 above, however Vault does not allow the code to run. We have not been able to figure out why. We also have the option of manually maintaining the creation_statement to include all the necessary databases per instance, but that is tedious to maintain as databases may be added/removed as needs change.

[jasonodonnell]

We have attempted option 2 above, however Vault does not allow the code to run. We have not been able to figure out why.

@adamgrasso Are there any errors returned by Vault?

[rossmohsenzadeh]

Hey Jason, I'm under Adam and was the one that created that script. I tried changing the quotes to brackets but that didn't work. All I get when I try and access it is : We ran into a problem and could not continue: 1 error occurred: * mssql: Incorrect syntax near ']'.

      USE [master];

      CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
      DECLARE @dbname VARCHAR(50)   
      DECLARE @statement NVARCHAR(max)

      DECLARE db_cursor CURSOR 
      LOCAL FAST_FORWARD
      FOR  
      SELECT name
      FROM sys.databases
      WHERE name NOT IN ('master','model','msdb','tempdb','distribution','rdsadmin')  
      AND state_desc = 'ONLINE'
      OPEN db_cursor  
      FETCH NEXT FROM db_cursor INTO @dbname  
      WHILE @@FETCH_STATUS = 0  
      BEGIN  

      SELECT @statement = N'use ['+ @dbname +'];'+ '
      CREATE USER ['+ {{name}} +'] FOR LOGIN [' + {{name}} +'];
      EXEC sp_addrolemember N''db_owner'', [' + {{name}} +'];' 
      exec sp_executesql @statement

      FETCH NEXT FROM db_cursor INTO @dbname  
      END  
      CLOSE db_cursor  
      DEALLOCATE db_cursor 

      ALTER SERVER ROLE [cesadmin] ADD MEMBER [{{name}}];

[rossmohsenzadeh]

Let me know if you want to get a working session together on this. I'm pretty open this week so it is at your convivence and only if you think it would be beneficial.

[jasonodonnell]

@rossmohsenzadeh I was able to reproduce my on end and the following SQL is causing the error:

      SELECT @statement = N'use ['+ @dbname +'];'+ '
      CREATE USER ['+ {{name}} +'] FOR LOGIN [' + {{name}} +'];
      EXEC sp_addrolemember N''db_owner'', [' + {{name}} +'];' 
      exec sp_executesql @statement

I'm looking for ways to optimize it because it seems the quoting is causing issues with the driver we use (go-mssqldb).

[rossmohsenzadeh]

So the quoting to make the dynamic sql is what is bombing out due to the driver. So at this point we just don't know why it is causing a problem when the syntax is correct?

[rossmohsenzadeh]

Also, thank you for your quick work on this.

[jasonodonnell]

I'm not sure why the driver doesn't like that SQL but I will look more closely tomorrow. Thanks for providing additional details.

[jasonodonnell]

@rossmohsenzadeh I looked into this a bit more and found potentially the issue. I added some debugging code to the plugin to output the query after Vault has filled in the templates. It seems to only return part of the query:

USE [master]
CREATE LOGIN [v-token-my-role-wOvYvmJez6cPI0dEPPgp-1671497305] WITH PASSWORD = 'c8UPZa-mfXguVzCpryb9'
DECLARE @dbname VARCHAR(50)
      DECLARE @statement NVARCHAR(max)

      DECLARE db_cursor CURSOR
      LOCAL FAST_FORWARD
      FOR
      SELECT name
      FROM sys.databases
      WHERE name NOT IN ('master','model','msdb','tempdb','distribution','rdsadmin')
      AND state_desc = 'ONLINE'
      OPEN db_cursor
      FETCH NEXT FROM db_cursor INTO @dbname
      WHILE @@FETCH_STATUS = 0
      BEGIN

      SELECT @statement = N'use ['+ @dbname +']

This seems to happen when Vault is parsing the query and looking for separate statements via semi-colons: https://github.com/hashicorp/vault/blob/main/plugins/database/mssql/mssql.go#L145. It doesn't like that subquery for some reason and is discarding the rest of it. I'm still trying to understand why that's happening.

That being said, I'm wondering if this could be turned into a function in MS SQL:

      DECLARE @dbname VARCHAR(50)   
      DECLARE @statement NVARCHAR(max)

      DECLARE db_cursor CURSOR 
      LOCAL FAST_FORWARD
      FOR  
      SELECT name
      FROM sys.databases
      WHERE name NOT IN ('master','model','msdb','tempdb','distribution','rdsadmin')  
      AND state_desc = 'ONLINE'
      OPEN db_cursor  
      FETCH NEXT FROM db_cursor INTO @dbname  
      WHILE @@FETCH_STATUS = 0  
      BEGIN  

      SELECT @statement = N'use ['+ @dbname +'];'+ '
      CREATE USER ['+ {{name}} +'] FOR LOGIN [' + {{name}} +'];
      EXEC sp_addrolemember N''db_owner'', [' + {{name}} +'];' 
      exec sp_executesql @statement

      FETCH NEXT FROM db_cursor INTO @dbname  
      END  
      CLOSE db_cursor  
      DEALLOCATE db_cursor 

By turning that into a function, we can bypass any weirdness around parsing the complex query and simplify the creation statement to something like:

CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
SELECT customFunction('{{name}}');
ALTER SERVER ROLE [cesadmin] ADD MEMBER [{{name}}];

Thoughts?

[rossmohsenzadeh]

I like it. That does simplify things and I'm always happy to simplify where possible. Let me test that here and I'll get back to you shortly.

[rossmohsenzadeh]

Sorry for the delay on replying to this. We've been on holiday for the past several days. Would this be better served as a stored procedure or how would you imagine this getting put into a function that returns nothing? I tested this and I'm not sure why it isn't working. Here is the stored procedure I attempted:

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[adduserdbo] @name as nvarchar(255)
AS
BEGIN
      DECLARE @dbname VARCHAR(50)   
      DECLARE @statement NVARCHAR(max)

      DECLARE db_cursor CURSOR 
      LOCAL FAST_FORWARD
      FOR  
      SELECT name
      FROM sys.databases
      WHERE name NOT IN ('master','model','msdb','tempdb','distribution','rdsadmin','10012_LorenzoTest')  
	  and name not like ('%Aberdeen%')
      AND state_desc = 'ONLINE'
      OPEN db_cursor  
      FETCH NEXT FROM db_cursor INTO @dbname  
      WHILE @@FETCH_STATUS = 0  
      BEGIN  

      SELECT @statement = N'use ['+ @dbname +'];'+ '
	  IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = '''+@name+''')
	  BEGIN
      CREATE USER ['+ @name +'] FOR LOGIN [' + @name +'];
	  WAITFOR DELAY ''00:00:00:50'';
      EXEC sp_addrolemember N''db_owner'', [' + @name +'];
	  END' 
      exec sp_executesql @statement

      FETCH NEXT FROM db_cursor INTO @dbname  
      END  
      CLOSE db_cursor  
      DEALLOCATE db_cursor 
END 
GO

And here is what I have in the hcl file:

environment "qa" {
  mount "core-rds-ces" {
    role "cesadmin" {
      db_name     = "default"
      default_ttl = "336h"
      max_ttl     = "504h"

      creation_statements = <<-SQL
      USE [DBA];
      EXEC dba.dbo.adduserdbo [{{name}}];

      ALTER SERVER ROLE [cesadmin] ADD MEMBER [{{name}}];

      SQL
    }
  }

  policy "db-qa-core-rds-ces-cesadmin" {
    path "core-rds-ces/creds/cesadmin" {
      capabilities = ["read"]
    }
  }

  secret "/auth/ldap/groups/sql-qa-core-us" {
    policies = "db-qa-core-us"
  }
}

Thoughts?

[rossmohsenzadeh]

Sorry, I forgot to leave the error message: We ran into a problem and could not continue: 1 error occurred: * mssql: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

[jasonodonnell]

@rossmohsenzadeh Sorry, I was out on holiday as well, but am back now!

I just tested your stored procedure above and it works fine for me. 🤔

Can you show me about the output of the role in Vault? IE something like: vault read database/roles/my-role

[rossmohsenzadeh]

That is interesting. I wonder if there is an issue with the mount itself. Here is the error I get when running that stored procedure and try to get the vault role

We ran into a problem and could not continue: 1 error occurred: * mssql: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0

[jasonodonnell]

@rossmohsenzadeh Can you show me the output of the database role you created? Looks like it should be:

vault read core-rds-ces/roles/cesadmin

[rossmohsenzadeh]

Something must be wonky with my setup because this is what I get when I run that command: Error reading core-rds-ces/roles/cesadmin: Get "https://127.0.0.1:8200/v1/core-rds-ces/roles/cesadmin": dial tcp 127.0.0.1:8200: connect: connection refused

[jasonodonnell]

Is Vault running locally? If not you need to set the VAULT_ADDR environment variable:

$ export VAULT_ADDR=https://<HOSTNAME OR IP OF VAULT>:8200
$ vault login
$ vault read core-rds-ces/roles/cesadmin

[rossmohsenzadeh]

Ah thank you for that. Here is the output:

rmohsenzadeh@LAPTOP-6GOBNPPE:/mnt/c/users/rmohsenzadeh/documents/github2/hashi-config$ vault read core-rds-ces/roles/cesadmin
Key                      Value
---                      -----
creation_statements      [USE [DBA];
      EXEC dba.dbo.adduserdbo [{{name}}];

      ALTER SERVER ROLE [cesadmin] ADD MEMBER [{{name}}];]
db_name                  default
default_ttl              336h
max_ttl                  504h
renew_statements         []
revocation_statements    []
rollback_statements      []

[rossmohsenzadeh]

One thing that is odd is that output shows that that there is an extra bracket here: ALTER SERVER ROLE [cesadmin] ADD MEMBER [{{name}}];] But when I'm looking at the config I see only

      creation_statements = <<-SQL
      USE [DBA];
      EXEC dba.dbo.adduserdbo [{{name}}];

      ALTER SERVER ROLE [cesadmin] ADD MEMBER [{{name}}];

[jasonodonnell]

@rossmohsenzadeh That's just the CLI rendering of a multi-line string and can be ignored. Everything between the outer brackets is the SQL that will run.

This output looks correct. I'm wondering if you tried to run the stored procedure outside of Vault (IE with sqlcmd or some other tool)? Additionally, is it possible to read the stored procedure for MS SQL and provide the output to ensure it was created correctly in the database?

[rossmohsenzadeh]

I'll run through the permissions and things again because if it works on your end then it should work on mine. I'll do a sanity check and get back to you.

[jasonodonnell]

Hi @rossmohsenzadeh, just checking in to see if you had any success getting this to work?

[rossmohsenzadeh]

Not yet but I'm going to make some changes today and see if that resolves the issue.

On Fri, Jan 6, 2023 at 11:16 AM jasonodonnell @.***> wrote:

Hi @rossmohsenzadeh https://github.com/rossmohsenzadeh, just checking in to see if you had any success getting this to work?

— Reply to this email directly, view it on GitHub https://github.com/hashicorp/vault/issues/18453#issuecomment-1373912390, or unsubscribe https://github.com/notifications/unsubscribe-auth/AZQ5OJCSNEIHCIZ5NBJFXVTWRBHQLANCNFSM6AAAAAATBQDJVY . You are receiving this because you were mentioned.Message ID: @.***>

--

Ross Mohsenzadeh

DBRE, Database Reliability Engineering, SeatGeek

@.***

(615) 578-2229

2908 Stewart Campbell Pointe

Thompsons Station, TN 37179

[rossmohsenzadeh]

Sorry for the delay. I've had a couple other priorities pop up but I plan on coming back to this work.