在 SQL Server 2008 中，如何以一种除非连接到网络否则无法解密的方式保护数据？
最近，我们在 SQL Server 2008 中为开发人员笔记本电脑上的本地数据库实施了透明数据加密，以便在笔记本电脑被盗或丢失时保护它们。 这很好用。
现在，我们正在尝试找出一种使证书每天过期的方法，强制自动化过程（可能是登录时的脚本）进入网络路径并获取一天后过期的新证书。 这将确保如果发生不可预见的情况，数据第二天将无法使用。
我还研究过使用加密提供程序，但似乎没有任何“提供程序”。 也许我错了。
我愿意接受建议。 如果有更好的方法请告诉我。 谢谢！
We have recently implemented Transparent Data Encryption in SQL Server 2008 for local databases on our developers laptops to keep them protected in the case a laptop is stolen or lost. This works fine.
Now we are trying to figure out a way to have the certificate expire everyday, forcing an automated process (a script at logon maybe) to go out to a network path and grab a new certificate with an expiration for a day later. This would ensure that if something unforeseen happened, the data would not be usable the next day.
I also looked into using a Cryptographic provider but there doesn't appear to be any "providers" out there. Maybe I'm wrong.
I am open to suggestions. If there is a better way please let me know. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
简短回答：没有
长回答：一旦消息（数据片段）被加密，相同的密钥将解密相同的加密消息，无论何时应用解密算法。 如果密钥每天都改变，则必须使用旧密钥解密数据并使用新密钥重新加密。 如果这个过程没有发生（即有人停止执行重新加密的代码段运行），旧密钥仍然有效。 即使您确实创建了一个加密提供程序来检查日期，其他人也可以创建一个新的提供程序来执行解密，而无需先检查日期。
Short answer: No
Long answer: Once a message (piece of data) is encrypted, that same key will decrypt the same encrypted message, regardless of what time the decryption algorithm is applied. If the key is changed every day, the data must be decrypted with the old key and re-encrypted with the new. If this process doesn't occur (i.e. someone stops the piece of code that performs the re encryption from running), the old key will still work. Even if you do create a cryptographic provider to check the date, someone else can create a new provider to perform the decryption without first checking the date.
解决的是问题而不是动机。 如果您使用派生模板设置 Microsoft CA（设置为过期一天）并且还允许在该证书模板上自动注册。 然后，您可以将 SQL 计算机设置为使用自动注册的目录中 OU 的一部分（Technet 将为您提供有关这方面的资源，需要使用组策略）。 这样，当证书过期时，机器将自动请求一个新证书。
http://windowsitpro.com/article/ Articleid/40948/windows-server-2003-pki-certificate-autoenrollment.html
标记
T address the question rather than the motivation. If you set up a Microsoft CA with a derived template (Set to expire for a day) and also allow autoenrollment on that certificate template. You could then set your SQL machine to be part of a OU within the Directory that uses autoenrolment (Technet will give you resources on this requires the use of goup policy). That way when the certificate expires the machine will automagically request a new one.
http://windowsitpro.com/article/articleid/40948/windows-server-2003-pki-certificate-autoenrollment.html
Mark
不对！ 有一些选项可用于 SQL Server 2008 加密。 此处位于 TownsendSecurity.com，查看数据库加密解决方案。 Townsend 的 Alliance AES Encryption 是一种经过 NIST 认证的解决方案，可帮助您遵守有关医疗保健、信用卡和银行业务的法规。 另请参阅有关 Alliance AES Encryption 的白皮书。
完全披露：我是 Townsend Security 的实习生。
Not true! There are options available for SQL Server 2008 encryption. Check out the database encryption solutions here at TownsendSecurity.com. Townsend's Alliance AES Encryption is a NIST-certified solution that would put you into compliance with the regulations around health care, credit cards, and banking. Also see the white paper on Alliance AES Encryption.
Full disclosure: I'm an intern at Townsend Security.
如果没有更多详细信息，我无法理解您的 TDE 设置将如何保护数据以防数据丢失或被盗。
如果您没有使用全盘加密（通过 Bitlocker、Truecrypt 等），那么作为实际拥有您硬件的攻击者，我可以轻松重置本地管理员密码、启动笔记本电脑并使用本地管理员凭据访问 SQL Server 实例。 那时我是数据库服务器上的系统管理员，能够提取我想要的任何数据或关闭 TDE。
此外，由于所有加密密钥和证书都存储在本地，因此实际拥有设备的攻击者可以相对容易地访问它们。 仅当您将数据库加密密钥保护器（存储在主数据库中）与加密数据库物理分离时，TDE 对数据保护才有意义。
如果您使用全磁盘加密，那么使用 TDE 不会对攻击者提供任何额外的威慑力，只会对开发人员笔记本电脑的系统性能产生不利影响。
Without additional detail I fail to understand how your TDE setup will protect data in case it is lost or stolen.
If you are not using full disk encryption (via Bitlocker, Truecrypt, etc) then I as an attacker in physical possession of your hardware can easily reset the local admin password, boot up the laptop and access the SQL Server instance with the local admin credentials. At that point I am a sysadmin on the database server and am able to extract any data I want or to turn off TDE.
In addition since all of the encryption keys and certificates are stored locally it is relatively easy for an attacker in physical possession of the device to gain access to them. TDE is only meaningful for data protection when you physically separate the Database Encryption Key protectors (stored in the master database) from the encrypted database.
If you are using full disk encryption than the usage of TDE is not providing any additional deterrent to an attacker and is only adversely affecting system performance of your developers laptops.
你是对的——你想要的是一个加密提供程序，而且你是对的，目前还没有这样的提供程序。
如果您打算参加 11 月份的 PASS 峰会，请与 Microsoft 的 JC Cannon 交谈。 他正在举办一个关于合规性的会议，并且他是 SQL Server 合规性小组的负责人。 他与当前致力于构建加密提供商的供应商有联系，他也许可以与您讨论供应商名称。 目前他们还没有公开宣布是谁在做这件事。
You're right - what you want is a cryptographic provider, and you're right that there's none out there yet.
If you're going to the PASS Summit in November, talk to JC Cannon from Microsoft. He's doing a session on compliance, and he's the head of the SQL Server Compliance group. He's tied into the vendors that are currently working on building cryptographic providers, and he may be able to talk to you about vendor names. Right now they haven't come out publicly to announce who's doing it yet.