Sql-server – Timeout saving table in SQL Server

Re-encrypting the data-at-rest is going to be prohibitive. 500M records to update will generate tremendous log, a lot of data IO, will last hours if not days, overall will be quite disruptive. Not to mention that an operational error might leave the entire database perfectly encrypted (ie. w/o a key to decrypt it).

What I would recommend is to rotate the keys higher on the key hierarchy:

• Use a symmetric key to encrypt the data.
• Change the symmetric key periodically, eg. once a week generate a new one, encrypt new data with the new one but don't change old data
• encrypt the symmetric keys with a certificate
• use DECRYPTBYKEYAUTOCERT to decrypt the data, which will handle picking the right symmetric key automatically
• when required, rotate the certificate that encrypts the symmetric keys. Symmetric key support multiple certificate encryption, so it is perfectly feasible to add a new certificate, add encryption by the new certificate to all symmetric keys, then drop old certificate

Similar schemes to this are often deployed by large companies. Re-encrypting all the data is seldom done, because is so prohibitive. Using multiple symmetric keys and generating new ones every so often will reduce the amount of possible privacy loss if a symmetric key gets compromised. Rotating the certificate used to decrypt the symmetric keys gives the desired mitigation of certificate compromise as a compromised certificate will not be able to access data after the rotation, even though the data is still encrypted with the same old symmetric keys.

It is true that I can envision an attack on which if I have access to the certificate I can extract all the symmetric key material, then when the certificate rotates, I can in theory use the saved key material to decrypt the data (using means other than SQL Server). But this is no different that saying that 'if I have access to a compromised certificate before is rotated, I can decrypt all the data and save the decrypted data', and this puts the attack in a new light, since no amount of after the fact key rotation will recuperate data that is already lost to the attacker.

Solved!

Turns out the issue was a firewall one, although why it would work on the second attempt is still a mystery to me -- generally if a firewall is getting in the way, it stays in the way, it doesn't magically allow a second attempt right through...

Anyway, it turns out that the first instance was configured to listen on TCP port 1433, however the second instance was configured for "TCP Dynamic Ports" on 59196 (or something like that); what's so "dynamic" about a single port is beyond me, but whatever.

Adding a firewall rule to the Windows Firewall to allow connections to that port immediately resolved my problem. I then took it a step further, however: Instead of using this "dynamic" port, I changed it to use TCP port 1432 (1434 being used for administrative connections, or something like that), and modified the firewall rule to allow that port instead.

The port configuration can be found in SQL Server Configuration Manager --> SQL Server Network Configuration --> TCP/IP Properties --> IP Addresses; under this tab, I simply modified the settings in the IPAll section, ignoring the myriad IPn sections (which were all disabled anyway): blank the "TCP Dynamic Ports" entry, and enter 1432 for "TCP Port". Changing these settings does require that you restart your SQL Server instance.