[Event Notifications] Using Certificates for Authentication instead of AD/Kerberos

My normal environment that I run is pretty simple.  One trusted domain.  So security for Event Notifications is pretty simple too - I just use Kerberos.  It's simple, easy to read, nothing fancy involved, and I wanted to make sure that people weren't going to look at the prerequisites and immediately say "I don't want to deal with keys and certificates and all that - so I'm not going to do it". 

A former coworker isn't as lucky - he has multiple, untrusted domains.  So he had to get it working.  And much to my surprise, it was considerably easier than I'd thought, from reading about Service Broker certificates (literally, one of the demos in the book I read used _7_ certificates).  Yet again, I dig what MS has done with EN, since it's a simple use-case of Service Broker.


Note that we're doing encryption, creating master keys and certificates.  You may need these keys in order to restore the database - that's what I've heard over the years.  But from what it sounds like, technically you don't need it as nothing is encrypted.  However, I'd be really paranoid and test this out on a test server first, specifically the whole "restore the databases without the keys" part.. 

TL;DR -  Script is below, test restores before putting in production.  You'll create 1 key & 1 cert on your Repository, and then on each monitored server you'll create 1 key & 1 cert, swapping keys with the Repository. 

Many thanks to James for doing all the hard work on this!

	--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_%'

view raw EN_certificate_setup.sql hosted with ❤ by GitHub