SQL Server Forum / Other Technologies / Service Broker / January 2008
users and logins and certificate-based authentication?
|
|
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.
|
|
|