如何在SQL Server 2005数据库之间传输sql加密数据？ [英] How to transfer sql encrypted data between SQL Server 2005 databases?

问题描述

我有一个现有的SQL Server 2005数据库，其中包含使用对称密钥加密的数据。使用密码打开对称密钥。我正在升级到使用此数据库的前端应用程序，其中包括添加数十个新表，存储过程，UDF等，并对现有表和数据库对象进行了数十项修改。为此，我正在制作现有开发数据库的副本，以便在新的开发过程中，当前系统可以独立支持，维护和更新。

I have an existing SQL Server 2005 database that contains data encrypted using a Symmetric key. The symmetric key is opened using a password. I am working on an upgrade to the front end applications that use this database, which include adding dozens of new tables, stored procedures, UDFs, etc. and dozens of modifications to existing tables and database objects. To that end I am making a copy of the existing development database, so that the current system can be independently supported, maintained, and updated while new development takes place.

什么是复制数据库的好方法吗？通常，我将对现有数据库进行备份，然后将其还原到新数据库。但是，鉴于加密数据，这可能是可行的吗？我仍然可以使用现有的对称密钥和密码加密和更重要地解密新数据库中的数据？

What is a good way to go about copying the database? Normally, I'd take a backup of the existing database, and then restore it to the new database. However, will this be feasible given the encrypted data? Will I still be able to encrypt and more importantly decrypt data in the new database using the existing symmetric key and password?

可能我只想使用DTS来传输现有的模式。在新数据库中创建一个新的对称密钥/密码。然后写入特殊查询来传输数据，使用现有密钥/密码进行解密，并在新数据库中使用新密钥/密码进行加密。

Might I instead want to use DTS to transfer the existing schema only. Create a new symmetric key/password in the new database. Then write ad hoc queries to transfer the data, decrypting using existing key/password, and encrypting using new key/password in new database.

我猜这个核心在于，对称密钥是否有利于在单个数据库或同一服务器上的许多数据库中加密/解密数据？

I guess at the heart of this is, are symmetric keys good for encrypting/decrypting data in a single database or in many databases on the same server?

推荐答案

您所指的对称密钥是数据库主密钥（DMK）。它们保留在数据库级别，因此对另一个SQL Server的备份/还原应该可以正常工作（不同服务帐户的注意事项，这是线程暗示）

The Symmetric keys you are referring to are Database Master Keys (DMKs). They are held at the Database level, so a backup/restore to another SQL server should work OK (with the caveat of differing service accounts, which this thread alludes to)

在做任何事情之前，请确保你有一个钥匙的备份（大概你已经这样做了）：

Before you do anything make sure you have a backup of your keys (presumably you've already done this):

USE myDB
GO
BACKUP MASTER KEY TO FILE = 'path_to_file'
    ENCRYPTION BY PASSWORD = 'password'
GO

从此文章< a>：

From this article:

创建数据库主密钥时，
a副本将使用附带的
密码进行加密并存储在当前的
数据库中。副本也加密
与服务主密钥，并在主数据库中存储
。
的副本，DMK允许服务器
自动解密DMK，一个
功能被称为自动密钥
管理。没有自动密钥
管理，您必须使用OPEN
MASTER KEY语句，并在每次您要
时使用
证书加密和/或解密数据时提供
密码和依赖
DMK的密钥来安全。使用自动密钥
管理，OPEN MASTER KEY
语句和密码不需要
。

When you create a Database Master Key, a copy is encrypted with the supplied password and stored in the current database. A copy is also encrypted with the Service Master Key and stored in the master database. The copy of the DMK allows the server to automatically decrypt the DMK, a feature known as "automatic key management." Without automatic key management, you must use the OPEN MASTER KEY statement and supply a password every time you wish to encrypt and/or decrypt data using certificates and keys that rely on the DMK for security. With automatic key management, the OPEN MASTER KEY statement and password are not required.

这篇关于如何在SQL Server 2005数据库之间传输sql加密数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！