將密碼加密的證書重新創建到另一個數據庫

我創建了一個儲存過程，它需要跨多個數據庫的權限：

USE MyDB;

CREATE PROCEDURE dbo.MyProc
(
   @ScheduleName NVARCHAR(260),
   @KeepSchedule BIT = 0
)
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @ScheduleId UNIQUEIDENTIFIER;

   -- get the schedule id so we can delete
   SELECT  @ScheduleId = s.ScheduleId
   FROM    ReportServer.dbo.Schedule s
   WHERE   s.[Name] = @ScheduleName AND
           s.EventType = 'SharedSchedule';

   IF @ScheduleID IS NOT NULL
   BEGIN
       BEGIN TRY
           BEGIN TRAN
               -- delete the schedule
               IF @KeepSchedule = 0
               BEGIN
                   EXEC ReportServer.dbo.DeleteTask @ScheduleID=@ScheduleID;
               END;

               -- delete from our local table
               DELETE FROM MyDB.dbo.MyTable WHERE MyColumn = @ScheduleName;
           COMMIT
       END TRY
       BEGIN CATCH
           -- rethrow the error
           DECLARE @ErrorNumber INT = ERROR_NUMBER();
           DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

           DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5));
           DECLARE @ErrorState INT = ERROR_STATE();

           RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

           IF @@TRANCOUNT > 0
           BEGIN
               ROLLBACK;
           END;
       END CATCH;
   END
   ELSE
   BEGIN
       RAISERROR (N'There was an error finding the schedule. Does this schedule exist?', 11, 1);
   END;
END;
GO

我想授予使用者執行此儲存過程的權限，但因為它訪問兩個數據庫中的對象：

• 數據庫
• 報表伺服器

我無法利用所有權連結，因此我希望使用程式碼簽名技術來授予權限。

我創建瞭如下證書：

USE MyDb;
CREATE CERTIFICATE [CodeSigningCertificate]
ENCRYPTION BY PASSWORD = 'MyStrongPassword'
WITH EXPIRY_DATE = '2099-01-01',
SUBJECT = 'Code Signing Cert';

-- copy the cert to report server database
DECLARE @Cert NVARCHAR(4000) =
        CONVERT(NVARCHAR(4000),
                CERTENCODED(CERT_ID(N'CodeSigningCertificate')),
                1);

EXEC (N'USE [ReportServer];
CREATE CERTIFICATE [CodeSigningCertificate]
FROM BINARY = ' + @Cert);
GO

-- create the code signing user for ReportServer and grant the perms
USE ReportServer;
CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate]; 

然後我簽署儲存過程並為程式碼簽名使用者提供儲存過程中所需的相關權限：

-- sign stored proc
USE MyDb;
ADD SIGNATURE TO dbo.MyProc
BY CERTIFICATE [CodeSigningCertificate]
WITH PASSWORD = 'MyStrongPassword';

-- give the code signing login the permissions it needs to run the procedure successfully
USE ReportServer;
GRANT SELECT ON dbo.Schedule TO CodeSigningUser;
GRANT EXECUTE ON dbo.DeleteTask TO CodeSigningUser;

最後，我授予使用者執行權限並以該使用者身份執行：

GRANT EXECUTE ON dbo.MyProc TO [Domain\MyUser];

EXECUTE AS LOGIN = 'Domain\MyUser';
   EXECUTE dbo.MyProc @ScheduleName = 'Test'; 
REVERT;

我得到錯誤

Msg 50000, Level 14, State 5, Procedure MyProc, Line 41 [Batch Start Line 111]
The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'. Line 1

拋出此錯誤是因為ReportServer.dbo.DeleteTask從具有刪除觸發器的表中刪除：

CREATE PROCEDURE [dbo].[DeleteTask]
@ScheduleID uniqueidentifier
AS
SET NOCOUNT OFF
-- Delete the task with the given task id
DELETE FROM Schedule
WHERE [ScheduleID] = @ScheduleID
GO

