SQL Server 2005 connectivity and Volume Shadow Copy Service (VSS)

SQL Server 2005 connectivity and VSS

In this article, we discuss SQL Server connection issues that you may encounter when you use Volume Shadow Copy Service (VSS) enabled backup applications.

Background information

Before we jump into the details of the errors and problems, let's examine the different components that are involved in this discussion.

• VSS
  VSS is a framework that consists of a set of interfaces and structures that enable applications to perform backups of volumes while other applications use the data in these volumes. VSS is included with Microsoft Windows XP and Microsoft Windows Server 2003.
• Backup applications
  A backup application is any Windows application that requests a snapshot backup be taken by using VSS. For example, the Windows NT backup application that is included with Windows is a backup application.
• Writers
  Writers are applications or services that store persistent information on disk and that cooperate with providers and requestors through the shadow copy interface. We will discuss the MSDEWriter writer and the SqlServerWriter writer in this article.
  • MSDEWriter: The MSDEWriter writer is a VSS Writer for SQL Server. MSDEWriter is included with the VSS framework in Windows XP and Windows Server 2003. MSDEWriter works with SQL Server 7.0, SQL Server 2000, and SQL Server 2005.
  • SqlServerWriter: The SqlServerWriter writer is a VSS Writer for SQL Server. SqlServerWriter is included with SQL Server 2005. SqlServerWriter only works with instances of SQL Server 2005.

Connections to SQL Server

You might be wondering why VSS framework components would need to connect to SQL Server when the components are only performing a backup of the volume. During the initial phases of snapshot creation, the configured default writer makes a connection to the instances of SQL Server on the particular server. One of the first phases of a snapshot creation process is "Backup Initialization." During this phase, the backup application (requestor) performs the following actions to make sure that all the components in the snapshot creation process are ready:

• The backup application binds to the IVssBackupComponents interface.
• The backup application initializes the IVssBackupComponentsinterface.
• The backup application calls the IVssGatherWriterMetadata API to perform metadata enumeration.
  
  

The VSS framework then instructs all writers to gather metadata. This includes a default writer that is included with SQL Server. It could be either MSDEWriter or SqlServerWriter based on the server's current settings. This default writer for SQL Server connects to all instances of SQL Server that are started on the local system, obtains the required information about the databases on the instance of SQL Server, and then creates the metadata document. The metadata document is then returned to the backup application.

Identify the writer

Either MSDEWriter or SqlServerWriter will perform the metadata enumeration. So, either MSDEWriter or SqlServerWriter will need to connect to the instance of SQL Server. To determine which writer is configured by default as the writer for the instance of SQL Server, use the following information. The MSDEVersionChecking value is located in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\VSS\Settings registry key. This is a DWORD value and may not be present unless you manually create it.

Even though SqlServerWriter is installed with SQL Server 2005, it is not configured as the default writer for SQL Server 2005. You must manually configure SqlServerWriter as the default writer. For more information, visit the following Microsoft Web site: When you troubleshoot connection issues from the writer to the instance of SQL Server, you must be aware of the following important information:

• When MSDEWriter tries to make a connection, VSS acts as the client to SQL Server for this specific connection attempt.
• When SqlServerWriter tries to make a connection, the SQL Server VSS Writer service acts as the client to SQL Server for this specific connection attempt.

Problems when the writer tries to connect to SQL Server

You may experience the following two problems when the default writer tries to connect to the instances of SQL Server on the computer:

• The writer cannot perform successful authentication with the instance of SQL Server.
• The writer cannot establish a successful connection to the instance of SQL Server.

The symptoms that you notice when you use a backup application vary depending on the problem. For authentication problems, the writer cannot perform metadata enumeration and produces an immediate error in the Windows event log and the configured trace logs for the writer. For connection problems, the backup application stops responding for an extended period of time during the initialization phase. After the application stops responding, entries are logged in the Windows event log and the configured trace logs for the writer.

Authentication problems

When the VSS service and the SQLWriter service are installed, the service startup account is configured as Local System [NT AUTHORITY\SYSTEM]. When the writer tries to establish a connection to instances of SQL Server, the writer uses the credentials of the Local System [NT AUTHORITY\SYSTEM] startup account. You must make sure that the instances of SQL Server have granted access to this Windows account either explicitly or through group membership of BUILTIN\Administrators.

