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.
我有一个现有的SQL Server 2005数据库,其中包含使用对称密钥加密的数据。使用密码打开对称密钥。我正在升级到使用此数据库的前端应用程序,其中包括添加许多新表,存储过程,UDF等,以及对现有表和数据库对象的许多修改。为此,我正在制作现有开发数据库的副本,以便在进行新开发时可以独立支持,维护和更新当前系统。
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?
复制数据库的好方法是什么?通常,我会备份现有数据库,然后将其还原到新数据库。但是,鉴于加密数据,这是否可行?我是否仍然可以使用现有的对称密钥和密码加密并更重要的是解密新数据库中的数据?
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.
我可能想要使用DTS仅传输现有架构。在新数据库中创建新的对称密钥/密码。然后编写即席查询以传输数据,使用现有密钥/密码进行解密,并使用新数据库中的新密钥/密码进行加密。
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?
我想,对称密钥是否适用于加密/解密单个数据库或同一服务器上的许多数据库中的数据?
1 个解决方案
#1
3
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)
您指的对称密钥是数据库主密钥(DMK)。它们保存在数据库级别,因此备份/还原到另一个SQL服务器应该可以正常工作(需要注意不同的服务帐户,此线程暗示)
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
From this article:
从这篇文章:
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.
创建数据库主密钥时,将使用提供的密码对副本进行加密,并将其存储在当前数据库中。副本也使用服务主密钥加密并存储在主数据库中。 DMK的副本允许服务器自动解密DMK,这一功能称为“自动密钥管理”。如果没有自动密钥管理,则必须使用OPEN MASTER KEY语句,并在每次希望使用依赖于DMK以确保安全性的证书和密钥加密和/或解密数据时提供密码。使用自动密钥管理,不需要OPEN MASTER KEY语句和密码。
#1
3
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)
您指的对称密钥是数据库主密钥(DMK)。它们保存在数据库级别,因此备份/还原到另一个SQL服务器应该可以正常工作(需要注意不同的服务帐户,此线程暗示)
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
From this article:
从这篇文章:
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.
创建数据库主密钥时,将使用提供的密码对副本进行加密,并将其存储在当前数据库中。副本也使用服务主密钥加密并存储在主数据库中。 DMK的副本允许服务器自动解密DMK,这一功能称为“自动密钥管理”。如果没有自动密钥管理,则必须使用OPEN MASTER KEY语句,并在每次希望使用依赖于DMK以确保安全性的证书和密钥加密和/或解密数据时提供密码。使用自动密钥管理,不需要OPEN MASTER KEY语句和密码。