September 8, 2017 at 11:47 am
Starting a project that requires data in the cloud and the boss wants the replicated DB to be encrypted but the source DB is not. We will be replicating from SQL server 2014 to SQL Server 2016 hosted in AWS. Any issues that I should be aware of? This will be providing data to a public facing website.
September 10, 2017 at 3:21 am
Encrypted how? Using TDE I think it would be possible, but I do not think encrypting the row data itself would be possible without changes to the source database.
Biggest consideration is probably right sizing the data/log files if you are using TDE. TDE will effectively disable instant file initialization, so any file growths you pay for up front because a query will wait for the file initialization operation to complete before writing to disk. So for log activity this could kill performance. Ensuring your log file is grown up front should alleviate this.
Joie Andrew
"Since 1982"
September 11, 2017 at 8:51 am
TDE should work on the subscriber. The data comes over unencrypted, gets encrypted when written to disk. As mentioned, be aware of sizing.
September 11, 2017 at 8:56 am
Thank you both for the feedback. It will be on the subscriber only and was thinking or hoping that maybe the traffic would be encrypted. You have given me a starting point though.
September 11, 2017 at 12:22 pm
Traffic isn't encrypted by default. If you used Always Encrypted, it would be, but I wouldn't do that. TDE doesn't encrypt traffic either.
You could use SSL, but really, set a VPN or IPSec between hosts. It's an easier, and more solid method of encryption and ensuring all traffic is encrypted. Use TDE in AWS because is makes everyone feel better and most auditors accept it as encryption.
September 11, 2017 at 12:27 pm
Thank you Steve Jones. No I have a solid starting point. The network security guy wont let me set up a VPN tunnel as it would be hard to detect an intrusion if it were to happen.
September 11, 2017 at 12:59 pm
Setup SSL like Steve mentioned ensuring that the ForceEncryption option is set to yes on the publisher and subscriber, and use PKI-issued certs if you can.
Last year Microsoft introduced official support for TLS 1.2 for many SQL Versions, so you can enforce the newer standard of TLS/SSL encryption on your SQL Server connections to keep data-in-transit encrypted.
Also, like Steve mentioned there is the option of IPSec. I am not sure how either of these options are any different than VPN as far as your network administrators are concerned though. It is still encrypted traffic, and unless they are doing some kind of corporate man-in-the-middle where they break and re-establish encrypted connections in order to do packet analysis the traffic is all going to be noise for them.
Joie Andrew
"Since 1982"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply