DialogError message type

Hi There

In my testing i have seen the following i just would like confirmation that i am correct.

When something happens to cause a Dialog Error say for example message validation fails.

I have noticed that the actual DialogError message always goes to the initiator queue, is this correct ?

If so then logic to handle the ErrorDialog message type only has to be in the initiator activated SP that logic is not required in the target queue activated SP since the ErrorDialog message always goes to the initiator queue, is that correct ?

Thanx

Both sides need to be prepared to deal with the error message. The same message validation can fail for a message sent on the other direction, or the initiator code can issue an explicit END CONVERSATION ... WITH ERROR (or even an ALTER DATABASE ... SET ERROR_BROKER_CONVERSATIONS), or the initiator can drop the contract used on the conversation, or the service. All these result in an error message being sent to the target, and the list is not complete.

HTH,
~ Remus

Dialog security with different instances of a service

I am in the process of evaluating the use of Service broker for messaging in a load balanced configuration, and am not having any success. My configuration is as follows.

1) Master database on Box #1 which is the initiator of all dialogs

2) Target database on Box #2

3) Target database on Box #3

One of my goals is that the databases on Box #2 and #3 are as close to identical as possible.

My routing table on Box #1 looks something like the following

Name remote_service_name broker_instance address TargetOne TargetService

E96DC67E-F696-4D93-8545-C2E92157E32D

tcp://server1:4022/ TargetTwo TargetService

56607120-26AA-4AAA-B9E4-F14689C40E41

tcp://server2:4022/

