SQL Server Mirroring using Certificate Authentication, NO Domain User

When we use SQL Server Locally/Home PC, it is not possible to get Domain User to practics Mirroring.

Now I will describe how we can Mirror SQL Server Database, locally/Without Domain User, to another instance.

I assume the Secondary Database is in Virtual Metchin (VM/VDI). [It can be the same PC BUT another instance]

NOTE: Create Inbound & Outbound Port Rule in Windows Firewall. in this example, I am using 5022 port for Mirroring only.

[[  Prepare Secondary Database for Mirroring  ]]

Now, I will follow below 5 steps to complete mirroring. for this, I assume below things...

Principal Database Name: TEST_DB
Principal Server: SQLPRIN
Secondary Database Name: TEST_DB [Assuming the Secondary Database is in Restore mode to start Mirroring]
Secondary Server: SQLSEC
Mirror port for both: 5022

NOTE: If you use same PC/Server with a different instance, please use the different port for Principal and Secondary server; like 5022 for Principal and 5023 for Secondary. Make sure the firewall port rule for both ports.

NOTE: All the steps have been divided into two parts. SETP 1 & 2 for Outbout Authentication and STEP 3 & 4 for Inbound Authentication of SQL Server.

ALL OF THE BELOW STEPS SHOULD BE DONE BY A USER WHICH HAS SYSADMIN ROLE.

STEP 1: For authentication, as there is no Domain User, we will use SQL Server Certificate.

We have to create Certificate using Master Key for the both, Principal & Secondary.

First, create a Master Key then create the Certificate

NOTE: If the SQL Server instance already has a Certificate [have been created for another purpose, like Encreption], we may use that Certificate instead of creating a new one.

We can check all the existing certificates using below SQL:

SELECT * FROM sys.certificates;

Principal Server Certificate Creation:

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE CERTIFICATE  SQLPRIN_CER
   WITH SUBJECT = 'SQLPRIN certificate for database mirroring',
   EXPIRY_DATE = '01/01/2020';

Secondary Server Certificate Creation:

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE CERTIFICATE  SQLSEC_CER
   WITH SUBJECT = 'SQLSEC certificate for database mirroring',
   EXPIRY_DATE = '01/01/2020';

STEP 2: Create Endpoint using above certificate to communicate with each other.

NOTE: If you already have an Endpoint for Mirroring with Windows Authentication (NEGOTIATE) and don't want to drop that, then please check the post to create Endpoint which supports both, Windows and Certificate Authentication. "Endpoint for Mirroring with Windows & Certificate Authentication in SQL Server"

-- To check available Endpoints use below SQL
SELECT * FROM sys.database_mirroring_endpoints;

-- To Drop an Endpoint
DROP ENDPOINT Endpoint_Name

Principal Server Endpoint Creation:

USE master;

CREATE ENDPOINT Mirroring_P
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE   SQLPRIN_CER
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL

   );

Secondary Server Endpoint Creation:

USE master;

CREATE ENDPOINT Mirroring_S
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE   SQLSEC_CER
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL

   );

NOTE: Choose REQUIRED ALGORITHM for ENCRYPTION as per your requirements. SQL Server supports both,  AES & RC4. For more details please check Create Endpoint.

STEP 3: Backup Certificate as a file for the Partner Database (Principal to/from Secondary). Then Copy the .cer file to the partner pc for later use.

-- Check existing Certificates
select * from sys.certificates

Principal Server Certificate Backup:

USE master;

BACKUP CERTIFICATE  SQLPRIN_CER  TO FILE = 'C:\SQL_Certificate\SQLPRIN_CER.cer';

NOTE:  Copy the file, SQLPRIN_CER.cer, to the Secondary server.

Secondary Server Certificate Backup:

USE master;

BACKUP CERTIFICATE  SQLSEC_CERTO FILE = 'C:\SQL_Certificate\SQLPRIN_CER.cer';

NOTE:  Copy the file, SQLSEC_CER.cer, to the Principal server.

STEP 4: Now will perform below tasks to use above Certificate Files.

1. Create Login for Partner
2. Create User for above Login (1)
3. Create Certificate for above User (2) using Coppid Partner's Certificate File
4. Grand Connect to the Endpoint for above Login (1)

Principal Server Tasks: 

USE master;

-- Create Login for Partner  (Secondary)

CREATE LOGIN SQLSEC_LOGIN  WITH PASSWORD = 'password';

-- Create User for above Login (1)

CREATE USER  SQLSEC_USER  FOR LOGIN  SQLSEC_LOGIN ; 

-- Create Certificate for above User (2) using Coppid Partner's (Secondary) Certificate File

CREATE CERTIFICATE   SQLSEC_CER_FOR_PRIN

   AUTHORIZATION  SQLSEC_USER  

   FROM FILE = 'C:\SQL_Certificate\SQLPRIN_CER .cer'

-- Grand Connect to the Endpoint for above Login (1)

GRANT CONNECT ON  ENDPOINT::Mirroring_P  TO  SQLSEC_LOGIN ; 

Secondary Server Tasks: 

USE master;

-- Create Login for Partner/ Principal

CREATE LOGIN SQLPRIN_LOGIN  WITH PASSWORD = 'password';

-- Create User for above Login (1)

CREATE USER  SQLPRIN_USER  FOR LOGIN  SQLPRIN_LOGIN ; 

-- Create Certificate for above User (2) using Coppid Partner's (Principal) Certificate File

CREATE CERTIFICATE   SQLPRIN_CER_FOR_SEC

   AUTHORIZATION  SQLPRIN_USER  

   FROM FILE = 'C:\SQL_Certificate\SQLPRIN_CER.cer'

-- Grand Connect to the Endpoint for above Login (1)

GRANT CONNECT ON  ENDPOINT::Mirroring_S  TO  SQLPRIN_LOGIN ; 

STEP 5 : (Final STEP)  Set partner for each other. 

NOTE: It is must to set Partner in Secondary/Mirror server FIRST.

If Server Name failed to set Partner, use IP instead.

Secondary Server Partner:

USE master;

ALTER DATABASE  TEST_DB

SET PARTNER = 'TCP://SQLPRIN:5022';

 -- SET PARTNER = 'TCP://192.168.56.1:5022';

Principal Server Partner:

USE master;

ALTER DATABASE  TEST_DB

SET PARTNER = 'TCP://SQLSEC:5022';

 -- SET PARTNER = 'TCP://192.168.56.2:5022';

If SET PARTNER Failed, perform below tasks

1. SET Partner Off in Secondary
2. Check Windows Firewall Rule is Enabled
3. Check the Port is Open (using Telnet)

-- SET Partner Off in Secondary

Secondary Server Partner:

USE master;

ALTER DATABASE  TEST_DB

SET PARTNER OFF;

-- Check Windows Firewall Rule is Enabled

-- Check the Port is Open (using Telnet)

    telnet [domainname or ip] [port] 

    -->ex --> telnet SQLSEC 5022    OR  telnet  192.168.56.2  5022

Please let me know if you get any problem, in comments.