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.

 

Microsoft – SQL Server – Error – Error: 17066, Severity: 16, State: 1 // SQL Server Assertion: File: cxrowset.cpp, line=1538 Failed Assertion = ‘pXacc–>FHasVirtualColumn()’. This error may be timing-related

We started getting this “error – Error: 17066, Severity: 16, State: 1:”

Due to its severity level 16 and SQL Server’s suggested remediation steps:

  • Run DBCC CheckDB (to check database for structural integrity)
  • Restart the server (to ensure in-memory data structures are not corrupted)

…. I am thinking I am in for long nights…and not the nice ones; actually the grumpy – I am a DBA ones…

SQL Server Error logs reads —


2013-01-03 01:22:28.12 spid59 Error: 17066, Severity: 16, State: 1.
2013-01-03 01:22:28.12 spid59 SQL Server Assertion: File: <"cxrowset.cpp">, line=1538 Failed Assertion = 'pXacc->FHasVirtualColumn()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2013-01-03 01:22:28.12 spid59 Error: 3624, Severity: 20, State: 1.
2013-01-03 01:22:28.12 spid59 A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
2013-01-03 01:22:28.13 spid65 Using 'dbghelp.dll' version '4.0.5'
2013-01-03 01:22:28.13 spid65 **Dump thread - spid = 0, EC = 0x00000009E92D64B0
2013-01-03 01:22:28.13 spid65 ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0440.txt
2013-01-03 01:22:28.13 spid65 * *******************************************************************************
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * BEGIN STACK DUMP:
2013-01-03 01:22:28.13 spid65 * 01/03/13 01:22:28 spid 65
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Location: "cxrowset.cpp":1538
2013-01-03 01:22:28.13 spid65 * Expression: pXacc->FHasVirtualColumn()
2013-01-03 01:22:28.13 spid65 * SPID: 65
2013-01-03 01:22:28.13 spid65 * Process ID: 2036
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Input Buffer 255 bytes -
2013-01-03 01:22:28.13 spid65 * 16 00 00 00 12 00 00 00 02 00 00 00 00 00 00 00 00 00
2013-01-03 01:22:28.13 spid65 * ÿÿ & 01 00 00 00 ff ff 0d 00 00 00 00 01 26 04 04 00 00 00
2013-01-03 01:22:28.13 spid65 * ç@ Ð 4Ž @ P 0 00 00 00 e7 40 1f 09 04 d0 00 34 8e 05 40 00 50 00 30
2013-01-03 01:22:28.13 spid65 * n v a r c h a r 00 20 00 6e 00 76 00 61 00 72 00 63 00 68 00 61 00 72
2013-01-03 01:22:28.13 spid65 * ( 4 0 0 0 ) , @ P 00 28 00 34 00 30 00 30 00 30 00 29 00 2c 00 40 00 50

Yes, young one, this is not an easy one.

And, you are possibly thinking “If you wanted cryptic error messages, you would have stayed with C++ and be an Old School hacker”

But, anyways as you have friends on the .Net, you have come to the right place.

Couple of things you want to keep in mind:

  1. What is your spid – In our case 65
  2. As spid is 65, quite a bit above 50 we are in user’s land; the session that is failing is not a system process, but a user process
  3. Also keep in mind the process ID, for those running multiple SQL Server Instances on the same server
  4. Then try your best to read through the InputBuffer — It contains every thing you need to tell you which SQL Statement is failing

Our problem was traced back to the day I took that proverbial short-cut:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionIDOrig as 0 persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionIDOrig]
		)

		ON [partitionScheme]([partitionIDOrig ])

)
go

If you take a quick look at our definition for partitionIDOrig, you will see it is hard-coded to be 0.

Everything worked well when we were inserting into the table.

But, once we changed to Merge, things broke.

To fix:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionID as isNull(cast(rand(100) as int), 0) persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionID]
		)

		ON [partitionScheme]([partitionID ])

)
go

Our new partitionID is an actual computed \ variant column:


    partitionID as isNull(cast(rand(100) as int), 0) persisted

 

I think when Microsoft invokes FHasVirtualColumn(), they are asking whether we have Virtual columns — And, things “stumble” a bit when the calculated column is actually a hard-coded value.


