SqlDependency changes for RTM [Sushil Chordia]

  • Article

As mentioned in my previous blog, SqlDependency is a new feature in .Net framework 2.0, which provide a mechanism to notify an app when a cache is invalidated. We got enough feedback from customers in Beta 2 with regards ease of deployment (some issues here) and security that we decided to make some changes for the final release. These new changes are now available as part of the September CTP. Following is a quick example on how to get Notification working on the September CTP bits. (Things new in September CTP are marked in RED)

using System;
using System.Data;
using System.Data.SqlClient;
class QuikExe
{
public static string connectionstring = "Get Connection String From The Config File";
public void DoDependency()
{
using (SqlConnection conn = new SqlConnection(connectionstring))
{
conn.Open();
Console.WriteLine("Connection Opened...");

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select i from dbo.test";

//Notification specific code
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += delegate(Object o, SqlNotificationEventArgs args)
{
Console.WriteLine("Event Recd");
Console.WriteLine("Info:" + args.Info);
Console.WriteLine("Source:" + args.Source);
Console.WriteLine("Type:" + args.Type);
};

SqlDataReader r = cmd.ExecuteReader();
//Read the data here and close the reader
r.Close();
Console.WriteLine("DataReader Read...");
}
}

public static void Main()
{
try
{
//Start the listener infrastructure on the client
SqlDependency.Start(connectionstring);
QuikExe q = new QuikExe();
q.DoDependency();
Console.WriteLine("Wait for Notification Event...");
Console.Read();
}
finally
{
//Optional step to clean up dependency else it will fallback to automatic cleanup
SqlDependency.Stop(connectionstring);
}
}
}

You can compare it with the old Beta 2 example to get a feel of what has changed. One thing to note is that the APIs are not much different from Beta2, the internal implementation of SqlDependency is what that has changed a lot. Here are some changes will help you to get an idea of how dependency works.

Push Model to Pull Model
In Beta2 we had an HTTP/TCP listener that was active on the client that would listen on changes pushed by the server. The client for the first time acting as a listener added additional level of complexity. It was difficult to get the firewall set up right. Also, both the HTTP/TCP opened ports that were not secure.
In RTM, we have static method on SqlDependency called Start that

  • opens a new non-pooled connection
  • creates a new queue with a unique name is created
  • creates a new service with a unique name is created for that queue
  • creates a new procedure is created on the server to clean up the transient queue and service created in case the client is no longer listening
  • listens on the newly created queue for change notifications

One thing to note that there is only one connection that is opened per process (even if there are multiple calls to the default Start() method). This connection then pulls the change notification from the server queue and raises the appropriate event. Since the underlying listener infrastructure uses the normal connection, it doesn’t add any additional deployment nightmares and provides the same level of security as the underlying connection (SSL/Integrated Authentication). As expected a corresponding static method Stop is also implemented for closing that connection.

Ability to listen on custom queues
In Beta2, there was no way to listen on custom queue and services created by the user using the SqlDependency infrastructure. This had to solely be done using SqlNotificationRequest
In RTM, we have added SqlDependency.Start() to take a custom queue name as a parameter which will listen on the this new queue. In addition, when creating the SqlDependency the options property should be set to specify the SSB service, so that the resultsets associated with the dependency can bind to this service.

SqlNotificationPermission removed
In Beta2, we had SqlNotificationPermission that was used to restrict the listeners (HTTP/TCP) that could be opened on the client.
In RTM, since there is no concept of opening ports on the client, this is no longer necessary. SqlClientPermission should be sufficient for dependency infrastructure at the CAS level.

Event name change
In Beta2, The SqlDependency class had an event OnChanged which has been renamed to OnChange to RTM.

Required Database Permissions
The underlying implementation change has caused the minimum required database permission to change as well. Below is the list of permissions needed. Consider that you have two users: a user that calls SqlDependency.Start() (startUser) with no custom queue options and user that execute a command set up for Dependency(executeUser). In practice, these can be same as well. SQL statements in RED are the new permission changes for RTM.

  1. --DBA creates a new role

  2. EXEC sp_addrole 'sql_dependency_subscriber'

  4. --Permissions needed for startUser

  5. GRANT CREATE PROCEDURE to startUser

  6. GRANT CREATE QUEUE to startUser

  7. GRANT CREATE SERVICE to startUser

  8. GRANT REFERENCES on CONTRACT::[https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to startUser

  9. GRANT VIEW DEFINITION TO startUser

  11. --Permissions needed for executeUser

  12. GRANT SELECT to executeUser

  13. GRANT SUBSCRIBE QUERY NOTIFICATIONS TO executeUser

  14. GRANT RECEIVE ON QueryNotificationErrorsQueue TO executeUser

  15. GRANT REFERENCES on CONTRACT::[https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to executeUser

  16. EXEC sp_addrolemember 'sql_dependency_subscriber', 'executeUser'

Lets go over the permissions one by one.

  • Line 1: Creates a new role sql_dependency_subscriber
  • Lines 5,6,7: Call to Start creates a queue, procedure and service as discussed earlier, hence we need these permissions for the startUser.
  • Line 8: The service created references the PostQueryNotificationContract and hence it requires REFERENCES permission for the startUser.
  • Line 9: Call to Start refers the system views for seeing if the role sql_dependency_subscriber is available. If it is available it grants the SEND permission on the transient queue that is created to this role.
  • Line 12: The executeUser should have SELECT permissions to issue SELECT command that is bound with SqlDependency
  • Lines 13,14,15: Since the executeUser binds a command to notification, it needs Subscription permission
  • Lines 16: To receive notifications on the transient queue, executeUser needs to be part of a new role sql_dependency_subscriber

The above just illustrates the permissions required. It might be much more manageable to create roles and then assign database permission to the role instead of the user. Then adding users to this role will automatically provide SqlDependency access to the users.

Conclusion: The above is an overview of changes that went in for the final release. The idea was to make the deployment and security story of SqlDependency much more simpler. We are looking forward to your experiences with these new changes. Do send in your comments or feedback.
 

Sushil Chordia, ADO.Net team, Microsoft.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights