Database Mirroring using certificate authentication

SQL Servers:

Server -----SQL Server 2005 with sp1 ---Principal server

KingPC-------SQL Server 2005 with sp1 –Mirror partner

To configure database mirroring with certificate authentication, you must work on the master database.

SERVER:

USE MASTER;

CREATE MASTER KEY ENCRYPTION BY PASSWORD=’password’;

GO

CREATE CERTIFICATE ServerCertificate

WITH SUBJECT='Server Certificate';

GO

CREATE ENDPOINT Mirroring

STATE=STARTED

AS TCP(

LISTENER_PORT=5024,

LISTENER_IP=ALL)

FOR DATABASE_MIRRORING(

AUTHENTICATION=CERTIFICATE ServerCertificate ENCRYPTION=REQUIRED ALGORITHM RC4,

ROLE=ALL);

GO

/* This is for outbound authentication. When the Mirror endpoint communicates with KingPC, it uses the ServerCertificate.

--You must have permissions to write to the folder (C:\Certificates)

BACKUP CERTIFICATE ServerCertificate TO FILE=

'C:\Certificates\ServerCertificate.cer';

KingPC

USE MASTER;

CREATE MASTER KEY ENCRYPTION BY PASSWORD=’password’;

GO

CREATE CERTIFICATE KingPCCertificate

WITH SUBJECT='KingPC Certificate';

GO

CREATE ENDPOINT Mirroring

STATE=STARTED

AS TCP(

LISTENER_PORT=5024,

LISTENER_IP=ALL)

FOR DATABASE_MIRRORING(

AUTHENTICATION=CERTIFICATE KingPCCertificate ENCRYPTION=REQUIRED ALGORITHM RC4,

ROLE=ALL);

GO

/*

When the mirroring endpoint communicates with the Server.VIP.COM, it uses the KingPCCertificate.*/

--You must have permissions to write to the folder (C:\Certificates)

BACKUP CERTIFICATE KingPCCertificate TO FILE=

'C:\Certificates\KingPCCertificate.cer';

Server:

You must copy the KingPCCertificate from KingPC to Server, e.g. C:\Certificates folder.

USE MASTER

CREATE LOGIN KingPCLogin WITH PASSWORD='password';

GO

CREATE USER KingPCLogin FOR LOGIN KingPCLogin;

--Associate the KingPCCertificate.cer to the user: KingPCLogin

CREATE CERTIFICATE KingPCCertificate

AUTHORIZATION KingPCLogin

FROM FILE='C:\Certificates\KingPCCertificate.cer'

GO

GRANT CONNECT ON ENDPOINT::Mirroring TO KingPCLogin;

KingPC:

You must copy the ServerCertificate from Server to KingPC, e.g. C:\Certificates folder.

USE MASTER

CREATE LOGIN ServerLogin WITH PASSWORD='password';

GO

CREATE USER ServerLogin FOR LOGIN ServerLogin;

--Associate the ServerCertificate.cer to the user: ServerLogin

CREATE CERTIFICATE ServerCertificate

AUTHORIZATION ServerLogin

FROM FILE='C:\Certificates\ServerCertificate.cer'

GO

GRANT CONNECT ON ENDPOINT::Mirroring TO ServerLogin;

Server:

--Ensure the AdventureWorks database is in FULL RRECOVERY mode

ALTER DATABASE AdventureWorks

SET RECOVERY FULL;

GO

USE AdventureWorks

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\Certificates\AdventureWorks.bak'

WITH FORMAT

GO

KingPC:

Copy the AdventureWorks.bak from Server to c:\Certificates folder at KingPC.

RESTORE DATABASE AdventureWorks

FROM DISK = 'C:\Certificates\AdventureWorks.bak'

WITH NORECOVERY

GO

--Configuring a mirror partner

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://Server:5024';

GO

Server: --Principal database

ALTER DATABASE AdventureWorks

SET PARTNER = 'TCP://KingPC:5024';

GO

ALTER DATABASE AdventureWorks

SET PARTNER SAFETY OFF