Event Notifications - using certificates

EN_certificate_setup.sql

--For the repository server I only have one cert and one key.  
--For the new servers I have to create a new certificate and key for each one.  

-------------------------------------------------------------
--Part 1, Run on new server adding to Event Notifications--
-------------------------------------------------------------
USE master
GO
--Creating master key and an user which will be used by the certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'';
 
--Create certificate that will be used for encrypting messages.
CREATE CERTIFICATE EN_Cert_ServerName
WITH SUBJECT = N''
,EXPIRY_DATE = '';
 
--shouldn't be needed, according to James...
-- "This is not needed, since the Master Key is doing the encryption and not the Symmetric Key."
--CREATE SYMMETRIC KEY  EN_SymmetricKey_ServerName
--WITH ALGORITHM = AES_256
--ENCRYPTION BY CERTIFICATE EN_Cert_ServerName
 
--SELECT * FROM sys.symmetric_keys
--SELECT * FROM sys.certificates
 
--Backup certificate
BACKUP CERTIFICATE EN_Cert_ServerName --add server name
       TO FILE = ''
    WITH PRIVATE KEY (FILE = '',
    ENCRYPTION BY PASSWORD = '');
 
--Create end point
CREATE ENDPOINT EN_Endpoint STATE = STARTED AS TCP ( LISTENER_PORT = 4022 ) 
FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE EN_Cert_ServerName );
 
--check endpoints
SELECT  *
FROM    sys.endpoints
 
--create login to be used by Service Broker
CREATE LOGIN EN_User WITH PASSWORD = N'';
CREATE USER EN_User FOR LOGIN EN_User;
 
--Grant permissions on endpoint to login
GRANT CONNECT ON ENDPOINT::EN_Endpoint TO EN_User;
 
--Get SB GUID for msdb for 2nd script
SELECT  service_broker_guid
FROM    sys.databases
WHERE   name = 'msdb'
--get IP address if needed
EXEC xp_cmdshell 'ipconfig'


------------------------------------------- 
--Part 2, run on EN repository server--
-------------------------------------------
USE master
GO
--Creating master key and an user which will be used by the certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'';
 
--Create certificate that will be used for encrypting messages.
CREATE CERTIFICATE EN_Rep_Cert_ServerName
       WITH SUBJECT = N'Certificate For EN Repository server'
          ,EXPIRY_DATE = '1/1/2199';
 
CREATE SYMMETRIC KEY  EN_Rep_SymmetricKey_ServerName
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EN_Rep_Cert_ServerName
 
BACKUP CERTIFICATE EN_Rep_Cert_ServerName
       TO FILE = N''
    WITH PRIVATE KEY (FILE = '',
    ENCRYPTION BY PASSWORD = '');
 
ALTER ENDPOINT EN_Endpoint STATE = STARTED AS TCP ( LISTENER_PORT = 4022 ) 
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS CERTIFICATE EN_Rep_Cert_ServerName, ENCRYPTION = SUPPORTED )
 
CREATE LOGIN EN_User WITH PASSWORD = N'';
CREATE USER EN_User FOR LOGIN EN_User;
GRANT CONNECT ON ENDPOINT::EN_Endpoint TO EN_User;
 
--Get SB GUID for 1st script
SELECT  service_broker_guid
FROM    sys.databases
WHERE   name = 'EventNotificationRec'
--get IP address if needed
EXEC xp_cmdshell 'ipconfig'
 
---------------------------------------------------------------------------------
--Copy over backed up certificates between EN repository server and new server.--  
---------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------------
--Part 3 - run on new server.
--------------------------------------------------------------------------------------------------------------
--Restore target certificate copied from the EN repository server, add EN rep servername
CREATE CERTIFICATE EN_Cert_Repository_ServerName --add server name
AUTHORIZATION EN_User
FROM FILE = N''
       WITH PRIVATE KEY (FILE = '',
       DECRYPTION BY PASSWORD = '')
      
USE msdb
go
CREATE ROUTE [ENAudit_DestinationRoute]
WITH SERVICE_NAME = 'ENAudit_Service',
BROKER_INSTANCE = '',  --Service Broker GUID 
ADDRESS = 'TCP://IP or ServerName:4022'  --add IP address or server name
 
CREATE EVENT NOTIFICATION [ENAudit_ServerDDLEvents] ON SERVER
FOR  DDL_SERVER_LEVEL_EVENTS,DDL_TABLE_EVENTS,DDL_VIEW_EVENTS,DDL_INDEX_EVENTS,DDL_SYNONYM_EVENTS,DDL_FUNCTION_EVENTS,DDL_PROCEDURE_EVENTS,DDL_TRIGGER_EVENTS,DDL_EVENT_NOTIFICATION_EVENTS,DDL_ASSEMBLY_EVENTS,DDL_TYPE_EVENTS,DDL_DATABASE_SECURITY_EVENTS,DDL_SSB_EVENTS,DDL_XML_SCHEMA_COLLECTION_EVENTS,DDL_PARTITION_EVENTS,AUDIT_LOGIN_FAILED,DEADLOCK_GRAPH
--and any other events you want to watch.
TO SERVICE 'ENAudit_Service', ''; --Service Broker GUID 
 
SELECT * FROM sys.server_event_notifications
 
--test by creating and dropping table
IF OBJECT_ID('master.dbo.z_delete_ENtest') IS NOT NULL
    DROP TABLE master.dbo.z_delete_ENtest
CREATE TABLE master.dbo.z_delete_ENtest ( id INT IDENTITY )
DROP TABLE master.dbo.z_delete_ENtest
 
--verify transmission queue is empty
SELECT  *
FROM    msdb.sys.transmission_queue
 
SELECT * FROM sys.certificates
 
 
------------------------------------------
--Part 4 - run on EN Repository Server--
------------------------------------------
--Restore certificate from new server added to EN
CREATE  CERTIFICATE EN_Cert_ServerName
   AUTHORIZATION EN_User
   FROM FILE = N''
       WITH PRIVATE KEY (FILE = '',
       DECRYPTION BY PASSWORD = '')
 
USE EventNotificationRec
go
CREATE ROUTE ENAudit_ReturnRoute_ServerName
WITH SERVICE_NAME = 'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',
BROKER_INSTANCE = '', ADDRESS = 'TCP://IP or ServerName:4022';
 
SELECT * FROM sys.routes
 
SELECT TOP 10 * FROM EventNotificationRec..ENAudit_Events ORDER BY id DESC
 
SELECT * FROM master.sys.certificates WHERE name LIKE 'EN_%'