我在单个 SqlServer 实例中有 2 个数据库。我需要使用ServiceBroker服务将消息从一个数据库发送到另一个数据库。如何在不将数据库上的 TRUSTED 设置为"ON"的情况下完成此操作,因为在我的情况下,由于安全风险,这不是选项?
必须使用证书设置对话安全性,请参阅对话框安全性证书:
- 在涉及的两个数据库上创建数据库主密钥
-
为发起方服务所有者(可能是 DBO)创建和导出证书
USE <initiatordb>;
CREATE CERTIFICATE [<initiatorservicename>] AUTHORIZATION [<initiatorserviceowner>] WITH SUBJECT = '<initiatorservicename>';
BACKUP CERTIFICATE [<initiatorservicename>] TO FILE = 'c:temp<initiatorservicename>.cer'
; -
对目标服务执行相同的操作:
USE <targetdb>;
CREATE CERTIFICATE [<targetservicename>] AUTHORIZATION [<targetserviceowner>] WITH SUBJECT = '<targetservicename>';
BACKUP CERTIFICATE [<targetservicename>] TO FILE = 'c:temp<targetservicename>.cer'
; -
在目标数据库中导入发起方服务所有者的证书,为其创建代理用户并授予 SEND 权限:
USE <targetdb>;
CREATE USER [<initiatorserviceproxyuser>] WITHOUT LOGIN;
CREATE CERTIFICATE [<initiatorservicename>] AUTHORIZATION [<initiatorserviceproxyuser>] FROM FILE='c:temp<initiatorservicename>.cer'
GRANT SEND ON SERVICE::[<targetservicename>] TO [<initiatorserviceproxyuser>];
-
在发起方数据库中导入目标服务所有者的证书,为其创建代理用户并映射远程服务绑定:
USE <initiatordb>;
CREATE USER [<targetserviceproxyuser>] WITHOUT LOGIN;
CREATE CERTIFICATE [<targetservicename>] AUTHORIZATION [<targetserviceproxyuser>] FROM FILE='c:temp<targetservicename>.cer'
CREATE REMOTE SERVICE BINDING [<targetservicename>] WITH SERVICE_NAME = '<targetservicename>', USER = [<targetserviceproxyuser>];
我从记忆中键入了所有这些内容,我希望我的语法正确。