December 28, 2021 at 3:41 pm
I need some assistance, please.
Now that TDE has blown up in our faces with SQL 2012, we are being pushed to use "native SQL encrypti on" on our databases to ensure the data at rest is encrypted. This while we're in the middle of a migration to new https://showbox.tools/servers.
Does anyone know what "native SQL encryption" means that makes it different from TDE? I can't seem to find it on Google.
December 28, 2021 at 6:32 pm
Heh... smells like another prelude to SPAM. For example, why would anyone in their right mind migrate a decade old version of SQL Server to brand new hardware?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2021 at 7:21 pm
I agree with Jeff here - you should be upgrading the SQL instance to something supported. 2012 is out of support. I don't quite see this as going to spam, but Jeff has been on here longer than I and may see some clues that I am missing.
Native SQL Encryption is a bit of a misnomer in my opinion as I would interpret it as "a method of encryption of SQL data that is native to SQL Server". TDE would fall under this bucket as it encrypts SQL data and is native to SQL Server.
I think before investigating alternate encryption approaches, you should first decide what you are comfortable with in terms of encryption. What I mean is if your database MDF and LDF files were obtained by a malicious actor, is there any data in that database that you would be comfortable with them seeing? If not, then TDE is going to be your best bet. If you are only looking to encrypt 1 or 2 columns, then encryption by certificate is an option with caveats. Encryption of columns REQUIRES you to plan for it before setting things up as it will result in metadata changes and changes to your SELECT statements as you need to decrypt the data and have appropriate permissions to do so. Plus I am fairly certain this is going to hurt performance a lot if you are encrypting all columns on all tables. Microsoft has a good article on encryption here - https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15. Mind you it is for SQL Server 2019 but goes back to 2016 - the currently supported versions of SQL Server.
Now my big question is - what do you mean by "TDE has blown up in our faces with SQL 2012, we are being pushed to use "native SQL encryption" on our databases to ensure the data at rest is encrypted." How did it "blow up"? TDE is designed for exactly that scenario - when you want your data encrypted at rest. Native encryption (as you call it) is going to be a pain in the butt to set up after the fact as you will need to change all of your stored procedures that push or pull data from the database to use the certificate. That is also assuming that you don't have any ad-hoc queries running against the database. Rather than re-inventing the wheel, why not fix your TDE implementation? Or if TDE is now out the window - why not ask those who are pushing you to use "native SQL encryption" to define what that means and how it is different than TDE. If you are expected to implement it, you should know what you are implementing. And I would HOPE that if you are expected to implement it, you have an SME on site who can help with it. If you are expected to implement something based entirely on buzz words, I would come back to them saying something along the lines of "In order to properly implement a supported solution, we need to upgrade to a supported platform such as SQL Server 2019. After we implement that, we can investigate a proper encryption protocol for data at rest and implement the best practice solution that is available in SQL Server 2019". Which would be TDE, but you don't let them know that is the solution. If they want to throw around buzz words, then you come back with "best practice" and chances are you will be in their good books even if you are re-implementing something that was tried before.
Plus, if you are getting new servers, I assume they are on newer Windows Server OS's. SQL Server 2012 is NOT supported on Windows Server 2019. So if you are looking at WS 2019, you will want your SQL to be at 2014 or newer at a MINIMUM if you want to remain in a supported environment. And if you are upgrading anyways, might as well go all the way (2019) if possible. If you are on Windows Server 2012, you will want to stop at SQL 2017 to remain supported and upgrade to SQL 2019 after you move to Server 2019. Shout out to Glenn Berry for this article with a great chart: https://glennsqlperformance.com/2019/12/16/operating-system-support-for-sql-server-versions/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply