@ZEAL Blog·厉
WWW Zeal Blog
We stand alone,
TOGETHER.

MSSQL错误15466:解密过程中出错

Posted by zeal on 2010-04-11 01:11 , 1970 characters , Via |  + 0 - 0   English
转载请保留本行原始出处声明信息 : http://www.zeali.net/entry/646 MaDe1nZEAL
标签 ( 开发/理论 ):  , 

问题起因:

服务器切换,直接把 MS SQL Server 2005 所有库的mdf文件复制到新机器上挂载。重新启动新服务器的数据库服务之后其它一切正常,但数据库邮件无法正常发送,尝试重新配置数据库邮件的smtp,结果在重新输入smtp验证密码后保存时报T-SQL错误:解密过程中出错,错误号 15466 。

解决方法:

在原来的数据库服务器上新建查询,执行T-SQL脚本:

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
    ENCRYPTION BY PASSWORD = 'AnyPasswordULike'

将SMK导出来之后复制到新的服务器上,然后执行:

RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'
    DECRYPTION BY PASSWORD = 'ThePasswordWhenUBackup' FORCE

注意在restore的时候必须使用 FORCE 选项,否则无法强制刷新SMK。这样restore之后,那些保存在数据库里面需要进行加解密(通常是密码)的信息就可以被正常读取了。

如果你知道那些跟service相关的配置密码的话,也可以不通过backup&restore的方式,而是直接强制重新生成一个随机SMK:

ALTER SERVICE MASTER KEY FORCE REGENERATE

然后重新再去配置一遍(比如数据库邮件帐号信息)就行了。

SMK的详细解释:SQL Server 2005: A look at the master keys , and Part 2

Service Master Key (SMK)

This key is used to encrypt all database master keys as well as all server-level secrets such as credential secrets or linked server login passwords. The key itself is a 128bit 3DES key. The 3DES algorithm was chosen because of its availability on all Windows platforms supported by SQL Server 2005. The SMK is encrypted using DPAPI and the service account credentials. A second encryption of the SMK will be added in future builds.

Database Master Keys (DMK)

There can be only one such key per each database. Like the SMK, each DMK is a 128bit 3DES key. The DMKs are encrypted using a password and by default an additional encryption by the SMK is also made. A DMK is used to protect database level secrets such as the private keys of certificates or asymmetric keys. The purpose of the encryption by the SMK is to allow the server to be able to internally decrypt the DMK without requiring a password from the user. If this is not desired (because every sysadmin would have access to the DMK), the SMK encryption can be removed, but then the DMK can only be opened by specifying the password that was used to encrypt it.

Last Modified on 2010-04-11 02:48
没有评论.
日志存档
2014年
2013年
2012年
2011年
2010年
2009年
2008年
2007年
2006年
本 Blog 原创内容
遵循以下授权:

Creative Commons
(创作共用) CC

署名-非商业性
-相同方式分享
ZEAL Blog 基于
Powered byPivot - 1.24.1: 'Arcee'
Pivot1.24.1
开发
系统部署于
Linode.com
Launched @
2005-04-27
Since 2005.04.27  梦想 就像鸡蛋 要么孵化 要么臭掉RSS Feed (Entries) | About me | Back To Home | @ZEAL | zbird.com | 沪ICP备05024379号