How can we help?

Configure MS SQL as vSEC:CMS Database

Anthony - Versasec Support
Anthony - Versasec Support
  • Updated

Introduction

Out of the box vSEC:CMS uses an internal database which stores information about credentials registered and managed by the vSEC:CMS along with configuration settings. This database is an SQLite implementation.

However, if it is required to use a third-party database, it is possible to configure the vSEC:CMS to connect to an MS SQL database which can be used to store data for the application.

Configure Support

Prerequisites

A fully functional MS SQL server with a database needs to be available to the vSEC:CMS. The vSEC:CMS will not create the database.

Note
As recommended by MS, the MS SQL database should be encrypted. Please consult your MS SQL database documentation for further information on this.

The following versions of MS SQL Server are supported 2017, 2019, 2022, Azure SQL.

At minimum, Microsoft ODBC Driver 17 for SQL Server should be available/installed on the server where vSEC:CMS is installed.

It is possible to check what ODBC drivers (if any) are installed on your server. Open the ODBC Data Source Administrator console and from the Drivers tab you can see the drivers installed.

Sizing

For general information purposes the database sizing can vary depending on how you use vSEC:CMS. In this section we will provide very high level guidance on what the SQL database sizing could be to give you a high level idea of what to consider when designing your database from a sizing perspective.

For a typical system of 1 thousand managed credentials you could expect 100 MB to be allocated for your database.

Setup and Migration

From Options - Connections click the Add button and select SQL Database.

Enter a template name and from the drop-down list select vSEC:CMS SQL Database (v4) (MS-SQL).

Select the provider from the Database provider drop-down list and manually enter the database name or click Browse if it is possible to browse and find the database from your environment.

If a dedicated SQL user account is to be used for the connection credential enable Use SQL server authentication mode checkbox. Enter the name and password into the available fields. Otherwise the Windows service account that vSEC:CMS Service is running under will be used to connect to the MS SQL database.

Note
If a dedicated Windows account is to be used for the connection credential then it will be required to follow the instructions as described in the article Configure Dedicated Windows Service Account.

Manually enter the database name in the Name field or click the Get Databases button if it is possible to find the database in your environment.

Untitled.png

If all settings are configured correctly then click the Test button to test the connection end-to-end. You should see a success dialog as below if the connection is successful.

Click the Save button which will trigger the migration flow to MS SQL. You will see a dialog with a summary of what will be migrated and an estimate of the time it will take to migrate in full. Click Start to begin the migration.

Important
vSEC:CMS needs to have read/write and db_owner permissions on the MS SQL database. db_owner permissions are only required during the migration phase and if the SQL schema needs to be updated. Once the migration is done you can remove db_owner permission and if a schema update is required you can temporarily enable db_owner permission to perform the schema update and disable once this is done.
Below is an example of what permissions the SQL user would need.

Untitled.png

Limitations

The current implementation of the MS SQL database support has some limitations which are important to note. These are:

  • If, on startup of the vSEC:CMS, the MS SQL database is unreachable the application will not start;
  • It will only be possible to add one SQL connection of this type on any one installation of the vSEC:CMS;
  • Once the MS SQL connection is set up and in use it will not be possible to roll-back to the internal database.