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. The Live Demo can be found here: https://pleasantsolutions.com/passwordserver/live-demo/#

Decide on the server requirements.
– Windows Server 2019 (including Server Core), 2016, 2012, 2008
– Windows 10, 8,

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
Part A: Steps to provision a new MS SQL server

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. See the steps below
– Prepare or create a new MS SQL instance,
– Also, install the Management Studio to administer the database

Note: This step requires you backing 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: 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 these 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! and will need to be updated. Please keep this step in your upgrade plans.
– This step can be skipped if 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 (https://localhost:10001) to go to your Password Server administrative web site.

Activate the license
This step has been discussed previously for offline and online setup
https://techdirectarchive.com/2019/08/13/pleasant-password-server/

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 following below before proceeding to the steps below.

• First, copy the connection string to a safe location. This connection string can be accessed through the new Service Configuration Utility. This key is required to access the encrypted database.
• Also, pull a copy of the SQLite database file. The SQLite database location can be found here and also the version of the Pleasant Password Manager
  

To view the Version page in the web client (default location): https://localhost:10001/Version

- 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 helpful. If you have any questions, please let me know in the comment session.