必发bf88 2

情况1.
错误文本如下:
System.Configuration.ConfigurationErrorsException: Could not load file
or assembly ‘Microsoft.Web.Extensions, Version=1.0.61025.0,
Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its
dependencies. The system cannot find the file specified.
(X:\www\web.config line 29)
如图:

Transparent data encryption (TDE) is a new feature in Microsoft SQL
Server 2008 Enterprise edition and in my opinion one of the best small
features to come out of this release. It is a source of annoyance that
you have to fork out the not inconsiderable sum of money for the
Enterprise Edition in order to access this feature, but with a bit of
luck it will eventually filter through to the other versions.

必发bf88 1

The implementation and use of TDE is incredibly simple, as you would
expect from something that claims to be “Transparent”. It is also very
well documented. However, in the event that you move the encrypted
database or are required to restore it to another instance, you will be
required to remove TDE and that is not so clear cut. Failing to follow
the proper steps will result in wasted time or at worse, an
unrecoverable database!

造成这种情况的原因是由于web.config文件中有Microsoft.Web.Extensions这个文件没找到,应该是System.Web.Extensions

There are plenty of articles and blogs detailing the implementation of
TDE that can be found by a simple Google search. This article aims to
provide advice on how to remove TDE safely so that you are not left with
a load of useless bits and bytes. The knowledge in this article was
gained from painful experience…. and shouting.

情况2.
错误文本如下:
The report server cannot decrypt the symmetric key used to access
sensitive or encrypted data in a report server database. You must either
restore a backup key or delete all encrypted content. Check the
documentation for more information. (rsReportServerDisabled)
(rsRPCError) Get Online Help The report server cannot decrypt the
symmetric key used to access sensitive or encrypted data in a report
server database. You must either restore a backup key or delete all
encrypted content. Check the documentation for more information.
(rsReportServerDisabled)

T-D-What?!?!?

For those of you that don’t
know what TDE is,
it’s a method of encrypting the physical files associated with a
database. This means that it covers any backups taken from the database
in addition to data and log files. Any data stored in a database secured
with TDE is automatically encrypted when written to disk and decrypted
when read from disk. The users and clients are totally unaware of these
automated actions, hence the term “Transparent”. It is worth noting that
as well as the data files and any backups taken from an encrypted
database, Tempdb is also encrypted on that instance. This is done to
protect the transient data while it resides in TempDB. This will cause a
small overhead on other, unencrypted databases on that instance.

TDE is used to secure the physical files from loss or theft and this
protection is accomplished through an encryption key hierarchy that
exists externally from the database in which TDE has been enabled.

这个是由于密钥出错,只要在reporting
service配置工具中,加密密钥中删除旧的,然后按”更改”按钮,就可以了.
如图:
必发bf88 2

Removing TDE

So, there you are with your TDE secured database, a good set of
maintenance plans looking after the backups and most importantly, a
secure backup of the certificate used to encrypt the database. You’re
feeling pretty good about things.

Then one day you are asked to removed TDE from the database. “No
Problem” you think. You set the encryption property to ‘Off’ and then
drop the encryption key. For good measure you also delete the
certificate that was used to create the encryption key from the
instance. Voila, one unencrypted database. Happy days!

Weeks go by until one day the database server in question is rebooted.
You then find that the database which was previously encrypted will not
mount. You are faced with the error “Cannot find server certificate with
thumbprint ‘0xBB1B0816079C10A6342683443A8E576196EFBE73’.”. You then try
to restore the database from a backup and get the error “The file
“DatabaseFile” failed to initialize correctly. Examine the error logs
for more details.”

This has happened because there are still encrypted values in the log
file. In my case the header could not be read because it was still
encrypted. If you reach this point and you still have a copy of the
certificate used to encrypt the database, restore that certificate to
the Master Database and the database that was previously encrypted
should mount. If you no longer have the certificate then you can try
attaching just the mdf file which should rebuild the log file, but I
have had mixed results with this. If you only have access to a backup
file and not the certificate, then you really are stuck.

The reason for this issue can be found
on MSDN.aspx):

Because the transaction log is designed as a write-once fail safe, TDE
does not attempt to encrypt portions of the logs that are already
written to disk. Similarly, the log header cannot be re-written because
of this write-once principle so there is no guarantee that data that is
written to the log even after TDE is enabled will be encrypted. The TDE
background scan forces the log to roll over to the next VLF boundary,
which allows for the key to be stored in the header. At this point, if
the file scan is also complete, the DEK state changes to Encrypted and
all subsequent writes to the log are encrypted.

The same also holds true when the process is reversed. Just because
encryption has been turned off and the data file(s) has been decrypted,
there is no grantee that data already written to the log file will be
decrypted. This means that when the database is mounted, parts of the
log file are unreadable which will cause the database to become
unmountable.

The best solution is to make sure the log file is totally empty after
TDE has been removed. The following the steps will take a database out
of TDE and then clear the log file:

  1. Alter the database to have the ENCRYPTION option set to the value of
    OFF. This decrypts the database and can take some time if the database
    is large. If there are no other database using TDE then an unencrypted
    TempDB will be created next time the instance starts.

 

USE MASTER
GO
ALTERDATABASE{Database Name}
SET ENCRYPTION OFF
GO

 

  1. Wait until the decryption process is complete. Use the
    sys.dm_database_encryption_keys DMV to determine its status. A value
    of “1” returned in the encryption_status column indicates that the
    decryption is complete.

  2. Drop the database encryption key for the database.

    USE{Database Name}
    GO
    DROPDATABASE ENCRYPTION KEY
    GO

  3. Truncate the database log file.  This will remove all of the data
    contained within the log file, including any data that is still
    encrypted.

  4. Set the database recovery mode to simple and then shrink the log file
    of the database. This removes any encrypted headers that are in the
    database. Once this has been done, the recovery mode can be set to Full
    if required. This step will cause the header of the log file to be
    rewritten, this is important as the header may still be encrypted even
    after TDE is removed. Note: If you switch back from Simple to Full
    logging, you should take a full back up immediately to re-establish the
    log chain.

  5. Restart the instance in which the database resides. If there are not
    any other user databases on the instance that have TDE implemented, this
    action will force the recreation of the TempDB database in an
    unencrypted format.

Summary

This article has outlined some of the basic functions of Transparent
Data Encryption, it has also given a number of scenarios where even
though TDE has been switched off, aspects of that database remain
encrypted and explained the potential consequences of this. Finally,
this article has documented the proper steps that should be taken in
order to ensure the all encrypted data has been decrypted or removed
after TDE has been switched off.

admin

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注