Setting Up a Dedicated MsSQL Db for Pleasant Password Server

In this article, we will discuss How to use a dedicated MsSQL Db for Pleasant Password. Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications. Pleasant Password Server can be virtualized and will require sufficient resources (CPU and Memory) to run. Please see How to import Users and Groups into Pleasant Password Manager, and How To Use Pleasant Password Private Folder.

Here is a link to the Live Demo. Here are some things you should be aware of before you decide to use a dedicated MsSQL Db for Pleasant Password.

Server requirements

Windows Server 2019 (including Server Core), 2016, 2012, 2008, and
Windows 10, 8.

Also, see How to use the Pleasant Password Server tool, Migrate Veeam MSSQL Database to a new MSSQL Server, and Object First OOTBI Appliance Quick Setup

Supported Databases

For user requirements on the right server type to install to support your deployment

SQLite
MS SQL
PostgreSQL 9.0 or higher
Microsoft Azure SQL DB
Microsoft .NET Framework 4.5+ is required (installed for you)
Supported versions of IIS or IIS Express:

Server Hardware Requirements

For user requirements on the right server type to install to support your deployment. For Medium installation

Quad-Core 2 GHz or higher
8 GB RAM

Create a database

MS SQL Installation: See this link below for the needed steps to have MS SQL installed and running.

Part A: Steps to provision a new MS SQL server.

Prepare or create a new MS SQL instance. Also, install the Management Studio to administer the database

Note: This step requires you to back up your Pleasant Password Manager and Encrypting the new database before proceeding to switch to another database.

Now, we will have to encrypt the database using the information below.
Since I am using MS SQL: The database will be encrypted using “TDE Encryption as it is fully supported by Pleasant Password Server”.
Note: Ensure you have the SQL user create already with the necessary role if you are not using Windows Authentication in order to be able to connect to the database.
Assign (select the user mapping and set it to db_owner (database role membership)
Therefore: By default the Pleasant Password Server built-in SQLite database is encrypted. For an alternate database, be sure to follow the steps or links below to configure the encryption for your alternate database (i.e. PostgreSQL, SQL Server, Azure)

Part B: Switch Databases

After the installation of MS SQL Server installation, you can decide to switch to another database

Do not follow these steps if you would like to use the default SQLite database (ignore this step below. By default, Pleasant Password Server includes a Securely Encrypted SQLite database on the designated computer). But if you would like to use a different Db order than the default Db, please follow the steps below.

Note: It’s possible to replace this database with a different database type. To use the newly created MS SQL database, first, you will have to encrypt the database. Therefore follow the steps below.

Steps for MS SQL encryption

This is an easy and efficient way of encrypting the database. Run the following SQL commands on your database.

Note: First replace your values: database, password, Certificate name & subject, and encryption algorithm

Possible encryption algorithms include: AES_256, AES_192, AES_128, or TRIPLE_DES_3KEY

			
USE master;  
GO    
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'; 
go  
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';  
go  
USE MyDatabase;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
GO  
ALTER DATABASE MyDatabase
SET ENCRYPTION ON;  
GO 

		

Note: To revert the encryption, please see Pleasant Password Documentation.

Enter the new Connection String. This depends on the database you have decided to use. See this link above to see how the connection string is configured.

Click Save Changes and exit the Service Configuration Utility.

For MS SQL

Use this connection string type (If you don’t use this method, you will have connection issues9.

			
Server=YourServerNameyourInstanceName;Database=YourDatabaseName;Encrypt=TrueOrFalse;Integrated Security=SSPI;TrustServerCertificate=TrueOrFalse;

Change the Pleasant Password Server Service Account User

Note: This particular setting will not persist in an upgrade, so you have to upgrade independently. Please keep this step in your upgrade plans.

Skip this step if you are using SQL Authentication (connection string has username/password). Therefore, I am skipping this part as I have an MS SQL user account created in AD.

Start the Password Server service

Note: When using the default IIS Express, follow this step and connected to the newly created database with the connection string.

This will automatically create all the tables in the database and set up the default admin user.
Navigate to the startup website, which will launch once the installation is complete.
Click the default server link to go to your Password Server administrative web site.

Activate the license

See how to activate the license.

Backing up Pleasant Password Server

Note: This step applies only to the SQLite database. For other databases, follow their official link (e.g., for MS SQL and Postgres SQL). Note the below before proceeding to the steps below.

First, copy the connection string to a safe location. You can access this connection string through the new Service Configuration Utility. You will need this key to access the encrypted database.
Also, pull a copy of the SQLite database file. You can find the SQLite database location and the version of the Pleasant Password Manager here.

To view the Version page in the web client (default location):

			
- Stop the Password Server service (copying a "live" database can cause corruption).
- Run the Service Configuration utility. (On the Windows start menu, this program can be found)
- Click Database Configuration.
- Copy the entire contents of the Connection String field to a text file (named to reflect your current version of Password Server) in a safe location. To ensure you have copied everything, expand the width of this window to see all of the text before copying.
- Copy your SQLite database file to a safe location, naming the backup to reflect your current version of Password Server (to make sure you restore using the same version).
- Close the Service Configuration utility.
- Start the Password Server service.

		

Note: How to locate your SQLite database. The default database location is:

Version 7+

%ProgramData%Pleasant SolutionsPassword ServerPleasantPassServer.db

Version 1-6

 %ProgramData%Pleasant Solutions
 Note: %ProgramData% is a hidden folder

I hope you found this blog post on how to use a dedicated MsSQL Db for Pleasant Password helpful. If you have any questions, please let me know in the comment session.