How to See Which Certificate Was Used in an Existing Backup

Recently I have encountered a interesting scenario relate to SQL server backups.

In our environment there are few SQL servers are running. They are backed up and databases are also backed up. So everything was running smoothly. Until it's not. One of our servers has crashed.

Well, no one was worried, because we had backups and there were not much of data loss.

So after we rebuild the server (we built it from scratch rather than from backups, because we need to refresh the OS anyway), and after installing SQL server, we tried restoring databases.

Then only everyone realized that, backups were encrypted. I know it is our bad, we should have tested restoring periodically, but in small business like us, that never get happen.

So how do we restore the backups. We needed the DEK (Database Encryption Key) which those backups were encrypted.

Luckily we found, set of certificate backups which were use to encrypt database backups.

Every one was happy.

However, how do we know which certificate to use on this particular server. Name didn't really give us a clue.

So we had to Google/Chat with AI a bit.

That's when we came up following approach.

First you need to restore the backup with just header only.

RESTORE HEADERONLY FROM DISK = 'D:\Backups\MyEncryptedBackup.bak';

This will show, result set similar to below:

This result set have following columns (56 of them):

BackupName

BackupDescription

BackupType

ExpirationDate

Compressed

Position

DeviceType

UserName

ServerName

DatabaseName

DatabaseVersion

DatabaseCreationDate

BackupSize

FirstLSN

LastLSN

CheckpointLSN

DatabaseBackupLSN

BackupStartDate

BackupFinishDate

SortOrder

CodePage

UnicodeLocaleId

UnicodeComparisonStyle

CompatibilityLevel

SoftwareVendorId

SoftwareVersionMajor

SoftwareVersionMinor

SoftwareVersionBuild

MachineName

Flags

BindingID

RecoveryForkID

Collation

FamilyGUID

HasBulkLoggedData

IsSnapshot

IsReadOnly

IsSingleUser

HasBackupChecksums

IsDamaged

BeginsLogChain

HasIncompleteMetaData

IsForceOffline

IsCopyOnly

FirstRecoveryForkID

ForkPointLSN

RecoveryModel

DifferentialBaseLSN

DifferentialBaseGUID

BackupTypeDescription

BackupSetGUID

CompressedBackupSize

Containment

KeyAlgorithm

EncryptorThumbprint

EncryptorType

Last two columns, EncryptorThumbprint and EncryptorType will tell you which certificate has been used.

Something I didn't know before.

Connecting to PostgreSQL using pgAdmin

If you are using SSMS to connect SQL Server, you probably looking for similar tool for PostgreSQL. Well, you are in luck, for that we have pgAdmin GUI tool.

If you launching pgAdmin tool on the server where you installed PostgreSQL, it most probably launch and auto connect to the local server by default.

If it doesn't or connecting from another computer, you will have to register the server in pgAdmin tool. To do that, right click on the "Servers" node you see under object explorer window, and choose Register -> Server...

This will bring you a dialog box with connection settings. First tab you see is "General" tab.

You can give a name to the connection and specify a background color too.

Next tab is the  most important tab where you specify host address, port number and credentials.

In above screenshot I have shown how I connected from remote machine. I have entered the host IP address and port number, which is the default port number. Then I left others as defaults, such as database name (postgres) and default user name (postgres). You can choose to save your password or enter every time you connect.

Other tabs control advanced connection settings, such as SSL parameters etc. which I will not touch in this article.

So once you done click on the "Save" button and your server is registered and connected.

If you want to run a query on pgAdmin tool, you can use the query tool in there. To open it, expand the "Databases" node and select your database, right click on it and choose "Query tool".

This will open up new window/tab on the admin tool. Note that there are few tabs already opened by default.

Like in SSMS, you can run queries on here and see results in the bottom pane.

There are few other components in this admin tool, but I think I will cover them later, in a specific article to them.

Connecting to PostgreSQL using psql

In previous article we have looked into how we can install PostgreSQL on a windows machine.

Today I'm going to see how I can connect to PostgreSQL database.

There are two main ways to connect to a PostgreSQL, they are:

