THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Endpoints Need Privileges Too

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

Published Thursday, May 26, 2011 5:16 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement