This is quite an old feature, however, a very useful one in SQL Server.
Applies to: SQL Server 2005 and above.
Why do we need this or its Usages?
“WITH ENCRYPTION” clause can be used for procedures and function in SQL Server.
I personally used this clause in many places, where I wanted to hide my procedure definition. When we need to manage multiple environments and different people have access to environments, its possible, the procedure definition gets changed by others. So, if we need to have to keep the same definition across all environments, we need to get the definition encrypted which would prevent the users to change the definition.
Usually, DBAs will use this option to prevent any changes or hide the logic implemented for certain purposes.Sometimes business critical logic are also encrypted using this option to hide from others.
How to Implement?
The implementation is very simple to provide the clause at the time of creation or alter.
CREATE OR ALTER procedure Test_Encryption
Select 'This is an encrypted procedure.'
Let us try to get the definition using sp_helptext, we will be getting a message “The text for object ‘Test_Encryption’ is encrypted.”
If we try to get the definition through SSMS, we will get the below error message.
And you can see a lock symbol to identify the encrypted procedures.
1. Once the procedure is encrypted, you will not be ale to see the execution plan for the procedure.
2. It is always important to save the definition safe, you will never be able to retrieve the procedure definition once its encrypted.
3. CLR procedures and CLR User Defined Functions cannot be encrypted.
4. Encrypted procedures or functions cannot be replicated.
Today’s world is more concerned on security and sensitivity of data. As the data movement is so volatile in the IT industry, there is high risk of misusing the data source without any security measures. In recent days, IT organizations have been working together closely with in-house security team to make sure the security of data has been considered.
This whitepaper presents a native Encryption technique which has been introduced in SQL Server 2008 called – Transparent Data Encryption (TDE).Transparent Data Encryption allows the encryption of the entire database while providing real time encryption of mdf and ldf files. TDE also ensure encryption on any backups taken while the encryption is enabled. TDE is fairly a straightforward concept as Data is encrypted before it is written to disk and data is decrypted when it is read from disk as it is read into the memory. Hence, there are no special changes needs to be considered on the application code or database queries. This will help in preventing unauthorized access to the data and backup files. TDE will support only in Enterprise and Developer Editions of SQL Server.
Please download the below whitepaper.
OR Github link
• Ensures Security of database at master files (mdf & ldf) and backup files.
• TDE does not increase the size of the encrypted database.
• Encrypts the Entire Database and no granular level of encryption possible.
• When one of the databases on an instance has been set for TDE, then, TEMPDB on that instance also get under the TDE enablement. This might have a performance issue on other databases as TEMPDB is common for that instance.
• The data and logs are encrypted and decrypted to memory real time and that can have performance impact.
• No protection for data in memory.
• FILESTREAM Data will not be encrypted though TDE is enabled.
• TDE does not provide encryption across communication Channels.