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
No comments:
Post a Comment