If authentication problems occur, the following messages are logged in the Windows event log.

Message 1

Message 2

Message 3

You may encounter the following error when you use SQL Server VSS Writer to take a backup:


This operation requires the Local System (NT AUTHORITY/SYSTEM) account to have one of the following:

• ALTER ANY DATABASE server-level permission
  
• VIEW ANY DATABASE server-level permission
  
• CREATE DATABASE permission in the master database

Message 4

You can clearly identify the writer that encountered the problem by using the Event Source. The first error is from SqlServerWriter. The second error is from MSDEWriter.

If you are running a SQL Profiler trace and the Audit Failed Login event is included in the trace, the following information is captured for the event.

SQL Server 2005

SQL Server 2000

You can clearly identify the writer that is attempting to establish the connection by using the Client Process ID.

Because of strict security requirements, SQL Server installations may have removed the BUILTIN\Administrators group from the SQL Server logins. If this issue occurs, you have to make sure that the NT AUTHORITY\SYSTEM account is granted access to SQL Server. To do this, you must add it as an explicit login. 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.


Even if the MSDEWriter writer and the SqlServerWriter writer can log in to SQL Server successfully, they may encounter the following error message if there are insufficient permissions to query the system tables or the catalog views that contain the information about the databases and the files that belong to the database: To avoid this error message, make sure that you assign the correct permissions to the account in SQL Server. For example, add this account to the db_datareader database role in the master database.

Connection problems

This kind of problem occurs when the writer cannot establish a SQL connection to the instances of SQL Server. The assigned writer tries to establish a connection to a specific instance of SQL Server that is currently running. If a problem establishing a connection occurs, the following error messages are logged in the Windows event log.

Message 1

Message 2

Message 3

Message 4

In the previous error messages, pay close attention to the writer, the protocol that is used for the connection, and the error message that is returned. These errors are not logged in the SQL Profiler trace for the Audit Login events.

These types of connection issues may occur for the following reasons:

• Aliases on the computer are configured with incorrect information.
• There is no common protocol that the client uses to connect and that the server is listening on.
  
• The Microsoft Data Access Components (MDAC) configuration or the SQL Native Access Client (SNAC) configuration on the computer has been changed.
• Known protocol issues exist on specific platforms or editions of SQL Server.

For more information about connection proglems, visit the following Web site:

Isolate connection problems

If any of these problems occur and you want to obtain more information, you do not have to repeatedly run the backup application. You can use the VSS administrative command-line tool (Vssadmin.exe). To make sure that you use the same credentials and settings that are used by VSS, follow these steps to run the simulation exercises.

Note The following steps let you open a command prompt by using the credentials of the NT AUTHORITY\SYSTEM account. The writers will also use the NT AUTHORITY\SYSTEM account.

1. Make sure the Task Scheduler service is started.
   
2. If you are using a Terminal Services connection or a Remote Desktop connection, make sure that you log on to the CONSOLE session.
3. At a command prompt, and type the following command:
   at 14:05 /interactive cmd.exe
   Note Make sure that the time that you specify in this command is ahead of the present system time on the computer. When the specified time is reached, a new command prompt appears. It resembles the following:
   C:\Windows\System32\svchost.exe
4. At the new command prompt, type the following:
   Vssadmin list writers
5. Review the output to determine which writers are listed and the status of those writers, particularly MSDEWriter and SqlServerWriter.
   
   Note If a problem occurred with the metadata enumeration, the writers will not be listed. You must make sure that the correct writer is listed in the Vssadmin output with no errors. This must occur before a backup application can use a writer during the snapshot creation process.

For each instance of SQL Server, you must examine the SQL Server error log to understand which protocols this instance of SQL Server is currently listening on. The typical entries you will notice are listed below.

SQL Server 2005

SQL Server 2000

When you review the previous examples, you notice that the instances of SQL Server are listening on the following three protocols:

• TCP
• Named Pipes
• Shared memory

