When asked to run projects that utilize MSSQL as the database engine with the least cost, then you should be able to select from the various Editions of MSSQL that serves your need. This decision is based on your environment and what the project (task entails). See the following explanations below for the right Edition to use.

Five editions of MSSQL

• Express: This is the most basic of all SQL Server editions. It’s free to use in production, which makes it the best choice for independent software vendors, whose clients can’t afford the cost of a SQL Server license.
• Developer: This edition has all the features of the Enterprise edition, but cannot be used in production environments.
• Web: This edition is between the Standard and Express editions. It has features that Web hosting companies and Web VAPs can offer their customers to provide scalability, affordability, and manageability capabilities for small to large scale web properties.
• Enterprise: This is the most complete edition of all. With this edition, you have unlimited virtualization and high service levels for the mission-critical workload.
• Standard: This edition enables database management with minimal IT resources.

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.

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=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. http://bit.ly/2sQ3A8f

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

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

After altering (increasing) the ‘memory max target’ and ‘memory target’ of an Oracle database, you will have to run these statements as sysdba to restart the database. By not doing so, you will not have the necessary permission to perform some necessary tasks.

Sysdba grants the necessary privilege to administer the database.

Note: This requires a restart and do not necessarily have to restart the whole Db if it is in production, simply follow these commands before after modifying the ‘memory_max_target’ and ‘memory_target’ value of the Oracle Db. Simply run in sqlplus

sysys /as sysdba

Next

shutdown immediate

Next, enter the command as fast as possible

startup

This will ensure the changes are applied to the database.

Test to ensure the parameters are okay and changes propagated correctly.

show parameter memory

Navigate to this path /etc/init.d/mysqld
- stop mysqld_safe --skip-grant-tables &
mysql -uroot -p ( hit enter, without any password)

- use mysql
- UPDATE mysql.user SET password=PASSWORD('your_new_password') WHERE user='root';
- FLUSH PRIVILEGES;
- quit

Next run

/etc/init.d/mysqld restart

http://www.linuxtweaker.com/linux/how-to-export-and-import-mysql-database-via-command-line-in-linux/
https://www.howtoforge.com/faq/how-to-use-the-command-line-to-make-an-sql-dump-of-a-mysql-database

Note: plxxx.ixxx is a given name for the directory

Step1: Creating A Backup
Instead of entering passwords always, change into the web directory

christian@wexx0:~$ sudo -i
[sudo] password for christian:
wexx0#
wexx0# cd /var/www/plxxx.ixxx
wexx0# ls

Note: This is for contao setup in other to get the username and password as well as the database
name for creating the mysql dump and this is different for vtiger files
Contao can be found in /var/www/plxxx.ixxx/web/system/config/
Cat localconfig.php

Ignore for Contoa = (for vtiger it can be found /var/www/vtiger.plxxx.ixxx.info/web/
wexx0#Cat config.db.php

https://wiki.vtiger.com/index.php/Migrating_vtiger_source_and_database_from_one_machine_to_another_one_on_Windows
http://www.linuxtweaker.com/linux/how-to-export-and-import-mysql-database-via-command-line-in-linux/
http://www.liquidweb.com/kb/how-to-back-up-mysql-databases-from-the-command-line/

Step 2: How to export MySQL database via command line in Linux

The mysql dump command is used to create text file dumps of databases managed by MySQL.
These dumps are just files with
all the SQL commands needed to recreate the database from scratch. The process is quick and easy.

wexx0# mysqldump -u c1xx_cxxtao -p cxxp_cxxtao > /tmp/mysqlnewdump.sql
Enter password:
wxx10# pwd
/var/www/plxxx.ixxx/web/system/config
wxx10# ls

wxx10# scp /tmp/mysqlnewdump.sql root@8x.xxx.xx.1x:/var/www/pplxxx.ixxx/
root@8x.xxx.xx.1x’s password:
mysqlnewdump.sql 100% 2903KB 2.8MB/s 00:00
wxx10#

Sometimes you may need to import and export your MySQL databse from command line. Then this maybe could help you out.

#mysqldump -u database_user_name -p database-name > dbname.sql
Behind -u you should put your database user name
-p this is for the database password option
When you have done this command it will ask for the database password,
so you put the password in and press enter.

Example:
#mysqldump -u root -p my_db > /home/kudda/my_db_backup.sql
The example above will dump my_db into /home/kudda
and it will land under the name of my_db_backup.sql. Also i am using mysql root to easily dump database,
so when i written -u after that you shall write root.

Step 3: To Access the Dump from another Server where the mysqldump file is copied into

Here i am logging in to an LXC container
root@alxxxa:~# pct enter 167
root@Pxxxro:~#
root@Pxxxro:~# /var/www/pplxxx.ixxx/
bash: /var/www/ppplxxx.ixxx/: No such file or directory
root@Plxxxro:~# ls
root@Plxxxro:~# /var/www/pplxxx.ixxx/
bash: /var/www/pplxxx.ixxx/: No such file or directory
root@Plxxxro:~# cd /var
root@Plxxro:/var# ls
backups cache lib local lock log mail opt run spool tmp www
root@Plxxro:/var# cd www
root@Plxxro:/var/www# ls
pplxxx.ixxx html
root@Plxxro:/var/www# mv Planpro.info/ planpro.info/
root@Plxxro:/var/www# ls
html pplxx.ixxx
root@Plxro:/var/www# ls
html pplxxx.ixxx
root@Plxxro:/var/www# cd pplxxx.ixxx/
root@Plxxro:/var/www/pplxxx.ixxx# ls

root@Plxxro:/var/www/pplxxx.ixxx#

mysqlnewdump.sql

Step 4: Restoring a Backup (Import A MySQL Database)

http://www.linuxtweaker.com/linux/how-to-export-and-import-mysql-database-via-command-line-in-linux/

root@Plxxro:~#
root@Plxxro:~# mysql -u root -p -h localhost cxxp_cxxtao < /var/www/pplxxx.ixxx/mysqlnewdump.sql
or
#mysql -u database_user_name -p my_db < /home/kudda/my_db_backup.sql

More info:
Here it should be almost the same as the export.
#mysql -u database_user_name -p my_db < /home/kudda/my_db_backup.sql
Here it is the same as export. after u- you put your database user name
and the -p is used for the password option still.

Here is one example of it.
Example: mysql -u root -p my_db < /home/kudda/my_db_backup.sql
On the example above i am importing my_db_backup.sql script into the database my_db