Solution Using Certificates Authentication on Production Servers

What you need is:

2 Servers and 1 Witness

Create a database on Principal Server

Backup the database 2 times: 1st time do a “FULL” backup, 2nd time do a “Transaction Log” backup

Restore the database on Mirror Server, MUST use option “NO RECOVERY” !!! After restore, the mirror database will not take any request, that is completely normal.

Fully Qualified Domain Name for all 3 servers…..  you can do this by: 1. Setup a domain, or 2. Change the computer name and modify the “HOSTS” file on the 3 servers (C:\WINDOWS\system32\drivers\etc)

OK ! Now we shall start!

HOST 1 = Principal Server

HOST 2 = Mirror Server

HOST 3 = Witness Server

You need to copy the certificate between the servers manually.

Here is the SQL code:

HOST 1

create master key encryption by password = ‘password’;
GO

create certificate HOST_1_cert with subject = ‘HOST_1 certificate’, start_date = ‘2007/11/01′, expiry_date = ‘2099/11/01′;
GO

Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate HOST_1_cert, encryption = disabled, role = all);
GO

Backup certificate HOST_1_cert to file = ‘c:\HOST_1_cert.cer’;
GO

HOST 2

create master key encryption by password = ‘password’;
GO

create certificate HOST_2_cert with subject = ‘HOST_2 certificate’, start_date = ‘2007/11/01′, expiry_date = ‘2099/11/01′;
GO

Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication = certificate HOST_2_cert, encryption = disabled, role = all);
GO

Backup certificate HOST_2_cert to file = ‘c:\HOST_2_cert.cer’;
GO

HOST 3

create master key encryption by password = ‘password’;
GO

create certificate HOST_3_cert with subject = ‘HOST_3 certificate’, start_date = ‘2007/11/01′, expiry_date = ‘2099/11/01′;
GO

Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 5022, listener_ip = all)
for database_mirroring (authentication = certificate HOST_3_cert, encryption = disabled, role = witness);
GO

Backup certificate HOST_W_cert to file = ‘c:\HOST_3_cert.cer’;
GO

Now Copy 3 Certificates on all Servers.

HOST 1 again

create login HOST_2_login with PASSWORD = ‘password’;
GO

create user HOST_2_user from login HOST_2_login;
GO

Create certificate HOST_2_cert
Authorization HOST_2_user
From file = ‘c:\HOST_2_cert.cer’;
GO

Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_2_login];
GO
———————————————————————————–

create login HOST_3_login with PASSWORD = ‘password’;
GO

create user HOST_3_user from login HOST_3_login;
GO

Create certificate HOST_3_cert
Authorization HOST_3_user
From file = ‘c:\HOST_3_cert.cer’;
GO

Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_3_login];
GO

HOST 2 again

create login HOST_1_login with PASSWORD = ‘password’;
GO

create user HOST_1_user from login HOST_1_login;
GO

Create certificate HOST_1_cert
Authorization HOST_1_user
From file = ‘c:\HOST_1_cert.cer’;
GO

Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_1_login];
GO

—————————————————————————-

create login HOST_3_login with PASSWORD = ‘password’;
GO

create user HOST_3_user from login HOST_3_login;
GO

Create certificate HOST_3_cert
Authorization HOST_3_user
From file = ‘c:\HOST_3_cert.cer’;
GO

Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_3_login];
GO

HOST 3 again

create login HOST_1_login with PASSWORD = ‘password’;
GO

create user HOST_1_user from login HOST_A1_login;
GO

Create certificate HOST_1_cert
Authorization HOST_1_user
From file = ‘c:\HOST_1_cert.cer’;
GO

Grant CONNECT ON Endpoint::Endpoint_mirroring to [HOST_1_login];
GO

———————————————————————

create login HOST_2_login with PASSWORD = ‘password’;
GO

create user HOST_2_user from login HOST_B2_login;
GO

Create certificate HOST_2_cert
Authorization HOST_2_user
From file = ‘c:\HOST_2_cert.cer’;
GO

Grant CONNECT ON Endpoint::endpoint_mirroring to [HOST_2_login];
GO

HOST B again

alter database yourdbname set partner = ‘TCP://hostname.ace.local:5022′;
GO

HOST A again

alter database yourdbname set partner = ‘TCP://hostname.ace.local:5022′;
GO

alter database yourdbname set witness = ‘TCP://hostname.ace.local:5022′;
GO

We implemented it successfully on production servers.

If someone facing any problem write it down !!