TroubleshootingSQL

Explaining the bits and bytes of SQL Server

Archive for the ‘Backup’ Category

« Previous Entries

Sysdatabases is empty–You say Whaaat?

Posted by Amit Banerjee on December 7, 2011

While trying to backup a SQL Server database using SQL Writer (snapshot backups using VSS framework), then you might notice the following error in the application event logs.

Log Name: Application
Source: SQLWRITER
Event ID: 24581
Task Category: None
Level: Error
Description:
Sqllib error: System table sys.sysdatabases in SQL Server instance <SQL Server Name> is empty.

The above error is a bit misleading because it doesn’t literally mean that all your databases on the SQL Server instance reported have disappeared. If you scour the application event logs, you will find that another error is reported along with this error during the same timeframe.

Log Name: Application
Source: SQLWRITER
Event ID: 24583
Level: Error
Description:
Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 28000, Native Error: 18456
Error state: 1, Severity: 14
Source: Microsoft SQL Server Native Client 10.0
Error message: Login failed for user ‘NT AUTHORITY\SYSTEM’.
DBPROP_INIT_DATASOURCE: <SQL Server Name>
DBPROP_INIT_CATALOG: master
DBPROP_AUTH_INTEGRATED: SSPI

Now it is clear that the SYSTEM account is unable to log into the SQL Server instance mentioned in the error message above. If you look into the SQL Errorlog, you will find the following error message:

Error: 18456, Severity: 14, State: 11.
Login failed for user ‘NT AUTHORITY\SYSTEM’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

State 11 is basically telling you that the account doesn’t have access to the SQL Server instance. The reason for the login failure is available on SQL Server 2008 instances and above. There is a catch here the Login Failed error message is only reported in the SQL Errorlogs and Application Eventlogs only if the System account is granted access to the SQL Server instance but not granted the right permissions to get the database list from the sys.databases catalog. The hex code 0x80040e4d corresponds to an authentication failure.

So why does this happen? When you initiate a backup of a SQL Server database through SQLWriter (VSS Framework), SQLWriter will try to connect to all online instances to build a list of files associated with each database. SQLWriter/VSS needs this information to create the exclusion list. The account that the SQLWriter service uses is the NT AUTHORITY\SYSTEM account. So if you have multiple instances of SQL Server online on the same server, then the SYSTEM account needs to have SYSADMIN permissions OR the necessary permissions to run a query against the sys.databases catalog on the instance. This is a by-design requirement and documented in the KB Article mentioned below. However, if the SQL Server instance is not started, then you SQLWriter is not bothered about the instance(s) as I/O need not be frozen for database files for an instance which is shutdown.

919023    SQL Server 2005 connectivity and Volume Shadow Copy Service (VSS)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;919023

Excerpt from above article:

Additionally, because of the types of operations that the writer must perform, we recommend that you do not remove the NT AUTHORITY\SYSTEM login from the sysadmin server role.

Now if you do not want to grant sysadmin permission to the System account, then you need to do the following to prevent the above error:

1. Grant db_datareader role to the SYSTEM account on the master database. This is required for other queries that the SQLWriter may need to executed during the course of the backup.
2. Grant ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database to the SYSTEM account. These are the minimum permissions required to query sys.databases table.

Normally the SYSTEM account is part of Built-in Administrators group on the box if it is not removed due to security hardening. You would need to grant the above permissions to the System account on all SQL Server instances that are installed and online on the server where you are trying to perform the VSS backup.

Another symptom of the problem manifests itself when you execute the command: vssadmin list writers. You will find that the above error message(s) are reported and the SQLWriter is not listed in the list of available writers.

One you have granted the SYSTEM account the necessary permissions, a quick way to verify that everything is working as expected is to run the vssadmin list writers command again. You might get the following output:

Writer name: ‘SqlServerWriter’
   Writer Id: <GUID>
   Writer Instance Id: <GUID>
   State: [1] Stable
   Last error: Non-retryable error

If you find that a non-retryable error is reported, then you need to restart the SQL Server VSS Writer service. Once this is done, run the command again and if there are no problems encountered, then you will get the following output for the SQLWriter.

Writer name: ‘SqlServerWriter’
   Writer Id: <GUID>
   Writer Instance Id: <GUID>
   State: [1] Stable
   Last error: No error

NOTE: If a SQL Server VSS (Snapshot) Backup is in progress for any instance on the server, then a restart of the SQL Server VSS Writer service should be deferred till the backup is completed or fails.

