January 5, 2009 at 1:09 pm
I would like to know if that feature is supported in SQL Server 2k5 (or 2k8) and if not how would you implement it.
I'm trying to figure out if it's possible to have a central server (let's name it A) which replicate with subscribers servers B and C using merge replication. (B and C does not need to communicate together)
Data on server A need to be unencrypted while these same data need to be encrypted on server B and C.
Is there a way to tell the merge agent to encrypt those data before sending on server B and C and decrypt those data that come back?
Thank you
January 5, 2009 at 3:41 pm
From BOL:
Encryption
Replication does not encrypt data stored in tables or sent over network connections. This is by design, because encryption is available at the transport level with a number of technologies, including the following industry standard technologies: Virtual Private Networks (VPN), Secure Sockets Layer (SSL), and IP Security (IPSEC). We recommend using one of these encryption methods for the connections between computers in a replication topology. ...
* Noel
January 6, 2009 at 6:29 am
Thanks but that is to prevent outside from listening on the wire while data is transmitting. It is not what I'm looking for on that issue. (Still I do concern about it but that's a different chapter of the issue)
I do want to prevent the target destination to look at the data that was transferred locally in their DB.
In other words, the central DB (Server A), you could open up SSMS and look at the data straight out of it but if you do the same thing locally (when the data was transferred to Server B or C) the same data would appear encrypted.
I was thinking more about using an asymmetric or symmetric (depending of the data load) security feature built-in SS to send an unencrypted column data from server A to an encrypted column on server B and C and vice versa where the encryption / decryption would occur on Server A. The encryption is not to prevent wire listening but end-user to look at the data before it is transmitted and after it is on their local database (server B or C).
In BOL article, they supposed that both database are secured and that only the wire is the issue which is not my case, one of the server can be compromise thus the meaning of this post (and I can't do anything about it)
January 6, 2009 at 8:29 am
Sorry for not being explicit enough.
On the First sentence of my quote from BOL says: Replication does not encrypt data stored in tables or sent over network connections. So you can't use the agents to do what you want automatically.
You can definitely symmetric key to encrypt the data and you will have to supply it to the subscribers too, but that is NOT what you asked because both publisher and subscriber will have to have encrypted data.
http://technet.microsoft.com/en-us/library/bb326115(SQL.90).aspx
* Noel
January 6, 2009 at 9:08 am
For my problem there's a solution, but I don't want to go that path. Code a merge replication engine using .Net with all the needed features. A complete manual feature (by this I mean nothing is used built-in from SQL).
I still wish to check if I could find a semi-manual solution (because a complete built-in is not available) like having an event before merging occurs that could trigger a stored procedure or having a CLR doing some stuff to get some part done.
The more I read about it, with the links you sended, the more I believe that the complete manual solution might be the only way to go :sick:
But who knows, maybe someone's got the same issue as I and had a different way to solved it.
January 6, 2009 at 12:35 pm
Totally understand your position and I feel your pain 😉
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply