Error: "Failed to login to SQL Server" when backing up multiple databases (4332376)

User gets an error 'Failed to login to SQL Server' in the log when backing up multiple databases in a single job. However, a retry, to backup the failed databases individually, completes successfully.

Possible causes:

1. SQL Server Service and SQL Agent Service is not running using the same login account or the login is not a member of Administrators.
2. When using a Maintenance Plan to backup your databases, you will need to use an "owner" that has the proper rights to backup your databases, if using the console to do one off backups you'll need to change the login information for LiteSpeed to use an account with the proper rights to all your databases.
3. The Server is running out of resources. Lower the compression level or MaxTransfer Size until you find a level that lessens the load on the server resources and allows successful backups. Run the backup jobs sequentially, not simultaneously.
4. The instance is using SQL Authentication
5. The default database (for the user running LiteSpeed, or the logins for the SQL Server Service or SQL Server Agent or the LiteSpeed
Activity Service) is not set to master database.
6. The SQL Server is timing out when LiteSpeed tries to login to do multiple backups.
7. The network connections are requesting SQL Server connections too quickly when the maintenance plans backup several small databases in sequence.

WORKAROUND

To resolve the problem #1 or #2:
To change the owner/login for a maintenance plan:
1- Open up the Maintenance Plan designer in LiteSpeed
2- Click on the Manage Connections toolbar option - the button is to the far right
3- Edit the connection and change as needed

To resolve the problems listed as #6 and #7:
Development has provided a script to manually add a delay/pause between the backups (this delay cannot be added from the Create Maintenance Plan GUI).

The delay parameter is the same for Native SQL Server and LiteSpeed: WAITFOR DELAY '00:00:05'
The delay allows the SQL Server to complete the backup of the previous database, update the msdb and then begin the backup of the next database listed in the maintenance plan.

For the complete script, please contact: http://sqlserver.quest.com/forum.jspa?forumID=338

LiteSpeed Verbose error log may display similar text:
Backup DB: Northwind
17/06/2008 9:00:03 PM: Parameters passed in: @FileName=''D:\Backup\Northwind_db_200806172100.BAK'', @FileNumber=1, @Logging=3
17/06/2008 9:00:03 PM: --DEFAULTS USED: ,@threads=5 ,@throttle=100 ,@maxtransfersize=1048576 ,@buffercount=20 ,@priority=0 ,@compressionlevel=1 ,@CRYPTLEVEL=0 ,@affinity=0 ,@comment=''''
17/06/2008 9:00:03 PM: LiteSpeed 4.8.4.00086
17/06/2008 9:00:03 PM: PeakVirtualSize=33705984
17/06/2008 9:00:03 PM: VirtualSize=27525120
17/06/2008 9:00:03 PM: PageFaultCount=929
17/06/2008 9:00:03 PM: PeakWorkingSetSize=3420160
17/06/2008 9:00:03 PM: WorkingSetSize=3420160
17/06/2008 9:00:03 PM: QuotaPeakPagedPoolUsage=87404
17/06/2008 9:00:03 PM: QuotaPagedPoolUsage=73180
17/06/2008 9:00:03 PM: QuotaPeakNonPagedPoolUsage=2920
17/06/2008 9:00:03 PM: QuotaNonPagedPoolUsage=2400
17/06/2008 9:00:03 PM: PagefileUsage=1064960
17/06/2008 9:00:03 PM: PeakPagefileUsage=1064960
17/06/2008 9:00:03 PM: PagedPoolUsage=115822592
17/06/2008 9:00:03 PM: Connection name: LiteSpeed {2de260c0-081a-4f39-9001-ece389de8bcb}
17/06/2008 9:00:03 PM: OleDbConn::connect ->

17/06/2008 9:00:03 PM: Provider=SQLOLEDB;Integrated Security=SSPI;Data Source='SQLSERVER';Application Name='LiteSpeed {2de260c0-081a-4f39-9001-ece389de8bcb}';
17/06/2008 9:00:20 PM: hr=80004005
17/06/2008 9:00:20 PM: SLS::InitialiseConnection -> Error: -1, Msg: Failed to login to SQL Server

17/06/2008 9:00:20 PM: XML return:
<MESSAGES>
<ERROR error="-1">Failed to login to SQL Server
</ERROR>
</MESSAGES>
17/06/2008 9:00:20 PM: TsmEnvironment::close