SqlDependency not updating cache

Question

I'm on a project using .NET 4.5, MVC, EF 6

I had naively implemented a caching system using the HttpRuntime cache and needed to invalidate the data I cache on updates to that data; except I forgot to take into account that our production server is published to a load balanced set of two servers... :|

So on production, after the data was updated, the app would sometimes serve the right data, and sometimes the old data depending on which server the request was hitting. Bad news bears.

So I decided to define a dependency on the SQL table AcademicTerms which is where my data is coming from. But I did something wrong, and I'm not sure what.

SQL that I ran to set up the permissions after enabling the Service Broker

EXEC sp_addrole 'sql_dependency_role'

GRANT CREATE PROCEDURE to sql_dependency_role
GRANT CREATE QUEUE to sql_dependency_role
GRANT CREATE SERVICE to sql_dependency_role
GRANT REFERENCES on
  CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to sql_dependency_role
GRANT VIEW DEFINITION TO sql_dependency_role
GRANT SELECT to sql_dependency_role
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sql_dependency_role
GRANT RECEIVE ON QueryNotificationErrorsQueue TO sql_dependency_role

EXEC sp_addrolemember 'sql_dependency_role', 'MY_ASPNET_APP_USERNAME'

My implementation of inserting new data after fetching and thus setting up the SqlDependency (hopefully less naive!):

private void insertIntoCache(
  AcademicTermLockingInfo newItem,
  string itemKey,
  Guid termID) {

  var dbContextConnection = db.Database.Connection;

  var connectionString = dbContextConnection.ConnectionString;
  // important step otherwise it won't work
  SqlDependency.Start(connectionString);

  CacheItemPolicy policy = new CacheItemPolicy {
    AbsoluteExpiration = DateTime.UtcNow.AddMonths(6)
  };

  CacheItem item = new CacheItem(itemKey, newItem);

  using (SqlConnection connection = new SqlConnection(connectionString)) {
    connection.Open();
    // command which will be used to notify updates - probably want to parametrize this
    using (SqlCommand command = 
     new SqlCommand(
        String.Format("SELECT Name, LockDate FROM dbo.AcademicTerms WHERE ID = '{0}'",
          termID),
        connection)) {

        SqlDependency dependency = new SqlDependency(command);
        SqlChangeMonitor monitor = new SqlChangeMonitor(dependency);

        policy.ChangeMonitors.Add(monitor);
        MemoryCache.Default.Set(item, policy);

        // execute once otherwise dependency not registered
        command.ExecuteNonQuery();

    }
  }
}  

Any help would be very much appreciated!

---

Things I've done:

• Created two new users in SQL Server, net and sanba
• Added every NT* login and the sa login to the net user, added net to the sql_dependency_role
• Ran grant alter on schema::sql_dependency_role to net and grant alter on schema::dbo to net
• Check that my local SQL Server's Broker Enabled option is True under Service Broker
• Tried the web cache and the Memory Cache interchangeably (probably wouldn't change anything)
• Tried making the sql command string have a fully qualified name DevUMS.dbo.AcademicTerms and dbo.AcademicTerms
• I queried the sys.dm_qn_subscriptions and saw I had one subscription, good!
• I queried DevUMS.sys.transmission_queue and found an excpetion!
  An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
• I found this SO post with the same error

Answer 1

The secret sauce I was missing was alter authorization on database::DevUMS to [sa]; which I found on the linked SO post's answer.

There are a number of other steps, like adding a Role to use the appropriate login, but honestly, I'm really unsure as to whether or not those are actually necessary.

I'm going to publish a little later on today, and then I'll try to do the minimal amount of steps and document that here. I found the documentation in the wild to be very scattered and poor, so I hope to have this answer be a definitive place to refer to in the future