Skip to main content
Delphix

Resolving Access is Denied Error During SQL Server Operations (KBA1167)

 

 

 

KBA

KBA#1167

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Date Release
Jul 24, 2023 13.0.0.0
Jun 21, 2023 12.0.0.0
May 25, 2023 11.0.0.0
Apr 13, 2023 10.0.0.0 | 10.0.0.1
Mar 13, 2023 | Mar 20, 2023 9.0.0.0 | 9.0.0.1
Feb 13, 2023 8.0.0.0
Jan 12, 2023 7.0.0.0
Releases Prior to 2023
Major Release All Sub Releases
6.0

6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Troubleshooting Error: Access is Denied

In some situations, dSource and virtual database (VDB) operations initiated by the Delphix Engine may fail due to file permission (ACL) errors.

When this occurs, the following errors may appear in the Failed Actions sidebar:

  • Unable to open the physical file "C:\Program Files\Delphix\DelphixConnector\vdb-directory\DATA\db\databasefilename.mdf". Operating system error 5: "5(Access is denied.)".
  • CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Delphix\DelphixConnector\vdb-directory\DATA\db\databasefilename.mdf'
  • Cannot open backup device '\\STAGINGHOSTNAME\delphix-staging-directory\dlpxCopyOnlyFullBackup.dlpxbak'. Operating system error 5 (Access is denied.).
  • Cannot open backup device '\\SOURCEHOSTNAME\SHARENAME\backupfile.bak'. Operating system error 5 (Access is denied.).

The above errors may be displayed after failure of the following operations:

  • Linking a dSource using the Delphix Managed Backups option
  • Taking a manual Snapshot, and selecting the "Copy-Only Backup" option
  • Enabling a Disabled dSource
  • Rewinding or Refreshing a VDB
  • Provisioning a VDB

These errors indicate a permissions mismatch between an environment's SQL Server Instance Owner and the file permissions (ACLs) on files or directories managed by the Delphix Engine. These errors are raised by the SQL Server process, and will be visible in both the Actions sidebar within the Management interface and the SQL Server Error Log (ERRORLOG).

Common Causes 

Possible causes of this error include:

  • NTFS or File Sharing permissions do not meet Delphix requirements
  • The instance owner of one SQL Server instance has been changed
  • The Staging Instance has been configured to Use a Local Service Account
  • A Source Instance has been configured to use a Local User Account
  • Delphix Managed Backups and Delphix-initiated Copy-Only Full Backups are not currently possible when a Virtual Service Account is used.
  • The Staging or VDB snapshot taken contains incorrect file permissions

Each of these causes is described below.

NTFS or File Sharing Permissions do not Meet Delphix Requirements 

The SQL Server Staging User Requirements describes the permissions required on backup files. Specifically, the following permissions are required to prevent "Access is Denied" error messages:

  • The Staging Instance Owner (the Windows account running the Staging SQL Server Instance) must be able to access the Windows File Share containing the source database server's backup files.
  • The Staging Instance Owner must have NTFS file permissions to open the directories containing the backup file, and the backup file itself.
  • The Delphix OS User configured for the Staging Environment must be able to access the Windows File Share containing the backup file.
  • The Delphix OS User must have NTFS file permissions to open the directories containing the backup file, and the backup file itself.

The following diagram demonstrates the required access to backup files:

clipboard_e50320a1366dae6b23eb6e286c59d358c.png

To test whether the Staging Instance Owner has been granted the necessary permissions, you can run the following query on the Staging SQL Server Instance:

RESTORE HEADERONLY FROM DISK='\\SOURCEHOSTNAME\SHARENAME\sourcebackupfilename.bak'

Instance Owner has Changed 

As part of the provisioning and refresh process, the Delphix Engine sets permissions on the database database so that they can be read by the SQL Instance Owner.

You can confirm the instance owner of the SQL Server instances in the Source and Target environments from the Delphix Engine Manage Environments screen, in the Databases tab:

accessdenied1.png

The owner shown in Delphix should match the owner of the instance as defined in SQL Server Configuration Manager or the Services control panel.

Services.png

The Delphix Engine will grant privileges to the user displayed in the Management interface - if this information is not current, the real instance owner will not have access to the files during linking or provisioning operations.

To resolve this issue, you can refresh the environment in Delphix by clicking the circular Refresh arrow icon, in the top right corner of the environment. Completing this action will cause Delphix to go out and discover any changes to the environment (new databases, ownership changes, new SQL Server instances, et cetera).

Once the environment has been successfully refreshed, you can retry the failed Action.

VDB Start operations will not attempt to reset file permissions. If a VDB Start operation fails after making the above changes, it will be necessary to Disable and Enable the affected VDB to update the file permissions.

Staging Instance Configured to Use a Local Service Account

dSource Snapshots using existing backups may fail if the Staging SQL Server Instance is running as a Local Service Account, such as Local System, Network Service, or NT Service\MSSQLSERVER.

As shown in the following diagram, both the Delphix OS User and the SQL Server instance access the Source database's backup file during a Snapshot operation.

Staging Server_Source Server.png

If the SQL Server instance owner is configured as a local service account, and attempts to access a file over the network, it will typically do so as the Local Computer Account (COMPUTERNAME$). If this does not have permissions to access the backup file, an error will be raised:

Cannot open backup device '\\SOURCEHOSTNAME\SHARENAME\backupfile.bak'. Operating system error 5 (Access is denied.).