計劃表有一個刪除後觸發器：

CREATE TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule]  
AFTER DELETE
AS 
DECLARE id_cursor CURSOR
FOR
   SELECT ScheduleID from deleted
OPEN id_cursor

DECLARE @next_id uniqueidentifier
FETCH NEXT FROM id_cursor INTO @next_id
WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set.
BEGIN
   if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing.
   BEGIN
       exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule
   END
   FETCH NEXT FROM id_cursor INTO @next_id
END
CLOSE id_cursor
DEALLOCATE id_cursor
GO

ALTER TABLE [dbo].[Schedule] ENABLE TRIGGER [Schedule_DeleteAgentJob]
GO

為了解決這個問題，我在 msdb 中創建了一個證書和 CodeSigningUser：

-- copy the cert to msdb database
USE MyDb;
DECLARE @Cert NVARCHAR(4000) =
        CONVERT(NVARCHAR(4000),
                CERTENCODED(CERT_ID(N'CodeSigningCertificate')),
                1);

EXEC (N'USE [msdb];
CREATE CERTIFICATE [CodeSigningCertificate]
FROM BINARY = ' + @Cert);

-- create the code signing user for msdb and grant the perms 
USE msdb;
CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate]; 

GRANT EXECUTE ON dbo.sp_delete_job TO CodeSigningUser

如果我再次執行該程序：

EXECUTE AS LOGIN = 'Domain\MyUser';
   EXECUTE dbo.MyProc @ScheduleName = 'Test'; 
REVERT;

我犯了同樣的錯誤。

這個問題表明問題在於觸發器是另一個模組，證書權限不能傳遞給鏈中的其他模組，解決方案是向觸發器添加一個反簽名：

USE [ReportServer];
ADD COUNTER SIGNATURE
   TO dbo.[Schedule_DeleteAgentJob]
   BY CERTIFICATE [CodeSigningCertificate]
   WITH PASSWORD = 'MyStrongPassword';
GO

但我得到了錯誤

Msg 15556, Level 16, State 1, Line 134
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

如果我跑

USE ReportServer;
SELECT pvt_key_encryption_type_desc FROM sys.certificates WHERE [name] = 'CodeSigningCertificate'

我看到 NO_PRIVATE_KEY

和

USE MyDb;
SELECT pvt_key_encryption_type_desc FROM sys.certificates WHERE [name] = 'CodeSigningCertificate'

顯示 ENCRYPTED_BY_PASSWORD

如何複製使用密碼創建的 CodeSigning 證書，以便它也被密碼加密，使其能夠副簽觸發器？

我設法做到了。這是程式碼：

Use MyDB;

-- copy the certficates to the other databases
DECLARE @PublicKey VARBINARY(MAX)
DECLARE @PrivateKey VARBINARY(MAX)

SELECT  @PublicKey = CERTENCODED(CERT_ID(N'CodeSigningCertificate')),
       @PrivateKey = CERTPRIVATEKEY(CERT_ID(N'CodeSigningCertificate'), N'######',N'######');

DECLARE @CreateCertSQL NVARCHAR(MAX);
SELECT @CreateCertSQL =  N'CREATE CERTIFICATE CodeSigningCertificate 
                   FROM BINARY = '+ CONVERT(NVARCHAR(MAX), @PublicKey, 1) + '
                   WITH PRIVATE KEY (BINARY = ' + CONVERT(NVARCHAR(MAX), @PrivateKey, 1) + ',
                       DECRYPTION BY PASSWORD = N''######'',
                       ENCRYPTION BY PASSWORD = N''######'');'

DECLARE @SQLToRun NVARCHAR(MAX);

SET @SQLToRun = N'USE [msdb]; ' + @CreateCertSQL;
EXEC sys.sp_executesql @command = @SQLToRun;

SET @SQLToRun = N'USE [ReportServer]; ' + @CreateCertSQL;
EXEC sys.sp_executesql @command = @SQLToRun;

引用自：https://dba.stackexchange.com/questions/265252