Skip to main content
Delphix

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

 

KBA

KBA#1167

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\SH

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 

NTFS or File Sharing permissions do not meet Delphix requirements

Each cause is further described in the Resolution section.

NTFS or File Sharing Permissions do not Meet Delphix Requirements 

The document Windows Users and Permissions on Database Servers 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 Source 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 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.

accessdenied1.png

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.

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

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.