Changing databases for Pleasant Password Server

See why customers enjoy using Pleasant Password Server with a KeePass client

By default, Pleasant Password Server installs quickly with a securely encrypted SQLite database on the server machine. 

A simple, automated database migration tool is included. By using this powerful, built-in "Restore from Backup" functionality, it's possible to convert and migrate all the Password Server data to the new database location (see Items included in database).

Have Questions?  Contact Us

High-Level Overview

The main tasks involved in switching the database for Password Server is as follows:

• Create a new database instance
• Switch Database Connection to point to this new database instance
• Restore Data using the built-in "Restore from Backup," which will convert and populate your data into the new database.

See the detailed steps in the Configure Your Database section below.

Detailed steps:

• Configure Your Database  (section below)

Sections:

• Supported Databases
• Database Encryption
  
• Backup your Connection String
• Change the Service Account User
• Configure Your Database    (Steps for changing your database)
• Database Maintenance 
• Troubleshooting
• Previous Versions Notes

Related Links:

• Migrate Pleasant Password Server

Supported Databases

• PostgreSQL
• MS-SQL
• Azure DB

Considerations:

• Database Encryption - select an encryption method or resource
• Cloud Database Providers - can provide additional features such as database scaling, encryption, etc.
• Supported Databases - database size recommendations
• When to Upgrade the Database - performance considerations

Database Encryption

When changing to a new database, ensure that the database is secured and review these steps to Encrypt your Database.

Create a Backup

Ensure you have created a backup:

• copy of your Database
• your connection string

Configure Your Database

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

1. Before starting: make sure you've read sections above:

   • Create a Backup,

   • Encrypt your DB (above).
     

2. Stop the Password Server service.

3. Change the database:

   • Create a new blank database

   • (Optional) Encrypt your Database

   • Setup the user security for the database: MS-SQL Connection Security Settings

4. Open the Service Configuration Utility

   • (See how to open this utility from the Backup section above)

   • Click Database Configuration & Choose Database Provider:

     • for Azure SQL DB choose MSSQL

   • Enter the Connection String to connect Password Server to a fresh new database.

   • Click Save Changes and exit the Service Configuration Utility

5. Change the Service Account User for Pleasant Password Server:
   

   • 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).

6. Start the Password Server service. This will automatically create all the tables in the database and set up the default admin user.

7. Login with your browser to your server.

8. (optional) Restore all your data from backup:

   • Now you can migrate all your data (folders, entries, application settings, etc.) that you had in the previous database type.

   • Use the database backup and restore to: Restore from backup (Enterprise edition)

9. Double-check license activation.

10. Restart your service/site.

    • Note this may take time to restart, and you may receive a warning from the service about the time, which is safe to ignore.

11. See the Troubleshooting section below if necessary.

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

Database Maintenance & Optimization

Pleasant Password Server Database Tuning (MSSQL)

The following settings include important maintenance schedule settings to optimize the Microsoft SQL Server Database performance. This can be especially useful for more applications than just Password Server, and is more important for enterprises with many simultaneous users with rapidly growing data.

PostgreSQL database settings could also be changed in a similar manner, but these settings are provided for MS-SQL because it does not perform queries as well out-of-the-gate as PostgreSQL.

Schedule Database Backups

Database replication can be scheduled by the database natively. Although Password Server can provide manual and automatic backups to SQLite file, however, native MS-SQL database backups will be more efficient. Transactions log file backups can be set up at the same time.

• SQL Database Data, and Log File Backup: Microsoft - Schedule Database Backups

Change Database Settings

Update Statistics

IMPORTANT: Keeping the database statistics updated automatically will very likely greatly assist database query performance and optimization.

• Open the database properties
  • Enable: Auto Update Statistics Asynchronously  

Manage Disk Usage:

Optional: Open the Disk Usage Report to manage the current utilization.

• Set these parameters so that there will always be enough space. Otherwise the disk size will grow continuously over time.

Check for Missing Indexes

The database should be optimally configured for installation. However, we recommend after running the application for awhile, to check for missing indexes which could improve application performance, especially as the database grows in size.

After running Password Server for some time, there will be more usage information for the database to work with. Note that the data is reset after a restart of the database.

1. Run this script:

   • Missing Indexes Script

   • Reference:

     • SQL Scripts - How to find missing indexes

2. Calculate the performance times - for login, navigation, etc.

3. Create the indexes indicated by the script results

   • It may be beneficial to create them one a time
   • Note: that adding too many indexes may also slow the application down

4. Check the same performance times again for improvements/regression. If the performance is worse, then drop all the new indexes. Optionally re-add them one index at a time.

Setup a Maintenance Plan

Setup a maintenance plan to:

• Reorganize indexes
• Update Statistics

It's possible to view any existing fragmentation in the Indexes using this script:

• Fragmentation Script

• Reference:
  
  • SQL Scripts - How to resolve index fragmentation

Database Maintenance Scheduling

Open up the Database Maintenance Scheduling Wizard:

Maintenance Scheduling Wizard - Maintenance Task Selections

Select one or more maintenance tasks:

• "Reorganize indexes"

  • Scan type:
    
    • Detailed

  • Optimize indexes only if:

    • Fragmentation: 5%
    • Number of pages: 5

• "Update Statistics"

  • Refresh:
      All existing statistics

  • Scan type:
      Full scan

Acknowledgment:

• Thanks go to Florian Lammel from CONTIS GmbH, for their contribution of the Database Maintenance & Optimization steps.

Database Security Hardening

Additional security and maintenance steps can be further configured. Comprehensive and exhaustive details found by requesting the benchmarks found here:

• CIS Security Benchmarks

Some databases provided by third parties already include encryption, hardening, and additional security features.

Troubleshooting

If you run into any issues with your chosen database please check the items below:

• Ensure your Firewall or Anti-Virus are not interfering with network traffic

• Try Setup without encryption requirements / certificates to start, then add these later

• Increase logging details for the Password Server. Restart the service, and review the log outputs.

• Specific setup checks for MS-SQL:
  • Ensure the Authentication mode is properly set:
    • SQL authentication (userid/password), requires enabling Mixed Mode or SQL authentication (under the Instance properties security)
    • Change setting then restart the database
  • Start the services for SQL Server (using services.msc):
    • SQL Server
  • SQL Server requires that TCP/IP connectivity is turned on, and by default the port is 1433
    • Otherwise the ERROR: "The System cannot find the file specified." Problem creating a connection to the SQL Server Database.
    • We can check listening ports with this command:
      • netstat -ano | find ":1433"
    • Open SQL Server Configuration Manager (compmgmt.msc) > Click SQL Server Network Configuration:
      • Set TCP/IP = Enabled
      • Set TCP Port = 1433
      • Restart the website

  • Detailed MS-SQL troubleshooting: see Connectivity issues
    

• Problem 1:
  • Error: "The target principal name is incorrect" (found in EventViewer / Log files)
  • Resolution: 

    • Add "TrustServerCertificate=true" to your Connection String

• Problem 2:
  • 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

• Problem 3:
  • Empty database; no tables
  • Resolution:
    • Double-check the security permissions for the user
    • Double-check that the service account in format: Domain\UserName
      • Note: Pleasant Password Server does NOT currently support authentication with a UPN (User Principal Name) format, i.e. username@domain.

If you continue to experience problems, please don't hesitate to send us your detailed log files (at Contact Us), and let us know your situation.

Previous Versions Notes

 Version 3.5.0 or Older

• Database configuration for older versions of Pleasant Server