Using notifications (or rather the SQL Broker engine) in BizTalk is a great way to utilize Microsoft technology and platform uniformity. There is a great article about it in the Microsoft BizTalk 2010: Line of business Systems Integration book, and Richard Seroter had a good article about it back in the day.
I found something problematic though: When using samples I got all kinds of strange security exceptions I wanted to use integrated security and grand the BizTalk Service user enough (but just enough) rights to make notifications work using the WCF SQL adapter in the 2010 adapter pack, together with SQL Server 2008. This, is hard.
You can take the cowards way out and make the BizTalk service user a database owner. That is not good for security and SQL Server IT Pros would rightfully have a problem with that.
Digging around a bit I found a very useful article about Using Query Notifications and also one called Receiving Query Notifications from the product documentation. Either of these told me how to handle the delicate security issue. So this is what I came up with:
USE MyDatabase
— Only enable broker if needed
if ( SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDatabase' ) = 0 begin
ALTER DATABASE OrderNumber SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE OrderNumber SET ENABLE_BROKER;
ALTER DATABASE OrderNumber SET MULTI_USER;
end
— Create a role and a new schema
CREATE ROLE [BizTalkNotification] AUTHORIZATION [dbo]
GO
CREATE SCHEMA [BizTalkNotification] AUTHORIZATION [DOMAIN\BTS_SRV_USR]
GO
–Database level permissions
GRANT CREATE PROCEDURE to [BizTalkNotification];
GRANT CREATE QUEUE to [BizTalkNotification];
GRANT CREATE SERVICE to [BizTalkNotification];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [BizTalkNotification];
GRANT VIEW DEFINITION TO [BizTalkNotification];
–Service Broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [BizTalkNotification];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [BizTalkNotification];
GO
— Add the user to the role and connect the schema
EXEC sp_addrolemember N'BizTalkNotification', N'DOMAIN\BTS_SRV_USR';
GO
ALTER USER [DOMAIN\BTS_SRV_USR] WITH DEFAULT_SCHEMA=[BizTalkNotification];
GO
This will not only work perfectly, it will also satisfy most security levels within your company.
Happy notification!!!
Blog Post by: Mikael Sand