My messaging process begins with a dialog initiated from the master database (Box #1) to each of the target services. At this time, only the first server "tcp://server1:4022/" is receiving messages. The sys.transmission_queue shows a message outbound to "tcp://server2:4022/" with a blank status.

I believe that my delivery problem has something to do with the resolution of the dialog certificate need for the conversation. The MSDN documentation states that:

"Service Broker uses a remote service binding to locate the certificate to use for a new conversation...The certificate for user_name must correspond to the certificate for a user in the database that hosts the remote service."

I am confused as to how I would configure remote service binding if one can not create more than one binding for the same remote_service_name.

The following throws an exception on the last creation of the binding.

CODE SNIP

CREATE CERTIFICATE UFEDlgCertTargetPublic
AUTHORIZATION UFEDialogUser
FROM FILE='C:\certs\UFEDlgCertTarget.cer';

CREATE CERTIFICATE UFEDlgCertTarget2Public
AUTHORIZATION UFEDialogUser2
FROM FILE='C:\certs\UFEDlgCertTarget_2.cer';

CREATE REMOTE SERVICE BINDING [targetRSB]
TO SERVICE 'TargetService'
WITH USER = UFEDialogUser, ANONYMOUS=OFF ;
GO

CREATE REMOTE SERVICE BINDING [targetRSB2]
TO SERVICE 'TargetService'
WITH USER = UFEDialogUser2, ANONYMOUS=OFF ;
GO
/CODE SNIP

Msg 9619, Level 16, State 1, Line 1
Failed to create remote service binding 'targetRSB2'. A remote service binding for service 'TargetService' already exists.

I am obviously missing a clear understanding of dialog security, and would greatly appreciate an explanation of how certificates get resolved when more than one route exists for different instances of a service.

Any help would be appreciated
-Joel Neubeck

As you said in your BOL quote. You have to use the same certificate for both targets. That means you only have one remote service binding which specifies one user which own one certificate. The two target databases must have the same certificate. If you want to make the targets identical, the easiest way to get the same certificate on both boxes is to copy the database to the second box so everything - including the certificates - is truely identical.|||Thank you for your help.
-Joel

Dialog Security questions

Hi Remus

Thank you for the email reply.

However i am still not 100% clear, dialog security with certificates when there are hundreds of remote services seems a bit complicated.

I have a few more questions.

1. you said "When sending the first message (any direction), we look up the 'best' certificate for each user"

My inititor sevice is owned by dbo, i have created certificates only for named users not dbo, how would this work?

2. I am going to try settin gup this again, but at the central service i will create a seprate user for each remote service, authorize the remote certificate for this user and use this user in the remote service binding for that remote service, is this correct or am i still doing somehting wrong?

3. How does all of this relate to the endpoint security, i ahve the same scenario, 1 pricipal id with all remote certificates authorized for it for the endpoint? I have no idea how i would have to setup different user for each remote servcie as far as the endpoint goes as there is no remote service binding to dictate which user to use ?

I will keep try and experimenting maybe i will get it right sooner or later.

Thank You

Dietz wrote:

1. you said "When sending the first message (any direction), we look up the 'best' certificate for each user". My inititor sevice is owned by dbo, i have created certificates only for named users not dbo, how would this work?

If 'dbo' is the owner of the service, then dbo must have a certificate. I'm not a fan of having dbo own the services, but there isn't anything to prevent this from working.

Dietz wrote:

2. I am going to try settin gup this again, but at the central service i will create a seprate user for each remote service, authorize the remote certificate for this user and use this user in the remote service binding for that remote service, is this correct or am i still doing somehting wrong?

Yes, you will need a separate user for each service you are trying to address.

Dietz wrote:

3. How does all of this relate to the endpoint security, i ahve the same scenario, 1 pricipal id with all remote certificates authorized for it for the endpoint? I have no idea how i would have to setup different user for each remote servcie as far as the endpoint goes as there is no remote service binding to dictate which user to use ?

Dialog messages may hop multiple hosts (forwarders) and may be stored by intermediaries for a long time. This loosely coupled patern of dialogs prevents them from being able to do a handshake before exchanging messages. If the initiator is up from 6 am to 6 pm and the target is up from 6 pm to 6 am, a handshake involving 3 messages (quite typical in world of security: a request, a chalenge and a response to the challenge) would mean that any new dialog would first have to wait more than 24 hours for the handshake to complete. W/o the luxury of a handshake, dialogs must choose upfront the appropiate certificate to use. This is why dialog security needs a concept like a 'remote service binding', that binds the remote service name to a certificate to be used before sending the first message.

Endpoints/transport security on the other hand is always between two instances that can open a direct connection, so they can afford the luxury of a handshake. So there is no need to choose a certificate upfront, a handshake can exchange and then validate the certificates used (there's nothing new here, this is how SSL and TLS were working for years). As a result, there is no need for a concept like a 'remote endpoint binding'. This also implies that you can have one user own all the endpoint certificates from all the peers.

Thanks,
~ Remus

P.S. I've split your original post because I don't like 2 page threads, I have to scroll down all the time to get to the second page.

|||

Hi Remus

Thank you very much for all your help, i guess i am just going to have to keeping messing around until i truely understand it.

Thank you for all the assistance, i have this working now with a user for each remote service, i still dont understand how to properly create a non dbo certified user on both sides, and i cannot find any examples, i will just keep messing around i guess that is the best wat to learn.

Thanx

|||

"If 'dbo' is the owner of the service, then dbo must have a certificate. I'm not a fan of having dbo own the services, but there isn't anything to prevent this from working."

I would love to not use dbo, however i cannot find a good code examples with the proper steps and explanations on how to do this? Only good example i have uses dbo. Only other example i have found not using dbo is too simple for my solution with hundreds of possible initiators, and i am not sure how to implement properly. No offense but BOL sucks, and i cannot find any decent examples and the webcasts on service broker are very high level. Bottom line is that finding good Service broker resources on this type of scenario is virutally impossible.

But i do really do appreciate your feedback, Thanx

|||

You should use either the AUTHORIZATION clause of the CREATE statement, either the ALTER AUTHORIZATION statement. Here is an example:

-- when starting from scratch

create user [InitiatorServiceUser] without login;

create certificate [InitiatorServiceCertificate]

authorization [InitiatorServiceUser]

with subject = 'InitiatorService';

create service [InitiatorService]

authorization [InitiatorServiceUser]

on queue q;

-- when changing an existing 'dbo' owned service and certificate

create user [InitiatorServiceUser] without login;

alter authorization on certificate::[InitiatorServiceCertificate] to [InitiatorServiceUser];

alter authorization on service::[InitiatorService] to [InitiatorServiceUser];

HTH,
~ Remus

|||

Thanx Remus

This is also the section in Roger Wolter's book under dialog securoty that i did nto understand since i never did it and everything worked.

Does dbo automatically have any certificate cereated in the database, if not i dont really understand how it works without running this. But thanx for the answer i will do this in future Thanx

Dialog Security and Message Encryption

I understand that Dialog Security + certificates can be used to encryption individual dialogs. I have several demos working now that do just this.However, I don't fully understand exactly when the messages are encrypted, and if they are ever written to a queue on the initiating service prior to being encrypted. I want to make sure that admins can't simply query the transmission queues to get clear text messages, because I have strict requirements that I encrypt all personal data that is stored anywhere in a database.

BOL is a little unclear on this topic. The relevant doc reads:

For a dialog that uses security, Service Broker encrypts all messages sent outside a SQL Server instance. Messages that remain within a SQL Server instance are never encrypted. In dialog security, only the database that hosts the initiating service and the database that hosts the target service need to have access to the certificates used for security. That is, an instance that performs message forwarding is not required to have the capability to decrypt the messages that the instance forwards.

Does this imply that message destined for an external service aren't encrypted until they leave the instance? Or does Service Broker figure out that the message is destined for a remote service and therefore applies encryption to the message_body prior to the message hitting the transmission queue on the initiating service?

Many thanks, Kevin

Messages are encrypted only in traffic, as they are transmitted over the network. You cannot prevent an administrator (or user with proper permissions) from seeing the clear text of the messages in the transmission_queue. Storing the messages encrypted in the transmission_queue has several weaknesses/issues:
- it breaks cryptographic best practices as same cipher is sent over and over again in case of retries
- the message clear text can still be seen in the SEND statement T-SQL batch (and this can be monitored for)
- the message clear text can still be seen in the destination queue

In your case you have to use the encryption infrastructure of SQL Server (EncryptByKey, DecryptByKey) to encrypt/decrypt the sent payload, or do the encryption in the application code.

But since message data is ultimately originating from a table and usually is being stored (after RECEIVE) in a table, you have the requirement to store the data encrypted anyway, irellevant of Service Broker. IMHO the best option is to sent directly the encrypted data, as is stored in the table.

HTH,
~ Remus

|||

Thanks, that's kind of what I expected. I will define some sort of envelope message that includes the encrypted payload.

Out of curiosity, what format is the message_body column in when I query a queue? Is there any way I can decode that to see the actual xml? I'll need to demo that the payload is encrypted.

Cheers, Kevin

|||No encoding, just serialized. Is a simple VARBINARY(MAX). Doing a SELECT CAST(MESSAGE_BODY AS XML) FROM sys.transmission_queue will show the correct XML. The 0xFFFE at the beginning is the XML Byte Order Mark for UTF-16.|||

Remus Rusanu wrote:

But since message data is ultimately originating from a table and usually is being stored (after RECEIVE) in a table, you have the requirement to store the data encrypted anyway, irellevant of Service Broker. IMHO the best option is to sent directly the encrypted data, as is stored in the table.

I've been thinking about this and I'm going to have to correct myself. Sending directly the ecrypted column and storing received data as the encrypted column would require that the same key is used on both sites. This is a bad cryptographic practice and also can turn into a key maintainance nightmare when the keys are changed.

There should be a key to store the data on one site. This key must be used to decrypt the data before sending. The decrypted data should be encrypted it with a dedicated transmition key. The transmission key has to be shared or exhanged by the two parties involved. The receiver should use this transmission key to decrypt the payload. The decrypted payload should be then encrypted with the receiver's own 'storage' key.

I know there are a lot of encrypt/decrypt steps involved, but sharing the key between the sender and receiver can be dangerous/problematic.

The main problem is that the 'transmission' key must be somehow exchanged by the sender and receiver, this is not a trivial operation. One example can be as follows: for instance this 'transmission' key could be the first message sent on a conversation. For each conversation, the sender can generate and create a random symmetric key (later used to encrypt message on this conversation), then send it to the target encrypted with the target's public key (the very same one used to set up dialog security/REMOTE SERVICE BINDING). The receiver when it receives the first message it decrypts it (since it has the corresponding private key), creates a symmetric key from the received secret and later can use this symmetric key to decrypt message on this conversation. When a conversations is closed, its 'transmission' symmetric keys can be removed.

Other typical cryptographic precautions (signing/timestamping the transmission key, adding a nonce/chanlenge etc) I believe are not necessary, because you already have authentication from the dialog security itself.

HTH,
~ Remus

P.S. Kevin, you seem to know a thing or two about this subject and maybe you already knew all this, but I have to think at all the other people looking at the post.

|||

Many thanks Remus. I am intending to use a different set of keys for transmission encryption. The messages should have a much shorter lifetime that the data that gets mapped into the production tables, so I don't want to couple the data storage keys with the transmission keys.

I hadn't thought about using an initial exchange of messages in order to establish a symmetric key for encryption -- sounds just like what SSL does. Of course, I will also need to encrypt the transmission symmetric key when it is stored locally, but SQL 2k5 seems to have plenty of options there.

Cheers, Kevin