Checking a SQL Server backup and seeing that backups are failing.
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.
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
Our service account has full permission to the Shared resource.
Computer Management – System Tools – Shared Folders – (choose shared resource) – Security Tab
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.
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 “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”.
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.
Please choose to restart your DB Engine.
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.
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.