MSSQL storage backend does not work with Azure SQL Server

[mohag]

Describe the bug
When attempting to use an Azure MSSQL database, vault fails with the following error on startup:

Error initializing storage of type mssql: failed to create mssql table: mssql: Reference to database and/or server name in 'Vault.INFORMATION_SCHEMA.TABLES' is not supported in this version of SQL Server.

To Reproduce
Steps to reproduce the behavior:

1. Set up an Azure SQL server and a "Vault" database within it
2. Set up a mssql storage setting to point to it in /etc/vault/config.hcl
3. Run vault server -config /etc/vault/config.hcl
4. See resulting error above

Expected behavior
Vault starts up and uses the database

Environment:

• Vault Server Version (retrieve with vault status):
  Vault v1.0.2 ('37a1dc9c477c1c68c022d2084550f25bf20cac33')
• Vault CLI Version (retrieve with vault version):
  Vault v1.0.2 ('37a1dc9c477c1c68c022d2084550f25bf20cac33')
• Server Operating System/Architecture:
  Ubuntu 18.04 x86_64

Vault server configuration file(s):

storage "mssql" {
  server = "sql-server-name.database.windows.net"
  username = "vault"
  password = "password_here"
  database = "Vault"
}

listener "tcp" {
 address     = "127.0.0.1:8200"
 tls_disable = 1
}

ui = 1

[mohag]

vault/physical/mssql/mssql.go

Line 108 in df18871

if _, err := db.Exec("IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '" + database + "') CREATE DATABASE " + database); err != nil {

also fails with Error initializing storage of type mssql: failed to create mssql database: Login error: mssql: Cannot open user default database. Using master database instead. when a user with access to just the "Vault" database is created. (That is not what this issue is about, that was worked around by just adding the user to the master database)

The problematic line seems to be this one. It seems like using the object_id method should work on both normal SQL Server and the Azure version.

[hbwheat]

I found a solution to this bug. I added two pieces to vault/physical/mssql/mssql.go file:

https://github.com/hbwheat/vault/blob/9d2dd6ed996ef092ff1f5eeb0674e600445c0194/physical/mssql/mssql.go#L97-L101

https://github.com/hbwheat/vault/blob/9d2dd6ed996ef092ff1f5eeb0674e600445c0194/physical/mssql/mssql.go#L116-L119

My config.hcl file now looks like:

storage "mssql" {
    server = "AzureSQL"
    username = "user"
    password = "password"
    database = "vaultdb"
    table = "vault"
    dbCreated = "1"
}

I'd love to do a pull request if this is a viable solution to get the backend working with Azure SQL.

I didn't want to potentially break any SQL commands by changing them, but rather just add the database to the connectionString if it was already created for the Azure SQL VM in question.

Also, verified that there is a new table "dbo.vault" under tables in this database when starting vault.

[hbwheat]

To add, the much easier solution with connecting to an Azure SQL is to add the database connection string the end of the server parameter. This doesn't require changing the underlying Go code at all.

1. Create a db in Azure SQL
2. Append "server" with a database string

ie:

server = "azuresqlservername.database.cloudapi.net;databasae=vaultdb"

[kalafut]

@hbwheat Thanks for digging into this and finding a workaround! My preference would be to keep the configuration simple and update our code to handle both cases gracefully, if possible. I've not looked in the details of what that might be (maybe the object_id approach, or something else), and would welcome a PR. Do you have access to both a normal SQL server as well as Azure's version to try it out?

[hbwheat]

@kalafut
That’s certainly more preferable than a work around. I was able to replicate the error from queries in SQL Management Studio run on the master database.

Unfortunately I’m not much of a DB Admin, and I only have access to Azure AQL at the moment.

From what I can tell, it’s an inherit limitation in Azure SQL and the way it allows connections. The Go module connects to the master by default and Azure doesn’t like queries across databases if the database exists already.

So if the db doesn’t exist Vault will create a db with a bunch of default settings. If it does exist already it errors out with cross db query not allowed.