SqlDependency is really easy to get working if you are an admin, but when using it in a web app (whether via the System.Data.SqlDependency or System.Web. Caching.SqlCacheDependency) it is not so easy when you are not running under an admin account, which is what you should be doing (although maybe Don Kiely and Andrew Duthie will make exceptions for Betas!) When you deploy your apps to a web server, you will need the Network Services account to the SqlDependency work.
In addition to enabling the service broker, I had to do two additional steps when working as a non-admin.
Grant Notification Permissions to specific database
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [MyDomain\MyGroup]
Grant NotificationErrorsQueue Perms to user (this is for all of SQL Server, so do it from Master)
GRANT RECEIVE ON dbo.[QueryNotificationErrorsQueue] TO [MyDomain\MyGroup]
But this still didn’t do the trick. In Beta2 there is still a problem when SQL Server is trying to send notifications to the web server that requires opening up ports. There is a wide range of ports to open up. I tried a few but still was unable to get through. This is a known issue and will not persist through to the release mode. I will keep at it and blog the key to success when I find it.
During my presentation at the MAD Code Camp, when I got to this demo, I had to log in as admin to run it.
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!