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 = ''1Cannot.Observe.Known!''
            );
'
PRINT @sqlcommand --Actual procedure has this line commented out.
--exec sp_executesql @sqlcommand --Actual Procedure does not comment this line out.

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.