Yesterday I delivered a session for the PASS DBA Virtual Chapter on Service Broker. In preparing for the session I was having trouble with my demo environment.

Since early this year, after I upgraded my laptop to a Lenovo W510 with 10GB RAM, I've been using VM Workstation for my demo and test machines, replacing the Microsoft Virtual PC demos I'd been using for years. The problem was that I couldn't get the VMWare demo machine to successfully send the broker messages. I fired up Virtual PC, brought up my old demo machine, and voila, it worked just fine, so that's what I used, my old demo machine, running Windows Server 2003 R2 with SQL Server 2008 SP1.

Afterwards, I went back to VMWare, where my demo machine has Windows Server 2008 R2 and SQL Server 2008 R2, to see what's up. Now, I try to follow best practices, so I have a non-privileged account as the service account on my demo machines. Working my way through the messages I received from sys.transmission_queue, I found that the SQL Server service account required CONNECT privileges on the Endpoint I'd set up for Service Broker.

OK, so I go to do so, but I see that the service account isn't a valid login in SQL Server 2008 R2. (It was in SQL Server 2008, in fact it was a member of the sysadmin role.) So the first thing I needed to do was create a minimally privileged login for the service account, like this:

USE [master]
GO
CREATE LOGIN [SQLTBWS\sqlexec] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Once that was in place I was then able to grant the CONNECT privilege on the endpoint to that account:

GRANT CONNECT ON ENDPOINT::[INST01Endpoint] to [SQLTBWS\sqlexec]
GO

And magically, the messages went flying through as expected.

Whew!

Allen