SQL Server 2005加密，asp.net和存储过程

我需要使用SQL Server 2005，asp.net和ado.net编写Web应用程序。存储在此应用程序中的许多用户数据必须进行加密(读取HIPAA)。

过去，对于需要加密的项目，我在应用程序代码中进行了加密/解密。但是，这通常是用于加密密码或信用卡信息，因此，几个表中只有少数几列。对于此应用程序，需要加密多个表中更多的列，因此我怀疑将加密职责推入数据层会更好地执行，特别是考虑到SQL Server 2005对多种加密类型的本机支持。 (否则，如果有人有真实的经验证据，我可以说服我。)

我已经咨询了BOL，并且我相当精通使用Google。因此，我不希望链接到在线文章或MSDN文档(可能我已经阅读过)。

到目前为止，我束手无策的一种方法是使用使用证书打开的对称密钥。

因此，一次设置步骤是(理论上由DBA执行)：

创建一个主密钥

将主密钥备份到文件，刻录到CD并在异地存储。

打开主密钥并创建一个证书。

将证书备份到文件中，刻录到CD并存储在异地。

使用证书使用选择的加密算法创建对称密钥。

然后，只要存储过程(或通过Management Studio的人类用户)需要访问加密的数据，就必须首先打开对称密钥，执行任何tsql语句或批处理，然后关闭对称密钥。

那么就asp.net应用程序而言，就我的应用程序代码的数据访问层而言，数据加密是完全透明的。

所以我的问题是：

我是否要打开，执行tsql语句/批处理，然后关闭所有sproc中的对称密钥？我看到的危险是，如果tsql执行出现问题，并且代码sproc执行从未到达关闭键的语句，该怎么办。我认为这意味着该键将保持打开状态，直到sql杀死在其上执行sproc的SPID。

我是否应该考虑对需要执行的任何给定过程进行三个数据库调用(仅在需要加密时)？一个数据库调用打开密钥，第二个调用执行存储过程，第三个调用关闭密钥。 (每个调用都包装在自己的try catch循环中，以最大程度地提高打开键最终关闭的几率。)

我需要使用客户端事务进行任何考虑(这意味着我的代码是客户端，并启动一个事务，执行多个sproc，然后在成功的情况下提交该事务)？

---

1)考虑在SQL 2005中使用TRY..CATCH。不幸的是，这并没有最终完成，因此您必须分别处理成功和错误情况。

2)如果(1)处理清理，则没有必要。

3)使用SQL Server的客户端和服务器事务之间并没有真正的区别。 Connection.BeginTransaction()或多或少在服务器上执行" BEGIN TRANSACTION"(和System.Transactions / TransactionScope相同，直到被提升为分布式事务)。至于在事务中多次打开/关闭密钥的问题，我不知道有什么要注意的问题。

---

我是选项3的忠实粉丝。

假一分钟，您将在以下任何地方建立事务基础结构：

如果尚未开始现有事务，只要要对数据存储进行调用，就会创建一个。

如果事务已经到位，则对数据存储的调用将挂接到该事务中。这对于通过保存/转到数据库事件引发的业务规则通常很有用。 IE浏览器如果您有一条规则，那就是每当出售窗口小部件时都需要更新WidgetAudit表，您可能希望将窗口小部件审计插入调用包装在与告知数据存储已出售窗口小部件的事务相同的事务中。

每当原始的数据存储调用者(从步骤1开始)完成时，它都会提交/回滚事务，这会影响在其调用过程中发生的所有数据库操作(使用try / catch / finally)。

一旦创建了这种类型的交易，则很容易在开始时(当交易打开时)固定打开键，并在结束时(就在交易结束之前)关闭键。对数据存储进行"调用"并没有打开数据库连接那么昂贵。确实是像SQLConnection.Open()这样的东西会刻录资源(即使ADO.NET为您池化了它们)。

如果您想获得这些类型的代码的示例，可以考虑使用NetTiers。对于我们刚刚描述的交易，它有一个非常优雅的解决方案(假设您尚未想到某些东西)。

只需2美分。祝好运。

---

您可以使用@@ error来查看在调用SQL中的sproc期间是否发生任何错误。

不复杂。

您可以但我更喜欢在SQL Server本身中使用事务。

---