• Using psql -> command line based application similar to sqlcmd for SQL server
• Using pgAdmin tool -> GUI based application similar to SSMS in SQL server

Using psql

You can easily launch psql from start menu, just search for psql.

Once launch it will ask for server to connect, database to connect, port number to connect and user name and password.

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

Password for user postgres:

If you use default in installation, just accept the default values shown in the prompt. Otherwise you will have to type the value. In above I have accepted all defaults, except password.

If you successfully connected, you will see a warning message on windows, just ignore it.

You will be presented with "postgres=#" prompt.

You can type your queries in this prompt.

Connecting Remotely

If you want to connect to postgresql server which is not installed on the machine you are working on (in other words, remote PostgreSQL machine), you need to do some additional work.

Install psql

1. First of all you need to install psql on the machine you are going to access from.
2. Download the (zipped) binaries from the postgre site -> PostgreSQL Windows
3. Extract files to a directory (e.g. C:\pgsql)
4. Inside the directory you un-zipped, you will find several folders (e.g. doc, include, pgAdmin, StackBuilder, symbols), if you prefer you can remove these, but don't remove "bin" folder.
5. You can remove all .exe files except psql.exe from bin folder, but I suggest you keep all of these for future use.
6. For easy access, add the "bin" directory path (e.g. C:\pgsql\bin) to PATH variable.

Ensure remote access

• Make sure PostgreSQL server allows remote access. To do this you need to edit pg_dba.conf file on the server. File is located in data folder of the PostgreSQL installation (e.g. \Program Files\PostgreSQL\16\data)
• Add a line to allow source IP address. Line will be like below

host all all <<your IP>>/32 trust

E.g.: host all all 192.168.2.1/32 trust

• Open postgresql.conf file (which is same folder as above) in a text editor and make sure, server is listening to all IP addresses. There should be a line like below

listen_addresses = '*'

• Restart the PostgreSQL server
• Via Windows Services (GUI)
• There will be a service called "postgresql-x64-<<version>> - PostgreSQL Server <<version>>", which you need to restart using services GUI.

• Via command line
• Stop: net stop postgresql-x64-<version>
• Start: net start postgresql-x64-<version>

• Via pg_ctl
• <<path to postgresql server installation>>\bin\pg_ctl -D "<<path to postgresql server installation>>\\data" restart
• E.g. "C:\Program Files\PostgreSQL\16\bin\pg_ctl" -D "C:\\Program Files\\PostgreSQL\\16\\data" restart

• Connecting
• Run following command on command line of the source computer

psql -h <<ip address>> -p <<port>> -U postgres postgres

psql -h 192.168.2.52 -p 5432 -U postgres postgres

Running Queries

Once you connected, you get postgres=# prompt, where you can type your queries.

In next blog, lets find out how we can use pgAdmin tool to connect to PostgreSQL server.

Installing PostgreSQL

As per my previous blog on PostgreSQL, I'm going to start a journey to study this new area of interest. As the first step I'm going to install it on my local development/testing environment. I'm gong to follow articles from PostgreSQL Tutorial site and PostgreSQL course on W3 Schools site.

I have downloaded installation package for Window from Enterprise DB site -> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads. As of this article date, I'm using version 16.3 and it is about 375 MB.

I have followed the easy installation wizard.

Select the installation path

Select components. I have selected them all as I'm studying.

• PostgreSQL server -> the core database engine which you must install
• pgAdmin4 -> GUI to manage PostgreSQL server (like SSMS)
• Stack Builder -> Drivers and stuff for development (I guess)
• Command Line Tools

Select you data directory -> I choose default which is the "Data" sub folder in installation directory

Choose password for super user (administration/sa password)

Choose the port number for the server. I left it default, which is 5432.

Choose locale.

Check the installation summary and make sure all settings selected are looks ok.

Install.

Took about 5 minutes in my not so high end test machine and at the end it has asked me whether I want to launch the stack builder to install additional drivers and all. I choose yes.

At this point main installation is done and you can check the installation by launchign pgAdmin GUI from start menu.