References:

MSSQL Server – Database Recovery [Manual] (after a crash) – MS SQL Server v2005 and above

 

Tried out Paul Randal’s database recovery steps (http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx)  and it works a charm —

Note that the database server will try to and most like recover itself.  But, in our case, the time for a successful recovery was too much and so we chose to ‘circumvent’ possible recovery by renaming the database log files.

Here are the steps to re-create database crash & manual recovery:

  • Create database Demo
  • Create Table
  • Generate Transactions
  • Get MS SQL Server’s Process ID
  • Kill MS SQL Server Process
  • Rename Database Log Files
  • Restart MS SQL Server Service
  • Execute Manual Restore
    • Set database to emergency mode
    • Set database to single user
    • Execute DBCC CheckDB with repair_allow_data_loss
    • Set database to multi_user

 

Create Database Demo

 


if db_id('Demo') is not null
begin
    DROP DATABASE Demo;
end
go

CREATE DATABASE Demo
ON
PRIMARY
(
  NAME = Demo_PrimaryDataFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_PrimaryDataFile.mdf'
    , SIZE = 3 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
),
(
  NAME = Demo_SecondaryDataFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_SecondaryDataFile.ndf'
    , SIZE = 1 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
),
(
  NAME = Demo_SecondaryDataFile2,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_SecondaryDataFile2.ndf'
    , SIZE = 1 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
)
LOG ON
(
  NAME = Demo_LogFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf'
    , SIZE = 2 MB
    , MAXSIZE = 15 MB
    , FILEGROWTH = 3 MB
)
,
(
  NAME = Demo_SecondaryLogFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf'
    , SIZE = 2 MB
    , MAXSIZE = 15 MB
    , FILEGROWTH = 3 MB
)
COLLATE Latin1_General_CI_AS;

 

Create Table – Session

 

use [Demo]
go

if object_id('session') is not null
begin
drop table session
end
go

create table session
(
  [id] int not null identity(1,1) primary key
, [dateAdded] datetime not null default getdate()
, [serverName] sysname not null default @@servername
)
go

 

Generate Transactions


use [demo]
go

set nocount on
go

declare @id int

set @id = 1

while (@id != 0)
begin

      insert into session default values

      set @id = @id + 1

      print @id

end
go

 

Get MS SQL Server’s System Process ID (PID)

use [master]
go

select serverproperty('processid') as processID


 

Using Task Manager kill that process (PID)

  • Launch Task Manager
  • Identify the matching Process ID
  • Kill that Process

 

 

 

Using Windows Explorer – Rename \ Move the Log files

  • Rename E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf to xDemo_LogFile.ldf

 

  • Rename E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf to xDemo_SecondaryLogFile.ldf

 

 

 

Restart MS SQL Server – And, the erroglog  will indicate that the Demo database can not be brought online


2010-09-27 22:34:28.48 spid24s     Error: 17207, Severity: 16, State: 1.

2010-09-27 22:34:28.48 spid24s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf'. Diagnose and correct the operating system error, and retry the operation.

2010-09-27 22:34:28.48 spid24s     File activation failure. The physical file name "E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf" may be incorrect.
2010-09-27 22:34:28.48 spid24s     Error: 17207, Severity: 16, State: 1.

2010-09-27 22:34:28.48 spid24s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf'. 

Diagnose and correct the operating system error, and retry the operation.
2010-09-27 22:34:28.48 spid24s     File activation failure. The physical file name "E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf" may be incorrect.

2010-09-27 23:20:26.75 spid24s     The log cannot be rebuilt because the database was not cleanly shut down.

 

To fix do the following:

  • Set database to emergency mode
  • Set database to single user
  • Execute DBCC CheckDB with repair_allow_data_loss
  • Set database to multi_user

 

 


ALTER DATABASE Demo SET EMERGENCY;
GO 

ALTER DATABASE Demo SET SINGLE_USER;
GO

DBCC CHECKDB (Demo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

ALTER DATABASE Demo SET MULTI_USER;
GO

 

 

References

  1. Creating, detaching, re-attaching, and fixing a suspect database
    http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx