基于SQL的加密性能 [英] SQL based encryption performance

问题描述

我正在为未来的项目进行一些研究，并且需要对一些数据进行加密。

在今天的研究中，我注意到有许多不同的加密算法（包括AES，Triple-DES，X-DES等），我希望使用SQL Server中的算法（2008r2 express probs），你会如何推荐这样做，比如最佳实践等？

我打算做以下事情： / p>

   - 创建主密钥。 
 CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Som3Rand0m！3y？na'
  - 创建证书。 
 CREATE CERTIFICATE someCert WITH SUBJECT ='c3p009xFR？'
  - 创建对称键
 CREATE SYMMETRIC KEY someSymmetricKey WITH ALGORITHM = TRIPLE_DES加密证书someCert 
  

使用方法如下：

 声明@sql varchar（8000）
 set @sql ='OPEN SYMMETRIC KEY someSymmetricKey DECRYPTION BY CERTIFICATE someCert'
 exec（@sql）
 
  - 检查表
 select col1，Convert（varchar（max），DECRYPTBYKEY（col2））as col2 
 FROM myTable 
 
  - 不要忘记再次关闭对称密钥。 
 CLOSE SYMMETRIC KEY someSymmetricKey 
  

我想这是正确的方法（我读还有一个例子，只是双重检查：]）？

另外，真的有任何性能差异（或安全差异，就像一个更容易打破比另一个）之间使用不同的加密算法？

最后，如果我将数据库移动到不同的服务器，我猜这个我真的很想找到/最重要的我可以用相同的方法重新创建密钥/证书，我将能够获取数据等等。

任何好的博客文章/链接/白皮书将感谢您阅读：D

>解决方案

您只有两个选项：AES和3DES。简单的DES和XDES太弱（分别为56和112位强度）。 RC4不是一个选项，因为SQL Server实现被破坏（没有正确的加密值）。

3DES坚持过去。使用AES，是目前的NIST 推荐算法，并为您提供体面的速度。

I am doing some research for a future project and it requires some data to be encrypted.

In my research today, I have noticed that there are many different encryption algorithms (including AES, Triple-DES, X-DES etc) and I wish to use one of the algorithms in SQL-Server (2008r2 express probs), how would you recommend doing this, like best practices etc?

I am planning to do something like the following:

-- Create the master key. 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Som3Rand0m!3y?na'
-- Create the cert.
CREATE CERTIFICATE someCert WITH SUBJECT = 'c3p009xFR?'
-- Create Symmetric Key
CREATE SYMMETRIC KEY someSymmetricKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE someCert 

And use it in the following way:

declare @sql varchar(8000)
set @sql = 'OPEN SYMMETRIC KEY someSymmetricKey DECRYPTION BY CERTIFICATE someCert '
exec (@sql)

-- Check the table
SELECT      col1, Convert(varchar(max), DECRYPTBYKEY(col2)) as col2
FROM        myTable

-- dont forget to close the symmetric key again afterwards.
CLOSE SYMMETRIC KEY someSymmetricKey

I guess this is the correct way to do it (I read up on it ages ago and saved an example, so just double checking :])?

Also, are there really any performance differences (or security differences, like one is easier to break than another) between using the different encryption algorithms? <<- this is the one i was really trying to find out about/most important.

Lastly, if I move the database to a different server, I guess I can just re-create the key/cert in the same way and I will be able to get the data back etc?

Any good blog posts/links/whitepapers would be greatly appreciated :) (most that ive read have been quiet bad tbh)

Thanks for reading :D

解决方案

You have only two options: AES and 3DES. Simple DES and XDES are too weak (56 and 112 bit strength respectively). RC4 is not an option because the SQL Server implementaion is busted (does no properly salt encrypted values).

3DES is clinging to the past. Use AES, is the current NIST recommended algorithm and offers you a decent speed.

这篇关于基于SQL的加密性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！