Optionally you can continue with stack builder setup.

Stack Builder Setup

Once main PostgreSQL finishes Stack Builder setup starts with following screen:

You need to choose your PostgreSQL server from the drop down list (in case there are more than one installation).

Then you need to select categories for components to install.

•   Add-ons, tools and utilities
• EDB Language Pack v4.3-1 (programming language pack including python)
• pgAgent (64 bit) for PostgreSQL 16 v4.2.2-1 (Job agent)
• pgBouncer v1.22.1-1 (connection pooler)

•   Database Drivers
• Npgsql v3.2.6-3 (Microsoft .NET data provider)
• pgJDBC v42.7.2-1 (JDBC driver)
• psqlODBC (32 bit) v13.02.0000-1 (ODBC driver 32bit)
• psqlODBC (64 bit) v13.02.0000-1 (ODBC driver 64bit)

•   Database Server (additional version of Postgre SQL server, which I will not install)
• PostgreSQL (64 bit) v12.19-2
• PostgreSQL (64 bit) v13.15-2
• PostgreSQL (64 bit) v14.12-2
• PostgreSQL (64 bit) v15.7-2
• PostgreSQL (64 bit) v16.3-2 (installed)

• Registration-required and trial products
• • EnterpriseDB Tools -> No idea what these are, hopefully we learn about them in future)
  • Migration Toolkit v57.0-1
  • PEM SQL Profiler Plugin for PostgreSQL 16 (64 bit) v41.2
  • Postgres Enterprise Manager Agent v8.7.1-2
  • Postgres Enterprise Manager Agent v8.7.0-1
  • Postgres Enterprise Manager Server v8.7.0-1
  • Postgres Enterprise Manager Server v9.6.0-2
  • Replication Server v6.2.19 - 1
  • SQL/Protect for PostgreSQL (64bit) v16.0 - 2
  
  
• Spatial Extensions
• PostGIS 3.4 Bundle for PostgreSQL 16

•   Web Development
• PEM-HTTPD v2.4.59-1

Once you select your components, start the installation, which will download components.

This will download various components about 2Gb (depends on what you selected from above options).

Once all components are downloaded, you have options to setup them.

Please note that if you start this installation it will install all the components you choose one by one (each component have separate installation wizard). So if you choose 5 you will have to complete all 5 installation wizards.

Rebuilding system databases in SQL Server

SQL Server has few system databases, which help it to manage the system. They are:

• master
• msdb
• model
• tempdb
• resource

tempdb is rebuilt every time your restart your SQL server, so there is not issues there.

In case of a corruption, how do you re-create all those system databases? One approach is restoring backup for those system databases. But if you don't backup system databases or backups also contains the corruption, you have no other option, but recreate them.

However, note that if you re-create them, you will lost any custom database object you created in those databases (i.e. it is like doing a factory reset) hence you will lost things like customized settings and agent jobs.

Prep

If you want current settings to be applied after restore/rebuild, you need to do following pre-requisite tasks:

        1. Note down current server configuration. You can fetch current server configuration by running following SQL (you probably want to extract this to a excel sheet or to a user database):

            SELECT * FROM sys.configurations;

        2.   If you hoping to restore to latest hotfixes, note down current hotfixes and current collation:

            SELECT

                    SERVERPROPERTY('ProductVersion ') AS ProductVersion,

                    SERVERPROPERTY('ProductLevel') AS ProductLevel,

                    SERVERPROPERTY('ResourceVersion') AS ResourceVersion,

                    SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

                    SERVERPROPERTY('Collation') AS Collation;

        3. Following query shows current database file locations. Note this down if you are not using the default locations.

        SELECT name, physical_name AS current_file_location

        FROM sys.master_files

        WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

Rebuild

In order to rebuild, you need SQL Server installation media. There fore please mount or insert the installation media into the server you are rebuilding.

Then run following command on command line (make sure you are on the correct folder):

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]

There are many other parameters you can specify. For more detail please refer to Microsoft documentation - https://learn.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-ver16

Once rebuild completed, you can put your old settings back if necessary.