✎ [V7] ISSUE: Operating system error 5 (Access is denied) (MS SQL database backup) [Ahsay Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== [V7] ISSUE: Operating system error 5 (Access is denied) (MS SQL database backup) =====

<html>
 <b>
  Article ID:
 </b>
 5294
 <br/>
 <b>
  Reviewed:
 </b>
 2018-01-26
 <br/>
 <br/>
 <b>
  Product Version:
 </b>
 <br/>
 AhsayOBM: Post-7.3.0.0 
 <br/>
 OS: Windows
</html>

<html>
<FONT COLOR=red SIZE=+1>
<B>ATTENTION</B>
1st January, 2022: v7 officially End-of-Life [<A HREF=https://wiki.ahsay.com/doku.php?id=public:announcement:ahsay_v7_eol>details</A>]
</FONT>
</html>

===== Problem Description =====
<html>
When performing a MS SQL database backup (ODBC backup mode), the following warning message is received in the backup report:
 <br>
 <table border="0" cellpadding="0" cellspacing="0" width="850">
  <tr>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    No.
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Type
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0  1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Timestamp
   </td>
   <td bgcolor="#E8E8E8" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-TOP: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    Log
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    ...
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     info
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
   [Start] Backing up "SQL_hostname\MSDB\db_name" ... to "temp_path"
   </td>
  </tr>  
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     <font color=#0040FF>warn</font>
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
     <font color=#0040FF>YYYY/MM/DD
     hh:mm:ss</font>
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
   <font color=#0040FF>[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open backup device
   <br>
   'temp_path\backupset_id\...\db_name\db_name.bak.YYYY-MM-DD-hh-mm-ss.ADD'. 
   <br>
   Operating system error 5 (Access is denied.).</font>
   </td>
  </tr>
  <tr>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    *
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="top">
    ...
   </td>
   <td bgcolor="#ffffff" style="BORDER-LEFT: #C0C0C0 1px solid; BORDER-RIGHT: #C0C0C0 1px solid; BORDER-BOTTOM: #C0C0C0 1px solid" valign="middle">
    ...
   </td>
  </tr>
 </table>
</html>

===== Cause =====
The message suggests that there was a permission issue when attempting to access the temporary directory for storing backup files.



===== Resolution =====
<html>
To resolve the issue, ensure that the folder configured for the <b>Temporary directory for storing backup files</b> setting can be accessed by the SQL Server (MSDB) service:
<br>
<br>
      <img style="width:85%" src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:SQL_temp.png">
<br>
<br>
On the other hand, if the temporary directory is set to a network share, for SQL Server to access a remote disk, the SQL Server service account must have access to the network share. The availability of network drives and permissions depends on the context in which SQL Server service is running: 
<br>
<ul>
<li><font color=black>To back up to a network drive when SQL Server is running in a domain user account, the shared drive must be mapped as a network drive in the session where SQL Server is running. If you start Sqlservr.exe from the command line, SQL Server sees any network drives you have mapped in your login session.</li></font><br>

<li><font color=black>When you run Sqlservr.exe as a service, SQL Server runs in a separate session that has no relation to your login session. The session in which a service runs can have its own mapped drives, although it usually does not.</li></font><br>

<li><font color=black>You can connect with the network service account by using the computer account instead of a domain user. To enable backups from specific computers to a shared drive, grant access to the computer accounts. As long as the Sqlservr.exe process that is writing the backup has access, it is irrelevant whether the user sending the BACKUP command has access.</li></font>
<br>
Reference: <a href="https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-devices-sql-server#NetworkShare">https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-devices-sql-server#NetworkShare</a></ul>

Alternatively, you can also re-creating a new MS SQL Server backup set in VSS backup mode (supported since AhsayOBM version 7.11.0.0), which will not output the database file to the temporary directory:
<br>
<br>
      <img style="width:85%" src="http://wiki.ahsay.com/lib/exe/fetch.php?media=public:SQL_vss.png">
<br>
<br>
Note: 
<br>
There are some limitations to the VSS backup mode, refer to the Limitation and Backup Mode sections of the <a href="http://download.ahsay.com/support/document/v7/guide_obm_user_mssql.pdf">AhsayOBM Microsoft SQL Server Backup and Restore Guide</a> for details.
</html>
===== Keywords =====
sql, odbc, temp, vss, access, denied, mssql
User Tools

Site Tools

Page Tools
