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?
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.