2

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

When SQL Server saves certs to disk, it alters the ACLs on the exported files - you're probably running into a permissions issue where the SQL Server Agent account cannot overwrite the file once it gets generated the first time.

See my blog post at sqlserverscience.com for details about file system security.

The key part from that post is:

After those commands have been used to export each key or certificate, SQL Server modifies the Access Control List (ACL) on each file1. This ensure no-one other than the following Windows principals have access to the file:

  • OWNER_RIGHTS has Full Control. OWNER_RIGHTS is a well-known security identifier, S-1-3-4 that represents the current owner of the object. When an Access Control Entry that carries this SID is applied to an object, the system ignores the implicit READ_CONTROL and WRITE_DAC permissions for the object owner.
  • Members of the local “Administrators” group have Full Control
  • The Virtual Account or Managed Service Account used to run SQL Server has Full Control. Note this is not the SQL Server Service Account you configure in SQL Server Configuration Manager. For a default SQL Server instance, the account is typically NT SERVICE\MSSQLSERVER. For a named instance, the account is named NT SERVICE\MSSQL$INSTANCE_NAME.
  • Inheritance is removed from the object’s ACL to prevent access by any other principal.

You also need to ensure the SQL Server service accounts have share-level permissions that enable writes.

7
  • If I'm understanding you correctly, it's possible that the sql agent account is unable to update the ACL after the file is created? I double checked and both the SQL service user and sql agent user have full control at the folder level where it's trying to create the files. Wouldn't this allow it to modify the ACL?
    – Casey
    Nov 13 '20 at 22:51
  • Do they hsve write access to the fileshare itself?
    – Hannah Vernon
    Nov 13 '20 at 23:46
  • Yes. Full control.
    – Casey
    Nov 15 '20 at 3:14
  • did you run the backup script manually in a query window in SSMS? What was the result?
    – Hannah Vernon
    Nov 15 '20 at 4:21
  • Yes. Same error message. If I attempted to save it to a local drive (e.g. c:\temp) it would work.
    – Casey
    Nov 16 '20 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.