How to Set Up Mirroring in a Workgroup Using Certificate Authentication

The following guide steps you through the process of setting up a SQL mirroring configuration between two servers on a work group (non-domain) environment, using certificate based authentication. The configuration below includes the setup of a witness server, however this can be omitted if no automatic fail-over is required (noting that a witness server can be an express edition of SQL).

Please note the variables referenced throughout the script will need to be searched / replaced, and appropriate certificate expiration dates set based upon your requirements.

-- Principal = %%PRINCIPAL%%
-- Mirror = %%MIRROR%%
-- Witness = %%WITNESS%%
-- Encryption Password = %%SECUREPASSWORD%%
-- HTTP Endpoint Port = %%PORT%%
-- ON THE PRINCIPAL
IF (select Count(*) from sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0
BEGIN
CREATE master key Encryption by password = '%%SECUREPASSWORD%%';
END
create certificate %%PRINCIPAL%%_cert with subject = '%%PRINCIPAL%% certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO 
-- IF ENDPOINT ALREADY EXISTS --
SELECT name, endpoint_id,type,type_desc,state_desc
FROM master.sys.endpoints
drop endpoint Mirroring
GO
-- END IF -- 
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = %%PORT%%, listener_ip = all)
for database_mirroring (authentication = certificate %%PRINCIPAL%%_cert, encryption = disabled, role = all);
GO
Backup certificate %%PRINCIPAL%%_cert to file = 'c:%%PRINCIPAL%%_cert.cer';
GO 
-- ON THE MIRROR
create master key encryption by password = '%%SECUREPASSWORD%%';
GO
create certificate %%MIRROR%%_cert with subject = '%%MIRROR%% certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = %%PORT%%, listener_ip = all)
for database_mirroring (authentication = certificate %%MIRROR%%_cert, encryption = disabled, role = all);
GO
Backup certificate %%MIRROR%%_cert to file = 'c:%%MIRROR%%_cert.cer';
GO 
-- ON THE WITNESS
create master key encryption by password = '%%SECUREPASSWORD%%';
GO
create certificate %%WITNESS%%_cert with subject = '%%WITNESS%% certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = %%PORT%%, listener_ip = all)
for database_mirroring (authentication = certificate %%WITNESS%%_cert, encryption = disabled, role = witness);
GO
Backup certificate %%WITNESS%%_cert to file = 'c:%%WITNESS%%_cert.cer';
GO 
-- ON THE PRINCIPAL AGAIN...
drop user %%MIRROR%%_user;
drop login %%MIRROR%%_login;
create login %%MIRROR%%_login with PASSWORD = '%%SECUREPASSWORD%%';
GO
create user %%MIRROR%%_user from login %%MIRROR%%_login;
GO
select * from sys.certificates
Create certificate %%MIRROR%%_cert
Authorization %%MIRROR%%_user
From file = 'C:%%MIRROR%%_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [%%MIRROR%%_login];
GO
------
create login %%WITNESS%%_login with PASSWORD = '%%SECUREPASSWORD%%';
GO
create user %%WITNESS%%_user from login %%WITNESS%%_login;
GO
Create certificate %%WITNESS%%_cert
Authorization %%WITNESS%%_user
From file = 'C:%%WITNESS%%_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [%%WITNESS%%_login];
GO 
-- ON THE MIRROR AGAIN...
create login %%PRINCIPAL%%_login with PASSWORD = '%%SECUREPASSWORD%%';
GO
create user %%PRINCIPAL%%_user from login %%PRINCIPAL%%_login;
GO
Create certificate %%PRINCIPAL%%_cert
Authorization %%PRINCIPAL%%_user
From file = 'c:%%PRINCIPAL%%_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [%%PRINCIPAL%%_login];
GO
-------
create login %%WITNESS%%_login with PASSWORD = '%%SECUREPASSWORD%%';
GO
create user %%WITNESS%%_user from login %%WITNESS%%_login;
GO
Create certificate %%WITNESS%%_cert
Authorization %%WITNESS%%_user
From file = 'c:%%WITNESS%%_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [%%WITNESS%%_login];
GO 
-- ON THE WITNESS AGAIN...
create login %%PRINCIPAL%%_login with PASSWORD = '%%SECUREPASSWORD%%';
GO
create user %%PRINCIPAL%%_user from login %%PRINCIPAL%%_login;
GO
Create certificate %%PRINCIPAL%%_cert
Authorization %%PRINCIPAL%%_user
From file = 'c:%%PRINCIPAL%%_cert.cer';
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [%%PRINCIPAL%%_login];
GO
-------
create login %%MIRROR%%_login with PASSWORD = '%%SECUREPASSWORD%%';
GO
create user %%MIRROR%%_user from login %%MIRROR%%_login;
GO
Create certificate %%MIRROR%%_cert
Authorization %%MIRROR%%_user
From file = 'c:%%MIRROR%%_cert.cer';
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [%%MIRROR%%_login];
GO 
-- ON THE MIRROR AGAIN...
alter database databaseName set partner OFF
alter database databaseName set partner = 'TCP://%%PRINCIPAL%%:%%PORT%%';
GO 
-- FINALLY ON THE PRINCIPAL AGAIN...
alter database databaseName set partner OFF
alter database databaseName set partner = 'TCP://%%MIRROR%%:%%PORT%%';
GO
alter database databaseName set witness OFF
alter database databaseName set witness = 'TCP://%%WITNESS%%:%%PORT%%';
GO