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

#SqlSat308 I'm pleased to present in Houston on May 10th, 2014. "SQL Watchdog"

Woohoo!










I'm honored to have been chosen to present at SQL Saturday #308, Houston, on May 10th 2014.

This will be my Event Notifications presentation, more refined, running on SQL Server 2005 & SQL Server 2014.  We'll cover concepts all the way through a multi-server environment and more.  You'll see the advantages of running Event Notification - not only can you know within a second that code was run that might compromise or break your enviroment, but you can do other things with it, from TFS checkin of production changes (for later issues), ERRORLOG monitoring, killing unauthorized backups and more.

Naturally, in addition to me, there's a ton of big SQL people that'll be there.  Aaron Bertrand, Tim Costello, Tim Mitchell, John Sterrett, Denny Cherry, Lori Edwards, Chris Bell and more!  Sixty sessions in all!


http://www.sqlsaturday.com/308/eventhome.aspx