4) Changing databases for Pleasant Password Server

By default, Pleasant Password Server installs an encrypted SQLite database on the designated computer. It is possible to replace this database with a different database type for your server.

Supported Databases

  • Microsoft SQL Server 2008, 2012, 2014, 2016
  • Microsoft Azure SQL DB *
  • PostgreSQL
  • SQLite

 

Pleasant Password Server may work on other versions of Microsoft SQL Server (such as 2005). These versions are not regularly tested, so there is a chance some aspects may not work as expected.

* Pleasant Password Server works with Azure, but has not been extensively tested. Use at your own risk. Note that there are certain features that differ between MS SQL and Azure SQL.

Encrypting your Database

Password Server only manages encryption for the built-in SQLite database. Be sure to configure encryption for your alternate database (i.e. PostgreSQL, SQL Server, Azure).

MS SQL (2008-2014, or 2016 Enterprise Edition only)
  • Note: in version 2016, TDE is only supported in Enterprise edition

- General reference links:

https://msdn.microsoft.com/en-us/library/bb934049.aspx

https://msdn.microsoft.com/en-us/library/bb510663.aspx

 

Setup Steps for TDE (Transparent Data Encryption)

1) Run SQL commands on your database:

  • 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
  • Run the following:

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 

2) Backup certificate and private key

Backup your Connection String

Before making any modifications, backup your connection string: so that you can recover access to your database if something goes wrong.

  • Open the Service Configuration Utility (installed with the server).
    • Start menu -> Programs -> Pleasant Password Server -> Service Configuration

password_server_config.png

  • Store the entire contents of the Connection String field in a secure backup location. To ensure you have copied everything, expand the width of this window to see all of the text before copying.

 

WARNING: Without the encryption key you will not be able to access your database. This key is is not recoverable if lost or deleted.

This key is only accessible via the Service Configuration Utility. The default SQLite database is protected using the very secure AES-256 encryption algorithm. The encryption key is stored in the connection string, which itself is stored in a protected and encrypted registry key value. Please take great care when using this utility. Note: This utility only instructs Pleasant Password Manager on how to connect to the database, but the utility itself does not make the changes to the database.

If the encryption key for your SQLite database is lost or changed, there will be no way to recover your data.

Configuring an Alternative Database or Moving a Database

To change or move the database used by Password Server, follow these steps:

  1. Before starting, make sure you've read the notes on encryption & backing up above!
  2. Stop the Password Server service.
  3. Open the Service Configuration Utility (see above: Backup)
    • Click Database Configuration & Choose Database Provider:
      • for Azure SQL DB choose MS SQL
  4. Enter the Connection String for connecting Password Server to a fresh new database
    • Find a Connection String (see instructions below), or refer to: http://www.connectionstrings.com.

    • Choose method: Windows Authentication (recommended) OR SQL Authentication (includes userid/password in the connection string).

      • Note that the connection string is encrypted in storage and access to it can also be further restricted on the Security Configuration exe properties.
      • If chosing Windows Authentication, then also change the Service account user in the steps following.
    • If using the Default Placeholder Certificate, you will likely need to add this to your Connection String: TrustServerCertificate=True;

    • MS-SQL (Windows Authentication):

      • Server=YourServerName\yourInstanceName;Encrypt=trueOrFalse;Database=YourDatabaseName;Integrated Security=SSPI;
    •  MS-SQL (SQL Authentication):

      • Server=YourServerName\yourInstanceName;Encrypt=trueOrFalse;Database=YourDatabaseName;User Id=myUsername;Password=myPassword;
    • PostgreSQL (Windows Authentication):

      • Server=YourServerName;Port=5432;Database=myDataBase;Integrated Security=true;
    • Azure SQL DB:

      • Server=tcp:YourServerName.database.windows.net,1433;Database=YourDatabaseName; User ID=YourUserName@YourServerName; Password=YourPassword;
      • Note: You can also copy your connection string from your Azure Manager.
  5. Click Save Changes and exit the Service Configuration Utility.

  6. Change the Pleasant Password Service's user account:

    • Notes: This particular setting will not persist in an upgrade! and will need to be updated. Please keep this step in your upgrade plans.
    • (For SQL Authentication connection strings: you can skip this step and go to step 7)
    • Run Services.msc -> Open the Properties of the Pleasant Password Service -> Change user in Log On tab
      • Ensure your user has group member access (eg. Administrators, Domain Admins, etc.)
      • Ensure your user has access to your Password Server Backup folder. This can be configured to a location on your network.
      • For additional details: https://technet.microsoft.com/en-us/...(v=ws.11).aspx
    • For Windows Authentication connection strings:
      • Setup the user with login access & permissions on your Alternate Database
      • For MS-SQL:
        • Login to Microsoft SQL Server Management Studio
        • Open your SQL Server database folder (left-hand window pane) -> Click Security Folder
          • Right-click and select New Login -> Choose the Login Name of your service user account
        • Select the "User Mapping" page
          • Select your Database
          • Set: db_owner (database role membership)
        • Configure SQL Server to allow Windows Authentication mode / mixed authentication
          1. Right-click on SQL Server instance at root of Object Explorer, click on Properties
          2. Select Security from the left pane.
          3. Select the SQL Server and Windows Authentication mode radio button, and click OK.
        • More Information (MS-SQL):
  7. Start the Password Server service. This will automatically create all the tables in the database and set up the default admin user.

  8. Login with your browser to your server.

  9. (optional) To restore all your credentials and settings from a previous backup:

  10. (see Troubleshooting section below if necessary)

 

These settings will persist through future updates of Pleasant Password Server (except step 6).

Troubleshooting

If you run into any issues with your chosen database:

  • Ensure your Firewall or Anti-Virus are not interfering with network traffic
  • Temporarily increase logging details for the Password Server
  • MS-SQL specific:
    • SQL Server requires that TCP/IP connectivity is turned on (under SQL Server Network Configuration - Note: restart the server)
    • SQL authentication (userid/password), requires enabling Mixed Mode or SQL authentication (under the Instance properties security)
    • Services are running: SQL Server and SQL Server Browswer (run services.msc)
    • See: Connectivity issues

 

Specific Errors:

  • Problem:
    • Error: "The target principal name is incorrect" (found in EventViewer / Log files)
  • Resolution: 
    • Add "TrustServerCertificate=true" to your Connection String

 

  • Problem:
    • Service fails to start, after an upgrade
    • Error in Weblogs file:
      • Cannot open database "CustomerDatabase" requested by the login. The login failed.
      • Login failed for user 'DOMAIN\USERNAME'.
  • Resolution:
    • The database was changed to MS-SQL, and the Service User Account was switched.
    • On Upgrade, this information is lost, and needs to be re-applied.
    • Close the upgrade’s “Starting the service” window
    • Open Services.msc, make the change, and re-start

If you continue to experience problems, please don't hesitate to contact us, and include your detailed log files.

Previous Versions Notes

Version 3.5.0 or Older

Tag page
You must login to post a comment.