With specific protocols configured, there may be times when the server does not listen. Sometimes, even if you have configured a specific set of protocols for the server to listen on, the server might not be able to listen on all of them. This could be due to environmental issues on the server. So you will need to take a look at the protocols that you have configured for this particular instance of SQL Server to listen on. For SQL Server 2005, you will need to use SQL Server Configuration Manager. For SQL Server 2000, you will need to use Server Network Utility.

Now we will move onto the client portion of protocol configuration. There are two client configuration points you need to keep in mind. The first one is the list of enabled client protocols. The other one is the order in which the client protocols are used for connection. The default settings differ on various operating systems and for the different versions and editions of SQL Server. See the appropriate Books Online topics for details on what is the default setup.

The key point to understand here is that there should be at least one client protocol enabled that matches the server protocol. The only way that you can force the connections from the writer to go through a specific protocol is to configure an alias on the server. Neither the VSS framework nor the writers have any configuration settings where you can supply any connection-specific or authentication-specific information for the client computer's use.

You can run the following simple test to determine which protocol is being used on which default connections to the specific instance of SQL Server:

1. Make sure that the Task Scheduler service is started.
2. If you are using a Terminal Services connection or a Remote Desktop connection, make sure you are logged in to the CONSOLE session
   
3. At a command prompt, type the following command:
   
   at 14:05 /interactive cmd.exe
   Make sure the time you specify in this command is ahead of the present system time on the client computer.
4. When the specified time is reached, a new Command Prompt window opens that has the following name:
   C:\Windows\System32\svchost.exe
   .
5. In this window, run the query for your version of SQL Server:
   • For SQL Server 2005, run the following query:

     sqlcmd -E –S<server> -Q"SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@spid"

   • For SQL Server 2000, run the following query:

     osql -E –S<server> -Q"SELECT net_library FROM sysprocesses WHERE spid = @@spid"

6. The output from this query will give you the information on what protocol works on the client computer. There is always a possibility that the query in step 5 might fail and give you an error message. You will need to troubleshoot that error and resolve it to make sure that client instances of SQL Server on the local computer can successfully connect to the instance of SQL Server in question.
7. When you run either the sqlcmd command or the osql command, you can force the connections to be made by using a specific protocol and then observe the result. To do this, prefix the server name with the –S parameter and the protocol you are interested in observing. For example, the three possibilities are:
   
   • –Stcp:servername
   • –Snp:servername
   • –Slpc:servername
   Note In these examples, servername is the name of your server.

For more information about an issue that occurs on Windows Server 2003 x64-based computers, click the following article number to view the article in the Microsoft Knowledge Base:

913100 FIX: The MSDE writer is not included when you try to use the Vssadmin.exe tool to list subscribed volume shadow copy writers

For more information about how SqlServerWriter interacts with VSS and instances of SQL Server, visit the following Microsoft Web site: For more information about VSS and the architecture of VSS, visit the following Microsoft Developer Network (MSDN) Web site:For more information about how to manage access to SQL Server for Windows accounts and groups, see the following topics in SQL Server 2005 Books Online:

• CREATE LOGIN (Transact-SQL)
• sp_grantlogin (Transact-SQL Reference (SQL Server))

For more information about how to manage the protocols that SQL Server is listening on in SQL Server 2005, visit the following MSDN Web site: For more information about how to manage the protocols that SQL Server is listening on in SQL Server 2000, visit the following MSDN Web site: For more information about how to manage the protocol order and a list of protocols that can be used for client connections in SQL Server 2005, visit the following MSDN Web site: For more information about how to manage the protocol order and a list of protocols that can be used for client connections in SQL Server 2000, visit the following MSDN Web site: For more information about connectivity issues, click the following article number to view the article in the Microsoft Knowledge Base:

827422 How to troubleshoot connectivity issues in SQL Server 2000

812817 Support WebCast: Microsoft SQL Server 2000: Troubleshooting connectivity

To make sure that the MDAC installation works correctly and that the network libraries are installed correctly, visit the following Microsoft Web site to download the Component Checker utility: I’d like to thank Suresh for his hard work on this article. He has in-depth experience on several Microsoft Windows server products and enjoys supporting customers and debugging problems. Hope you enjoyed this month’s topic! Thanks for reading!

As always, feel free to submit ideas on topics you want addressed in future columns or in the Knowledge Base using the Ask For It form.