MSSQL backend returning 500 error when retrieving credentials

[awells3]

I recently updates my Vault set up to version 0.7.0 and I am now getting errors when trying to get credentials from my MSSQL backend:

Role configuration:
"CREATE LOGIN [{{name}}] WITH PASSWORD= '{{password}}'; USE test; CREATE USER [{{name}}] FOR LOGIN [{{name}}]; GRANT SELECT ON SCHEMA::dbo TO [{{name}}]"

Connection configuration:

connection_string = server={redacted};port:1433;user id={redacted};password={redacted};database=test;app name=vault;
max_open_connections = 2

The configuration comes directly from the walk through in the documentation for the MSSQL Secret Backend.

I was not having this issue until I updated to version 0.7.0.

The exact error is:

Error reading mssql/creds/test: Error making API request.

URL: GET {URL}
Code: 500. Errors:

 *1 error occurred:

* sql: expected 1 arguments, got 0

[jefferai]

@calvn @chrishoffman Does this seem related to that issue you found when looking at the physical backend that was submitted for 0.7.1?

[chrishoffman]

This is related to the same issue we found while working the the db backends. The issue is with colons in the SQL which get parsed as parameters. The workaround is to use a SQL statement in the role configuration that does not use colons which will not always be possible. I will be working on a fix for this issue.

[awells3]

Thanks! I changed the role configuration to:

CREATE LOGIN [{{name}}] WITH PASSWORD= '{{password}}'; USE test; CREATE USER [{{name}}] FOR LOGIN [{{name}}]; GRANT SELECT TO [{{name}}]

and it now seems to work.

I have run into another problem though. Even after the lease expires I am still able to use the login to connect to the database and retrieve information. When I check the logins for the SQL Server, I see the login created by vault is 'Disabled' but when I check the users for the database itself (test) the user still appears there.

The user creating the logins from vault has the following roles:

• securityadmin and processadmin for the server
• db_accessadmin and db_securityadmin for the test database

Is there something wrong in my configuration that is keeping the logins and users on the SQL server?

The weird thing is, if I use vault to create a user/login for the master database then the user and login is deleted from the database and server respectively.

[chrishoffman]

Revoking is a little tricky on the sql server side due to process, database users, and logins. I'll check if there is an issue there but are your server logs showing any errors when the lease expires? That would be helpful for us to track down the issue.

[awells3]

I did more testing and the error was on my part, the connection string I had was using my windows credentials instead of the user id and password I was passing in the connection string (had Trusted_Connection=true in my connection string).

The login is disabled which prevents the user from connecting to SQL. So that is good!

The only thing is the login and user appearing in the user and login list for the database and server. If this is the intended behavior then that is what it is and I could look into writing a clean up script. I looked in the SQL logs and windows logs but only saw errors when trying to connect with the disabled account. I did not see any errors for not being able to delete the SQL server login or the database user.

[awells3]

Sorry, just thought to check the vault logs and there is an error when trying to revoke the lease:
error=failed to revoke entry: resp:(*logical.Response)(nil) err:mssql: Invalid object name 'dbo.syslogins'.

[chrishoffman]

Thanks for the logs! All traces of the users should be removed so this is not intended behavior. It is odd that dbo.syslogins is referenced as it is not part of any queries we run. From what I gather from the documentation is it may be coming from sys.server_principals https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql.

What version of Sql Server are you using and can the user query the sys.server_principals table?

[chrishoffman]

Here is the sql we run to drop the users https://github.com/hashicorp/vault/blob/master/builtin/logical/mssql/secret_creds.go#L165-L182. We do a few other things will killing process and disabling logins but this is the bulk of the drop logic and likely where you are seeing a problem.

[awells3]

I ran the SQL queries to drop the login and user and those executed just fine and neatly cleaned up the left over logins and users in the SQL server.

However, when I ran EXEC sp_msloginmappings (https://github.com/hashicorp/vault/blob/master/builtin/logical/mssql/secret_creds.go#L105)

I got the error from the logs in my SQL messages:

Msg 208, Level 16, State 1, Procedure sp_MSloginmappings, Line 31
Invalid object name 'dbo.syslogins'.

I will note that I had my test database selected when I ran this and when I used the master database it was able to complete successfully.

[awells3]

I was able to successfully run the SQL query while connected to my test database by doing the following:

Use master
EXEC sp_MSloginmappings 'username'

[chrishoffman]

Thanks for you help here! I have some change pending that will address both of these issue for 0.7.1 release.