Possible MS SQL Errors

Error 1: Cannot connect to the localhost, connection timed out.

Solution:
Simply increase the timeout error in the MS Management Studio connection settings (increase from 30 t0 120 and remember to turn this back again to the default after the first connection). Fix the initial timeout connection and this can be changed afterward. Increased connection timeout to 120.

Error 2: Cannot connect “An exciting connection was forcefully closed by the remote host Sql Exception with error number 10054

Solution: Try to identify and understand your environment in order to resolve this issue.

  • This error clearly shows that the problem is not the firewall or the remote connections, but the login process.
This was not resolved because connection seems to work but the authentication method I tried to use is not supported by the database instances and therefore did not work. I was trying to connect using Domain Account and this was not supported.

Installing a Dedicated Db (MS SQL) for Pleasant Password.

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, 7

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

Steps:
– 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=YourServerName\yourInstanceName;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.
https://info.pleasantsolutions.com/Documentation/Pleasant_Password_Server/B._How_to_configure_Pleasant_Password_Server/4)_Changing_Databases_for_Pleasant_Password_Server/Change_the_Service_Account_User

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 Solutions\Password Server\PleasantPassServer.db

Version 1-6

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

Unable to connect to MSSQL Server after changing the Server name

Solution

Since I had this installed on my local machine, I could access it using the localhost.

Note: You can also use the IP Address to connect as well. This will save a lot of tweaking time.

1. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8ea7fbb2-9349-4b21-81b1-ad47cc49e8df/cant-login-sql-after-changing-computer-name?forum=sqldataaccess
2. https://serverfault.com/questions/386618/cannot-login-sql-server-after-changing-machine-name
3. https://dba.stackexchange.com/questions/62165/i-cant-connect-to-my-servers-sql-database-via-an-ip-address