March 8, 2017 at 8:43 pm
Hello
I have a requirement from a client that requires all SQL connections be encrypted. We cannot afford Enterprise Edition to make use of TDE. With Standard Edition, my options are limited to SSL encryption. With SSL encryption, I know that the client-server connections are encrypted, but how can I make sure the connection from SQL to disk is encrypted? What type of encryption will let me enable that? I am not looking for encrypting the disks here, but trying to understand how can I make sure that the connections between the SQL Instance and the disks are encrypted?
Thanks
March 8, 2017 at 9:15 pm
You are using terms that aren't really real. Between SQL and the disks. It is really about the Disks and Buffer Pool (reading from disks into memory) and there is nothing that does that. Even TDE is "At Rest" Encryption, so when the data is read from the disks into Buffer Pool, the data is decrypted and lives in RAM unencrypted. So the only way to do this is to encrypted your data in the columns. You can do this by Certificate or Symmetric Key. But this is a schema change and an application change thing, it is not free of changes to the schema, etc.
So I guess it depends on what your intent is, if you want it encrypted between the client and in buffer pool and disk then you can use SQL 2016 SP1 and use Always Encrypted technology. This lets you encrypt and decrypt at the client driver layer. Take a look : https://msdn.microsoft.com/en-us/library/mt163865.aspx
If you don't have SQL 2016 then there are not many ways you can accomplish what you want that I know of.
March 8, 2017 at 9:24 pm
Since SQL Server 2016 SP1, the Always Encrypted feature is included in express editions even. It does not provide whole database encryption like TDE. There are other nice alternatives like DbDefence and NetLib Encryptionizer.
March 9, 2017 at 12:34 pm
Satya Krishna - Wednesday, March 8, 2017 8:43 PMI have a requirement from a client that requires all SQL connections be encrypted.
That's got nothing to do with TDE, what exactly is your requirement
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 9, 2017 at 5:25 pm
Data goes as follows:
Client <-> SQL - "data in motion" - to encrypt, have data encrypted before it's sent to SQL or use SQL Server Configuration Manager, SQL Server Network Configuration, right-click Protocols for MSSQLSERVER, Properties, Force Encryption to Yes, and on Certificate tab choose a cert - I use OpenSSL to generate certs. It's not trivial to set up the first time, the FQDN must be exact and certain settings set. Very simple once you can generate certs, see if it's doing anything with
select session_id, encrypt_option from sys.dm_exec_connections
though figuring out which algorithm is encrypting a particular connection requires using a packet sniffer like Microsoft Message Analyzer https://www.microsoft.com/en-us/download/details.aspx?id=44226 - and a different client might use a different algorithm, those are controlled in the registry and group policy.
SQL <-> OS - to encrypt, you need to have data encrypted in SQL or before it gets to SQL.
OS <-> drive controller/HBA - to encrypt, you need to have data encrypted in SQL or before it gets to SQL.
drive controller/HBA <-> storage controller - to encrypt, you need to have data encrypted in SQL or before it gets to SQL or be using some sort of controller with built-in encryption, if any exist
storage controller <-> storage media - to encrypt, you need to have data encrypted in SQL or before it gets to SQL or if you have self-encrypting drives, that can be used here. Beware key control - if changing the key doesn't either take hours on a full drive or wipe out all your data, then you aren't changing the actual encryption key.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply