Skip to main content
Delphix

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
  • 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 a Delphix-presented file or directory. They are thrown by the SQL Server process, not by Delphix, and will be visible in both the Actions sidebar within Delphix and the SQL Server Error Log (ERRORLOG).

There are three common causes of this issue:

  1. The instance owner of one SQL Server instance has been changed
  2. A dSource has been configured to use a Virtual Service Account
  3. 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, Delphix 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.

Delphix will grant privileges to the user displayed in the Delphix GUI - 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. dSource configured to use a Virtual Service Account

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

However, this will fail if the dSource's SQL Server instance is owned by a local user account or Virtual Service Account, such as NT Service\MSSQLSERVER or NT Service\MSSQL$INSTANCENAME. When this occurs, the Source server will attempt to connect to the Staging server's shared folder using the Source's "Computer Account" (HOSTNAME$) and the connection 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 Active Directory account, and refresh the Source Environment from the Delphix GUI, or use a method other than Delphix Managed Backups to make backup files available to the Staging server. Once this is done, you can retry the linking operation.

3. 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 synchronised 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 5.1.5.0 adds further improvements to file permission handling, to deal with less common failure scenarios, and is a recommended minimum version.

Additional Information

For more information on the requirements for SQL Server hosts connected to Delphix, 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.