Posted in Backup, Troubleshooting SQL Issues | Tagged: , , , | 6 Comments »

Another SQL VDI error that can mystify you

Posted by Amit Banerjee on October 8, 2011

While performing a backup of a SQL Server using a backup software which uses SQLVDI, you might encounter a failure which reports the following failure hex code: 0×080070005. This is basically an Access Denied error message. If your backup software logs all the VDI API calls that are made, then you might even know which function call failed.

If you are using SQL Server Backup Simulator to simulate a VDI backup of the any SQL Server database with the same account that raised the error, you will find the following message in the Backup Simulator logs.

Trying to perform VDI test on a default instance
Error: VDS::Create fails: 0×80070005

The above message tells me that the IClientVirtualDeviceSet2::CreateEx function call failed. Now the important question here is why did the CreateEx function fail. If you look into the application event log, you will find an Error reported by SQLVDI (source) with the following text:

SQLVDI: Loc=CVDS::CreateEx. Desc=Create Memory. ErrorCode=(5)Access is denied.. Process=24456. Thread=13984. Client. Instance=. VD=Global\SQLBackupSim_SQLVDIMemoryName_0.

The above message tells me that I am creating a Global Shared Memory Object but I currently do not have the necessary privilege to create the object. Check the output of whoami /priv for the account that is running the Backup Simulator or the backup software and see if the following privilege is listed: SeCreateGlobalPrivilege. This privilege is granted by default to administrators, services, and the local system account. In case you are trying to use Backup Simulator on a Windows version which has UAC, then you need to run Backup Simulator using “Run as Administrator” option to avoid this issue. If the account trying to create this global memory object doesn’t have this privilege, then you can grant the privilege to this account using the following steps:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.

    The Group Policy dialog box opens.

  2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

  3. Expand Security Settings, and then expand Local Policies.

  4. Select the User Rights Assignment folder.

    The policies will be displayed in the details pane.

  5. In the pane, double-click Create global objects.

  6. In the Local Security Policy Setting dialog box, click Add.

  7. In the Select Users or Groups dialog box, add an account with privileges to run backup software or the Backup Simulator application.

Once this is done, you need to re-launch Backup Simulator or re-start the backup using the Backup Software as the security privilege will not modify the token that is currently being used by the backup software or Backup Simulator.

Another issue that you can run into even if you have the above mentioned security privilege is if you have multiple backups trying to create the same global shared memory object. In such scenarios, you would want to create a unique virtual device name. More information about the same is mentioned in the KB Article below:

903646    An application that uses the Virtual Device Interface feature of SQL Server 2000 to back up a SQL Server 2000 database may not back up the database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;903646

del.icio.us Tags: ,,

Posted in Backup, Troubleshooting SQL Issues | Tagged: , , | 2 Comments »

SQL Server Backup Simulator v2 available now

Posted by Amit Banerjee on June 18, 2011

SQL Server Backup Simulator is used by CSS to troubleshoot SQLVDI related issues and to identify if the SQLVDI DLL is functioning correctly. Based on the feedback received from the use of the tool and the current troubleshooting needs, we decided to do v2 release for SQL Server Backup Simulator.

The new features for the v2 release are:

1. Log backup – Now the tool can perform log backups. The tool performs COPY_ONLY backups so that your LSN chain is not broken.
2. Compression support – Starting from v2, the tool will allow you to take backups with compression enabled for SQL Server 2008 and higher.

The compression option drop-down list has three-options:
a. With Compression: This option will allow you to perform a backup with compression enabled even if the server default is not to use compression for backups.
b. No Compression: This option will allow you to perform a backup with compression disabled even if the server default is to use compression for backups.
c. Server Default: This option uses the server default setting (configuration setting: backup compression default) to perform the backup.

image

Screenshot of the v2 UI

Log Restore option is not currently available in this release. The incorporation of this feature will be evaluated while planning for the next release.

A big thank you to Sakthi [Blog | Twitter] for his assistance on the v2 release.

The latest release can be downloaded here.

Previous posts for Backup Simulator:
SQL Server Backup Simulator v1.0
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-backup-simulator.aspx
SQL Server Backup Simulator v1.2
http://troubleshootingsql.com/2011/01/17/sql-server-backup-simulator-cumulative-update/

Posted in Backup, Tools | Tagged: , , , , , | 5 Comments »

« Previous Entries