Microsoft – SQL Server – Backup Failing with error Operating System error 5 (Access is denied)

Background

Checking a SQL Server backup and seeing that backups are failing.

 

Error

Error Message:



Error: 18204, Severity: 16, State: 1.

BackupDiskFile::CreateMedia: Backup device '\\backupServer\DBbackupProduction\model_backup_2014_04_17_094400_0249947.trn' failed to create. Operating system error 5(Access is denied.).

Error: 3041, Severity: 16, State: 1.



Error Image:
backupFailureErrorMessages

 

 

Security Audit – Backup Server – NTFS Share and Folder Permissions

The first thing I did was connect to the backup server using Computer Management.  Then accessed shared resources permissions set.

Computer Management – System Tools – Shared Folders – (choose shared resource) – Tab – Shared Permissions

ComputerManagement-SharedFolders-SharePermissions

 

Our service account has full permission to the Shared resource.

 

Computer Management – System Tools – Shared Folders – (choose shared resource) – Security Tab

ComputerManagement-SharedFolders-Security

 

Our service account has full permission to the NTFS folder.

 

Security Audit – SQL Server Agent – Log On As

I checked the SQL Server Agent over and over again and tried seeing why it will be failing.

runas

Also, investigated using runas… This will allow me to start a new session that fully impersonates our SQL Server Agent account.


C:\>runas /user:LABDOMAIN\SqlAccount  "dir \\dbBackupServer\sqlbackup"
Enter the password for LABDOMAIN\SqlAccount:
Attempting to start dir dbBackupServer\sqlbackup as user 
"LABDOMAIN\SqlAccount" ...
RUNAS ERROR: Unable to run - dir \\dbBackupServer\sqlbackup
1385: Logon failure: the user has not been granted the requested logon 
type at this computer.

 

Obviously, the “run as” failed as the account does not have Local Security Privileges “Logon locally”

The user has not been granted the requested

 

The “Logon Type” is important.  It is #2; which means “Interactive”.

But, even granting that account principal access on DB computer did not help.

 

Security Audit – SQL Server Service \ Log On As

I went back and checked the SQL Server DB itself and noticed that “SQL Server” is running as “LocalSystem”.

SQLServerConfigurationManager

I knew immediately that LocalSystem will usually not have network privileges.

And, so went ahead and changed SQL Server “Log on As” account.

Please do so using “Sql Server Configuration Manager” as doing so will allow the SQL Server Software to properly change to the new Account and make all necessary NTFS Security changes.

 

LogOnAsAccountChange

Please choose to restart your DB Engine.

Continue Reading

Logon Types Code

Please read through Randall F. Smith “Logon Type Codes Revealed” article for the best coverage of Logon Types ( http://www.windowsecurity.com/articles-tutorials/misc_network_security/Logon-Types.html )

 

Null Session Shares

If temporarily you will like to be continue to use Local System account on the DB Server and be able to access network shares on remote servers, please consider reading “How to enable null session shares on a Windows 2000-based computer”
http://support.microsoft.com/kb/289655 ).

Basically, it covers how to create a to a multi-string entry called HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares on the remote backup machine and adding each share you will like accessible by remote local system accounts.

 

Conclusion

I really had a rough time with this as I was looking to SQL Server Agent as being the instigator of backup activities.  But, really it ended up being the SQL Server Engine itself.

Please keep an eye on the “SQL Server VSS Writer” service, as well.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s