rusanu.com

Service Broker services use certificates for authenticating message origin and for encrypting messages. I have explained in detail how this authentication works in my earlier post Conversation Authentication. The certificates used for service authentication are most times self-signed certificates created directly by SQL Server using CREATE CERTIFICATE and they expire by default one year after creation. When these certificates expire they have to be replaced and this article is goal is to help you do thisreplacement with no impact on production systems.

Identifying the certificates used by services

Here is a recap of the criteria Service Broker uses to pick a certificate to represent an service identity:

1. Service owner and certificate owner must be the same database principal
2. Certificate private key must be encrypted with the database master key
3. Certificate start date and expiry dates must be valid
4. Certificate must be ACTIVE FOR BEGIN_DIALOG = ON

If there are more certificates that satisfy the above criteria then Service Broker will pick the one with the latest expiration date. The following query will show the certificates that can be used by Service Broker and the services they are be used for:

select s.name, c.name, c.start_date, c.expiry_date
	from sys.services s
	join sys.certificates c on s.principal_id = c.principal_id
	where c.pvt_key_encryption_type = 'MK'
		and c.is_active_for_begin_dialog = 1
		and GETUTCDATE() BETWEEN c.start_date AND c.expiry_date
		and s.service_id > 2;

The query filters out the QueryNotifications and EventNotifications services because these two services do not pick certificates base don the service owner but instead they pick the ones used by the owner of the notification subscription.

Read the rest of this entry »

Recently I wanted to go over again Rick Vicik papers on high performance programs on the Windows platform. These papers are a true Bible for anyone in need to write truly highly scalable and high performance server applications. They address the back end C/C++ programming and explain how to properly use the Windows threading, optimize I/O and specially the importance of data cache conscious programming, NUMA object allocations and access locality and impact of data sharing on performance. I do find however that many of the principles explained there apply just as well to C# and .Net programming. I wanted to refresh my memory on some issues so I searched for them and to my delight I found that Rick updated the papers for Vista and Windows 2008 and had posted them as a three part series on the Windows Performance blog and I wanted to share these with my blog audience:

• Designing Applications for High Performance – Part I
• Designing Applications for High Performance – Part II
• Designing Applications for High Performance – Part III

Mr. Denny has a take at the conversation reuse and recycle topic: http://itknowledgeexchange.techtarget.com/sql-server/improving-sql-service-broker-performance/. This solution allows for a more flexible policy on conversation reuse replacing the hard coded @@SPID affinity I used in my original post with an application specific settings table.

I have covered before how certificate based authentication works for endpoints. I think is only fair to have a similar article covering how services use certificates for authentication and security. Service Broker conversations are very different though from endpoints in the way they use certificates for authentication and security. Unfortunately there is no similar protocol I could reffer to, like it was the case with endpoints and TLS.

Service Broker conversations need authentication in order to allow or reject the sender of a message as being permitted to send messages to a destination service. The message sender in this context is the service that initiated the conversation. In Service Broker does not authenticate the user that sent a message (ie. the user logged in that issues the SEND or BEGIN CONVERSATION statement). Because Service Broker is designed as a mean to communicate between applications, users connected to an application are a local concept that cannot be used for authentication purposes remotely. For example when Joe from accounting logs in to the Accounting application and this application has some data from the Inventory application, this later application will authorize the Accounting application not Joe. Perhaps the Inventory application doesn’t even have Joe’s credentials, and no one wants to add additional tight coupling between the applications by having them be aware of each other user base just to be able to communicate.

Read the rest of this entry »