Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Service Broker / January 2008

Tip: Looking for answers? Try searching our database.

users and logins and certificate-based authentication?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Craig Thomas - 21 Dec 2007 03:06 GMT
I read the Service Broker Tutorials, which have a description of using
Windows authentication for "a conversation between instances". And I read the
book, Pro SQL Server 2005 Service Broker, which has a description of using
certificate-based authentication for such a conversation.

But I'm not clear on the different approaches the two take on logins and
users:

Service Broker Tutorials shows 4 users, created "WITHOUT LOGIN":
1. an initiator user in the initiator instance,
2. a target user in the initiator instance,
3. an initiator user in the target instance, and
4. a target user in the target instance.

The Pro SQL Server 2005 Service Broker example shows just two users for a
conversation, both created "FOR LOGIN":
1. target user on the initiator instance, and
2. initiator user on the target instance.

Is this a matter of necessity, because of the use of certificate based
authentication? Or is this a matter of a stylistic difference in approach? Is
there a clear best practice here?

(I know I could hack on the two examples, and perhaps reveal a truth or
falsehood, but I confess I got bogged down in subtleties with whether the
certificates were created in the master or the user databases, and whether
that affected their use in authorizing CREATE SERVICE statements or CREATE
CERTIFICATE statements.)

In the system I'm building, there will be a central service, and dozens (or
even hundreds) of remote services, all in different Windows domains. So the
use of certificate-based authentication is necessary.

Handling 2 times dozens (or 2 times hundreds) of certificate public key
files, private key backups, master key backups, etc. is starting to look like
a system administration challenge.

But if we have to manage passwords for logins for the users, as well, plus
hardening permissions grants, well, that's even more system administration.

Any and all pointers or advice would be appreciated in the extreme!

Thanks,
--Craig.
Adam Machanic - 22 Dec 2007 15:56 GMT
Hi Craig,

There is a chapter on this topic in the book listed in my signature.  I also
did a webcast for MSDN, based on the content from the chapter, which you can
find here:

http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032334738&Culture=en-US

I also highly recommend this article:

http://sommarskog.se/grantperm.html

... as well as Laurentiu Cristofor's blog:

http://blogs.msdn.com/lcris/

I think if you take a look at some of these resources they should adequately
fill in the blanks for you; post back if you have any unanswered questions.

Signature

Adam Machanic
SQL Server MVP - http://sqlblog.com

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220

>I read the Service Broker Tutorials, which have a description of using
> Windows authentication for "a conversation between instances". And I read
[quoted text clipped - 46 lines]
> Thanks,
> --Craig.
Craig Thomas - 03 Jan 2008 17:19 GMT
Hi Adam,

Thanks very much for the terrific references. I haven't been able to get
your book yet - my local bookstores are out of stock.

I'm left with one conceptual gap, specifically about service broker's
transport security:

- What mechanism is used to specify which credentials are presented to (say)
InstanceB when a user is executing BEGIN DIALOG CONVERSATION and SEND ON
CONVERSATION code on InstanceA?

Must the SQL Server 2005 service process that hosts InstanceA be running as
a particular Windows user? (With a matching username in the SQL Server 2005
InstanceB?)

Is the CREATE REMOTE SERVICE BINDING (which is not discussed with any
consistency in all of the references I've located so far) required to
associate the credentials?

Maybe it is as plain aas the nose on my face, but I'm just not connecting
(and neither is my code...)

Thanks,
--Craig.

> Hi Craig,
>
[quoted text clipped - 65 lines]
> > Thanks,
> > --Craig.
Bob Beauchemin - 03 Jan 2008 19:36 GMT
Hi Craig,

I'm assuming that you are talking about endpoint security and using an
endpoint that has been configured with Windows (NTLM or KERBEROS or both)
auth. In that case, the credentials presented to the remote instance are the
credentials of the SQL Server service account. This account (in your
example, service account on instance A) must have a login on instance B that
has CONNECT privilege on the endpoint. This also means you must be using a
service account that has network credentials, as far as Windows security is
concerned (unless instances A and B are on the same computer).

Instance B also must have corresponding permissions to connect to instance
A, if your endpoint on A is configured to use Windows auth.

For endpoint security you can also use certificates, but that's another
story.

Remote service binding doesn't have anything to do with endpoint security,
but rather relates to dialog security. That's also another story.

Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb

> Hi Adam,
>
[quoted text clipped - 108 lines]
>> > Thanks,
>> > --Craig.
Craig Thomas - 03 Jan 2008 21:49 GMT
Hi Bob,

I'm interested in the "other story". My instances are on separate computers
in separate Windows domains. The gap in my understanding is around the use of
certificates for authenticating the service broker transport.

I have done a good deal of reading, and haven't yet found a "worked example"
of the use of certificates for this purpose.

So far, here's what I've got:
1. InstanceA is the initiator, InstanceB is the target.

2. On InstanceB, I create CertificateB. I create an endpoint on InstanceB,
using CertificateB. Then I export CertificateB to a file, and move it to
InstanceA.

3. On InstanceA, I create CertificateA. I create an endpoint on InstanceA,
using CertificateA. Then I export CertificateA to a file, and move it to
InstanceB.

4. On InstanceA, I create a login, LoginB; a user, UserB; then I create a
certificate from file created in step 2, "CREATE CERTIFICATE CertificateB
AUTHORIZATION UserB FROM FILE = '...'

5. On InstanceA, I grant connect permission on the endpoint to LoginB.

6. Back on InstanceB, I create a login, LoginA; a user, UserA; then create a
certificate from the file created in step 3, "CREATE CERTIFICATE CertificateA
AUTHORIZATION UserA FROM FILE = '...'

7. On InstanceB, I grant connect permission on the endpoint to LoginA.

I create a pair of messages, a contract, queues and services, and routes.

When I send a message, these are the symptoms:
- Only one row in the initiator's sys.conversation_endpoints. There should
be two, one for each direction of the conversation, right?
- In the initiator's sys.transmission_queue, the transmission_status column
is empty.

Service broker *is* enabled:
SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID() ;
returns 1 in both the initiator and target databases.

Pinging works, as does telnet to the port in my routes' addresses, in both
directions, so the firewalls are not getting in the way (heck, they're
disabled for now).

The event log has no messages on either machine.

Any suggestions about how to identify what is not working here? Is it
authentication? or is there some other issue?

Thanks,
--Craig.

> Hi Craig,
>
[quoted text clipped - 132 lines]
> >> > Thanks,
> >> > --Craig.
Craig Thomas - 04 Jan 2008 03:26 GMT
Bob, Adam,

All the pieces fit at last.

In addition to the suggestions from both of you, this great resource for
debugging service broker also came to my rescue:

http://rusanu.com/2005/12/20/troubleshooting-dialogs/

The resources Adam recommended provide the background for solid user-based
security.

And thanks, Bob, for steering me away from the cliff of "CREATE REMOTE
SERVICE BINDING". I'll build a bridge to cross that chasm next.

Thanks,
--Craig.

> Hi Bob,
>
[quoted text clipped - 188 lines]
> > >> > Thanks,
> > >> > --Craig.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.