-- 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