SQL Database Replication - Does it meet requirement?

[El_Luke]

Assume you know that cardholder data is being replicated from one MS SQL database to another and you know there isn't any sort of encryption configured. However, it is my understanding that SQL replication is not a clear text transfer of data. Sniffing the traffic supports that, the data is unreadable so it seems that SQL replication does some form of packet encoding or whatnot.

I assume its possible to capture all that traffic and decode it. Am I correct in that assumption, is it possible to decode SQL replicated data to view it cleartext?

Of course this all relates back to whether the data is being transmitted securely or not. If the data cannot be decoded and viewed, it would be ok even though its not encrypted, right?

Thanks

[jbhall56]

First things first.

It is my understanding that encryption of the SQL Server replication traffic is not by default. You have to implement a symetric key structure ala SSL or similar in order for this to occur. So, what you are seeing is by design, not by default. So another SQL Server replication implementation may or may not be similarly configured, even at the same location/client.

The next piece of the equation is related to the fact that the data is not encrypted at rest. You need to follow Appendices B & C of the PCI Security Audit Procedures v1.1 regarding the compensating controls that will have to be put in place to meet requirement 3.4.

[El_Luke]

SQL replication may not encrypted, but looking at the sniffed traffic, it doesn't appear to be clear text either. Perhaps the SQL replication process encodes the data somehow? Can anyone answer that definitively? Should I or should I not be able to view cleartext data when sniffing SQL replication traffic?

If it is "encoded" by some means or something similar, is there a way to decode it just using packet dumps. Not that I necessarily want to know exactly how to do it, rather, just whether it is possible. If its not possible, that would seem to satisfy the requirement for encryption.

[jbhall56]

Here is the definitive answer from Microsoft TechNet
http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx

"The encryption features in SQL Server 2005 are designed to encrypt data at rest. Data that is stored in the Digital Asset Store would be encrypted. Data transfer between applications and the Digital Asset Store would be performed by the passing of decrypted (clear text) data through an encrypted tunnel. Microsoft IT determined this approach to be a recommended best practice for transferring data between databases. In this approach, encryption keys are not shared between systems. Additionally, the data at rest is encrypted by means of the encryption framework that is present in each system. Figure 11 illustrates this data transfer."

Like I stated in my original post, it's much like SSL. However, you are not going to be able to readily access the keys.

[El_Luke]

SQL 2005 yes. But does SQL 2000 share the same abilities? I don't think so but I cannot verify that answer one way or the other. So I am still left wondering whether SQL 2000 replication is secured/encrypted by default or can even has the ability to be encrypted.

[jbhall56]

From MSDN:

"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. For more information, see Encrypting Connections to SQL Server."

Sounds to me like the situation you describe is using an encrypted tunnel to replicate the data. Since it's inside the netowrk, I would bet it's an SSL tunnel as that would be the easiest.

[lyalc]

Looks like there's a siple way to check.
With Wireshark, sniff traffic between 2 SQL boxes that have replication enabled and active.
If wireshark can decode the protocol, then its not encrypted.
The sensitive data is there, but encoded as binary (e.g. packed in nibbles etc) or unicode rather than ascii, then its not encrypted.
If tere are not key establishment settings on either machine for the replication service, then its not encrypted, or uses a default key common to every instance of SQL -> not good key management, not good crypto implementation.

I don't know the product, so I'm interested in the results, should someone have the time and facilities necessary to test this.

Lyal

[mdahn]

I think it should be noted that cardholder data only needs to be encrypted when it is written to disk or traverses a public (Internet or wireless) network. If the database replication is done over an internal or point-to-point network it does not need to be encrypted in transit.