To resolve this issue, we recommend configuring the Staging Instance to use an Active Directory user or service account, as described in the documentation section covering Staging host requirements. Microsoft's documentation for modifying the service account is available at SCM Services - Change the Service Startup Account.

Once the service account has been changed, the following additional steps will be required:

  • From the Delphix Manage > Environments menu, refresh the environment where SQL Server is residing.
  • Ensure that the new user account has permissions to read the Windows File Share.
  • Ensure that the new user account has NTFS permissions to read the backup file and its parent directories.

Alternatively, you may be able to grant read permissions of the backup share and backup file to the Active Directory Computer Account (COMPUTERNAME$). Please check to ensure that this is in line with your organizational security policies.

After making changes, you can run the following SQL query on the Staging SQL Server Instance to test whether permissions have been granted correctly, using the filename from your error message:

RESTORE HEADERONLY FROM DISK='\\SOURCEHOSTNAME\SHARENAME\sourcebackupfilename.bak'

Source Instance Configured to Use a Local User Account 

Delphix Managed Backups and Copy-Only Backups will fail if the Source Instance is running as a Local User Account (.\Username).

Copy-Only Backups performed by the Delphix Engine will need to write backup files to a network share (e.g. \\STAGINGSERVER\sharename\filename.bak). However, Local User accounts can not authenticate to other devices on the network, and attempted backups to SMB (Windows File Sharing) locations will fail:

Cannot open backup device '\\HOSTNAME\delphix-staging-directory\dlpxCopyOnlyFullBackup.dlpxbak'. Operating system error 5(Access is denied.).

To resolve this issue, reconfigure the Source Environment's SQL Server instance to use a supported user account (Domain User, Local System, Network Service, or Managed Service Account), and refresh the Source Environment from the Delphix Engine's Management interface. Once this is done, you can retry the linking operation.

If this is not feasible, consider taking a backup to a shared folder instead of performing Delphix Managed Backups.

Source Instance Configured to Use a Virtual Account 

Delphix Managed Backups and Copy-Only Backups may fail if the Source Instance is running as a Virtual Account, for example:

  • NT Service\MSSQLSERVER ; or
  • NT Service\MSSQL$INSTANCENAME

When attempting a backup to a SMB (Windows File Sharing) share, these accounts attempt to authenticate using a special Active Directory "Computer Account".

However, the Delphix Engine does not currently map Virtual Service Accounts to the computer name when granting permissions, and attempts to perform a a Copy-Only Backup will result in a permissions error:

Cannot open backup device '\\HOSTNAME\delphix-staging-directory\dlpxCopyOnlyFullBackup.dlpxbak'. Operating system error 5(Access is denied.).

To resolve this issue, upgrade the Delphix Engine to 6.0.11.0 or later.

Alternatively, the Source Environment's SQL Server instance can be modified to use a supported user account (Domain User, Local System, Network Service, or Managed Service Account). Once this is done, Refresh the Source Environment from the Delphix Engine's Management interface, and retry the linking operation.

If this is not feasible, consider taking a backup to a shared folder instead of performing Delphix Managed Backups.

A Source Instance is using the Local System or Network Service account 

Delphix Managed Backups and Copy-Only Backups may fail if both:

  • The Source Instance is running as the Local System or Network Service account; AND
  • The Staging Environment has been added using an IP address or a hostname alias

When linking a dSource using the "Delphix Managed Backups" option, or taking a snapshot using a Copy-Only Backup, the Delphix Engine creates a SMB (Windows file sharing) share on the Staging server, and initiates a backup from the dSource to this shared folder.

If the dSource's SQL Server Instance is running as the Local System or Network Service accounts, the Delphix Engine will detect this, and grant permissions on the Windows share to a special Active Directory "Computer Account".

However, the backup will still fail if the Staging server has been added to the Delphix Engine using an IP Address or alias, instead of the host's correct Fully Qualified Domain Name (FQDN). You can confirm this in the Host Address field of the Environments Page from the Delphix Engine's Management interface.

When these conditions are present, the Source Instance will attempt to connect to the Staging Server using a non-privileged Anonymous account and the connection will fail:

Cannot open backup device '\\IP ADDRESS\delphix-staging-directory\dlpxCopyOnlyFullBackup.dlpxbak'. Operating system error 5(Access is denied.).

To resolve this issue, either:

  • Modify the Host Address for the Staging Environment to use a Fully Qualified Domain Name; OR
  • Reconfigure the Source Environment's SQL Server Instance to use an Active Directory account, and Refresh the Environment from the Delphix Engine's Management interface

Alternatively, consider taking a backup to a shared folder instead of performing Delphix Managed Backups.

Database Snapshots Contain Incorrect File Permissions 

In Delphix Engine releases 4.3.5.0, 4.3.5.1, 5.0 (fixed in 5.0.4.1), and 5.1.0.0 (fixed in 5.1.4.0), issues may occur during SQL Server provisioning operations in some situations, such as:

  • The source server is running SQL Server 2005 or 2008, and the target server is a newer version than the source;
  • The target server uses a different instance name to the staging server, and you are attempting to provision a VDB;
  • You are provisioning from a snapshot taken using an earlier version of the Delphix Engine

This is the result of a timing issue, where the Delphix Engine may take a snapshot of the database files on the Staging/Pre-Provisioning server before the correct file permissions have been synchronized to disk.

This is a known issue in the product versions listed above. If you encounter this issue, please consider an upgrade to a newer version of the Delphix Engine. Delphix Engine releases 5.1.8.0 and later should resolve all known issues with file permissions.