Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
niroj
Starting Member
2 Posts |
 Posted - 2012-09-17 : 07:44:21
|
| Hi All,I am trying applying AES 256 bit encryption(SYM KEY) on an existing DB on SQL server 2005.I successfully applied encryption/decryption anyhow (Thanks to google :) ). But I suffered, because I have changed around 100 tables in DB, using cursor, to change the actual column datatypes to VARBINARY(max).As you know, this impacted the existing DB procedures and triggers applied on these tables. I don't want to change the procedures again.Could anyone please suggest me applying the encryption with out changing the schema?For example, once we get the encrypted binary data, can we CONVERT/CAST them into VARCHAR and UPDATE the existing column itself(i.e. still in encrypted varchar format), and later applying varchar to varbinary format followed by actual AES decryption?Help me plzzzzzz..asp |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-17 : 23:38:05
|
| Sure, as long as your varchar columns are long enough, you can cast varbinary to varchar. Keep in mind that encrypted data takes up more space, so you may have to alter those columns anyway unless they're already varchar(max) (please tell me they're not).Also keep in mind that the SQL Server encryption functions limit the number of bytes that can be encrypted, I believe it's 8000. |
 |
|
|
niroj
Starting Member
2 Posts |
Posted - 2012-09-18 : 01:18:26
|
| Thanks Robvolk!Intially, I though it is not working,bcoz when I run "select" command on the encrypted varchar values it was showing blank on the SQL editor :) . But, once I opened it in the object explorer, It showed the encrypted format.You are right. Many cols does not have varchar(max), so I need to alter it with (max). But, can we again compact this using any other kind of encoding which may reduce the length? I am basically an AbInitio guy, and don't hv much knowledge in PLSQL.Could you suggest me on the following things?> Will altering col length impact existing constraints and procedure underlying?> How is the symmetric key and certificates are secure, since once you create and open them, anyone can decrypt them by using the same certificate?> Can we expose the encrypted table as a whole for some specific procedures(in decrypted format), by which we won't need them to be modified?Thanks in Advance... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-18 : 07:08:41
|
| Let me rephrase, I don't suggest you modify the columns to varchar(max), as you're still altering them. If you're going to alter them you might as well stick to varbinary.There's no encoding I know of to make encrypted data smaller. If you could modify your application to perform a Gzip or similar compression, and then encrypt the compressed data, you might save space. Encrypted data usually can't be compressed.Before we go further with ever-complicated alternatives, what data do you have that needs encryption? Why does it need to be encrypted? Could you perhaps use transparent data encryption (TDE) on the entire database?Encryption isn't just functions and key management. This kind of security really isn't something you can add on to an existing database, it will require changes and how it interacts with applications. For instance, doing all the encryption on the SQL Server doesn't help if someone is sniffing the unencrypted data over the internet.You should research Microsoft's encryption overview if you haven't already: http://msdn.microsoft.com/en-us/library/bb510663.aspxThere's also a book that covers the full range of keeping SQL Server secure: http://www.amazon.com/Securing-SQL-Server-Protecting-Attackers/dp/1597496251 |
|
|
|
|
|
|
|
|
AES ENCRYPTION: avoid changing schema for binary