views:

492

answers:

1

I have a Sql Server inside a restricted network. I need to somehow get data from the outside in.

I would like to harness the use of Message Broker. My thinking is the external db places a message on a queue then I require a service that sits inside of the restricted LAN to listen (poll?) for these messages and then act upon them. I cannot have the external queue initiate the normal broker conversation into the restricted LAN.

My question is should I be looking at the broker external activator to sit inside the restricted LAN and listen for new messages and then act upon them? Has anyone got any experience with this. Documentation / examples for external activator are pretty thin on the ground and monologues are not supported in message broker yet.

Is msmq a better option?

+2  A: 

My recommendation would be to allow Service Broker to deliver the message all the way into the SQL Server instance inside the restricted lan. That will require the restricted LAN to allow incomming connection (allow the inside server to listen and accept). MSMQ would be no different, the MSMQ port(s) would have to be open in the restricted LAN.

If you want to use a dedicated process inside the restricted LAN that 'gets' the data inside then you must ensure the transactional consistency between the external server 'get' and the internal server write: the two operation have to be enrolled into a distributed transaction and the DTC protocol itself needs to be allowed to penetrate into the restricted LAN. So some ports still need to be open in the restricted LAN.

What your LAN security designers need to understand is that Service Broker connections are not Transact-SQL connections. Service Broker uses a dedicated protocol that only allows exchange of Service Broker messages. All traffic is encrypted and secured with RC4 or AES encryption. SSB cryptography is FIPS compliant. Allowing for Service Broker traffic to the SQL Server inside is probably the most secure way of allowing data from the external server to reach the secured server. In Service Broker networking there is no concept of 'client' and 'server' and one cannot design the network allowing connections only in one dirrection (eg. unlike say HTTP, which can be designed to connect from inside to outside but not the other way). SSB networking requires both machines involved to be able to connect to each other, because response messages can come after long delays (hours, days, consider the case when a queue is backed up so it takes a long time until the message is processed and a response is sent). IS not feasable to keep connecitons open for days to expect a response, so the receiver of a message must be able to connect back to the sender to deliver a response.

Remus Rusanu