Vault startup fails when using MSSQL as storage backend with pre-created database and schema

[kedarkale27]

Problem statement:
Vault startup fails when using MSSQL as backend storage with pre-created database and schema, here vault is configured to start with database user who does not have schema creation access.

Steps to reproduce:
Execute following statements on the MSSQL database:

USE master
CREATE DATABASE [oper_db]
USE [oper_db]
CREATE LOGIN vault WITH PASSWORD = 'password'
CREATE USER vault FOR LOGIN vault
CREATE SCHEMA vault AUTHORIZATION vault
CREATE ROLE VaultUser AUTHORIZATION [vault]
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER , EXECUTE
ON SCHEMA::vault TO VaultUser

CREATE TABLE [vault].[SECRETS] (
[PATH] VARCHAR(512) NOT NULL,
[VALUE] VARBINARY(MAX) NULL
)

Start vault with the following configuration.
listener "tcp" {
address = "127.0.0.1:8200"
tls_disable = "true"
}

storage "mssql" {
server = "#ServerIP"
username = "vault"
password = "password"
database = "oper_db"
table = "SECRETS"
appname = "vault"
schema = "vault"
}

Actual output seen on the console:
Error initializing storage of type mssql: failed to create mssql schema: mssql:
CREATE SCHEMA failed due to previous errors.

Expected output:
Vault should start successfully using the pre-created database and the schema.

[tyrannosaurus-becks]

Thanks for opening this issue, @kedarkale27! Looks like the error is originating from here.

Is it possible for you to simply not pre-create the schema and to let the "dbo" one be used?

[kedarkale27]

@tyrannosaurus-becks
We needed to create a separate user which will have access only to the vault schema and the tables inside it and not to the dbo schema.
As the password of the vault schema is getting exposed in the config file of Vault.
Hence we need a pre created schema different from dbo

We have also published a fix which solves this issue:
#6356

The code expects the sql commands to be executed in the same database session as of the "USE DATABASE" command,But for Go lang some how it is not working
We did a POC using these same sequence of commands in Java language and it worked correctly.

[tyrannosaurus-becks]

Thanks! Happy to review it after the submitter agreement is signed.

[kedarkale27]

@tyrannosaurus-becks I have already signed it yesterday.

[kedarkale27]

@tyrannosaurus-becks Did you get a chance to review the code?

[kedarkale27]

Hi @tyrannosaurus-becks the test case has been added to verify the updated code. Can you please check and approve ?

Thanks!

[sagitsofan]

Any new with this issue, i am experience exactly the same behavior as @kedarkale27 mention

[tyrannosaurus-becks]

Please follow the linked PR, #6356.