Skip to main content
Delphix

KBA1167 How to Resolve Error: "Access is Denied" During SQL Server Operations

 

Issue

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 '\\HOSTNAME\delphix-staging-directory\dlpxCopyOnlyFullBackup.dlpxbak'. Operating system error 5(Access is denied.).

Troubleshooting

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

There are five common causes of this issue:

  1. The instance owner of one SQL Server instance has been changed
  2. A Source Instance has been configured to use a Local User Account
  3. A Source Instance has been configured to use a Virtual Account
  4. A Source Instance is using the Local System or Network Service Account
  5. The Staging or VDB snapshot taken contains incorrect file permissions

Each is described further in the "Resolution" section.

Resolution

1. Instance Owner has changed

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

You can confirm the instance owner of Source and Target environments from the Delphix Engine's "Manage Environments" screen, in the "Databases" tab.

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

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 using the "Refresh Environment" button, on the lower right of the environment card:

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

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

3. 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, 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.

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

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

Additional Information

For more information on the requirements for SQL Server hosts connected to a Delphix Engine, please review the document An Overview for Setting up Delphix with SQL Server.

For more information on performing an Environment Refresh, please refer to the article Refreshing a SQL Server Environment.

For more information on the types of user with which Microsoft SQL Server can be configured to run, see Microsoft's document Configure Windows Service Accounts and Permissions.