-- Name:
-- sp_MSaddreplsymmetrickey
-- Description:
-- This procedure creates the following:
-- Database symmetric key based on a password seed
-- Database level certificate with associated asymmetric public/private keys
-- Database level scoped replication symetric key used for encrypting
-- Parameters:
-- See the procedure definition.
-- Returns:
-- 0 - On success
-- 1 - On Failure
-- Result:
-- None
-- Security:
-- None as this procedure is not public.
CREATE PROCEDURE sys.sp_MSaddreplsymmetrickey
AS
BEGIN
DECLARE @error_number int,
@error_severity int,
@error_state int
BEGIN TRY
-- this creates a certificate and associated asymmetric public/private keys in
-- the database and encrypts the private key using the database master key.
CREATE CERTIFICATE SQLSERVER_REPLICATION
WITH SUBJECT = 'SQLSERVER_REPLICATION'
END TRY
BEGIN CATCH
-- we eat the following errors:
-- Msg 15232, Level 16, State 1, Line 1
-- A certificate with name 'SQLSERVER_REPLICATION' already exists or this certificate already has been added to the database.
IF @@ERROR NOT IN (15232)
BEGIN
SELECT @error_number = ERROR_NUMBER(),
@error_severity = ERROR_SEVERITY(),
@error_state = ERROR_STATE()
RAISERROR(@error_number, @error_severity, @error_state)
RETURN 1
END
END CATCH
BEGIN TRY
-- this creates the database scoped replication symmetric key that will be used
-- later to encrypt replication passwords. Every time a password is stored by
-- replication, the stored procedure code should first query to see if the
-- SQLSERVER_REPLICATION key already exists and create it prior to storing the
-- password if does not exist. The server encrypts the symmetric key using the
-- private key of the certificate created in the previous step. The master key
-- is always available to local users and does not need to be opened. By contrast,
-- the symmetric key must be opened to be used.
CREATE SYMMETRIC KEY SQLSERVER_REPLICATION
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE SQLSERVER_REPLICATION
END TRY
BEGIN CATCH
-- we eat the following error:
-- Msg 15282, Level 16, State 1, Line 1
-- A key with name 'SQLSERVER_REPLICATION' or user defined unique identifier already exists or you do not have permissions to create it.
IF @@ERROR NOT IN (15282)
BEGIN
SELECT @error_number = ERROR_NUMBER(),
@error_severity = ERROR_SEVERITY(),
@error_state = ERROR_STATE()
RAISERROR(@error_number, @error_severity, @error_state)
RETURN 1
END
END CATCH
RETURN 0
END