MSSQL database engine config fails when using certain connection string syntax

[Matroxt]

Describe the bug

It doesn't seem possible to create a database connection when using AzureSQL syntax for connection_url with user/pass templating.
Using the other syntax works fine however.

This doesn't work ❌ :

vault write database/config/my-database \
     plugin_name="mssql-database-plugin" \   
     connection_url="server=localhost;port=1433;user id={{username}};password={{password}};database=master;" \    
     allowed_roles="myrole" \    
     username="sa" \    
     password="yourStrong(!)Password"

You'll get this error:
error creating database object: error verifying connection: Login error: mssql: Login failed for user 'sa'.

This works ✅ :

vault write database/config/my-database \
     plugin_name="mssql-database-plugin" \   
     connection_url="sqlserver://{{username}}:{{password}}@localhost:1433/instance?database=master" \    
     allowed_roles="myrole" \    
     username="sa" \    
     password="yourStrong(!)Password"

To Reproduce

Steps to reproduce the behavior:

1. Start a dev vault server vault server -dev
2. Login vault login
3. Start a local MSSQL container docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=My5trongP4ssword#" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest
4. Enable the DB engine vault secrets enable database
5. Try to create a config:

   vault write database/config/my-database \
        plugin_name="mssql-database-plugin" \
        connection_url="server=localhost;port=1433;user id={{username}};password={{password}};database=master;" \
        username="sa" \
        password="My5trongP4ssword#"

6. See error

   Error writing data to database/config/my-database: Error making API request.
   
   URL: PUT http://127.0.0.1:8200/v1/database/config/my-database
   Code: 400. Errors:
   
   * error creating database object: error verifying connection: Login error: mssql: Login failed for user 'sa'.
   

7. Try to create a config without the templating:

   vault write database/config/my-database \
        plugin_name="mssql-database-plugin" \
        connection_url="server=localhost;port=1433;user id=sa;password=My5trongP4ssword#;database=master;" 

8. See it succeed. Hence this syntax appears to break the templating.

Expected behavior

Success in both cases.
Both those syntax should behave the same when using templating:

• server=localhost;port=1433;user id={{username}};password={{password}};database=master;
• sqlserver://{{username}}:{{password}}@localhost:1433/instance?database=master

Environment:

• Vault Server Version: 1.8.4
• Vault CLI Version: 1.8.4

Additional context

First encountered this problem trying to setup a AzureSQL database, which recommends this syntax for the connection string. Tried locally and I was able to replicate.
The documentation shows it working without templating using this syntax, but doesn't say that we can't use it

[jasonodonnell]

Hi @Matroxt, I looked into this and it appears to be a bug related to escaping special characters. In particular it seems ADO style connection strings should not be escaped (vault is escaping special characters so they can be used in URLs, so your password is being rendered to my5trongP4ssword%23.

I was able to make to work by changing your password slightly:

$ docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=My5trongP4ssword" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

$ vault secrets enable database

$ vault write database/config/my-database \
     plugin_name="mssql-database-plugin" \
     connection_url='server=localhost;port=1433;user id={{username}};password={{password}};database=master;' \
     allowed_roles="myrole" \
     username="sa" \
     password='My5trongP4ssword'

We're looking into a fix on our end. Thanks for reporting this!

[robmonte]

Hi @Matroxt
I wanted to let you know that #13414 was merged about 2 weeks ago to address this and it will go out in Vault 1.10. Please let us know if the issue persists for you. Note that the docs aren't updated yet to reflect this new parameter.