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. Here are some things you should be aware of before you decide to use a dedicated MsSQL Db for Pleasant Password.
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
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, 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 (https://localhost:10001) to go to your Password Server administrative web site.
Activate the license
See how to activate license here.
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. 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): 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.dbVersion 1-6
%ProgramData%Pleasant Solutions
Note: %ProgramData% is a hidden folderI 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.