I am attempting to automate TDE key backups. I created the stored procedure below and was going to schedule it to be executed as a sql agent job.
However, when I execute the procedure I get this error after about 20 seconds:
Msg 15240, Level 16, State 1, Line 3 Cannot write into file '\\FileServerName\TDEcertificateBackups\ServerName_CertificateName_CurrentDate_key.pvk'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
I have tried the following:
- Verified that the SQL Agent account has full access to the UNC path.
- UNC to the UNC path to validate it.
- File does not already exist as the folder location is empty
- Used the PRINT @SqlCommand results and ran them under my domain admin account.
- googling it :)
After reading the documentation, saving to a UNC path should work. Is this a permissions issue that I need to track down with the network/sys admins, an error with my script, or some other "gotcha" I'm not currently aware of?
Stored Procedure:
CREATE OR ALTER PROC dbo.BackupTDEcertificate
AS
SET XACT_ABORT, NOCOUNT ON
/*
This script can be used to backup the certificate and private key used for TDE.
*/
/*Declare variables*/
DECLARE @DateTime NVARCHAR(8)
SET @DateTime =
CAST(YEAR(GETDATE()) AS NVARCHAR(4))
+ CAST(MONTH(GETDATE()) AS NVARCHAR(2))
+ CAST(DAY(GETDATE()) AS NVARCHAR(2))
--SELECT @DateTime
DECLARE @CertificateName NVARCHAR(50) = (
SELECT TOP 1 CER.name [Certificate]
FROM
master.sys.certificates CER
ORDER BY
CER.start_date DESC
)--SELECT @CertificateName
DECLARE @Path NVARCHAR(150) = '\\FileServerName\TDEcertificateBackups\'
--SELECT @Path
/* Perform the backup*/
DECLARE @SqlCommand NVARCHAR(2000) =N'
USE master;
BACKUP CERTIFICATE ' + @CertificateName + '
TO FILE = N''' + @path + @@SERVERNAME + '_' + @CertificateName + '_' +@DateTime + '.cer''
WITH PRIVATE KEY (
FILE = N''' + @Path + @@SERVERNAME + '_' + @CertificateName + '_' + @DateTime + '_key.pvk'',
ENCRYPTION BY PASSWORD = ''SuperSercretPassword''
);
'
PRINT @sqlcommand --Actual procedure has this line commented out.
--exec sp_executesql @sqlcommand --Actual Procedure does not comment this line out.
