SQL Server 2005 Maintenance Plan Error: Login Timeout Expired

[fuzzyocelot]

Hello all!

I’m having some problems with a basic maintenance plan on a server running SQL Server 2005 SP2. What it boils down to is that I can’t get the maintenance plan to run. It won’t run when I manually execute it (right click on the name under Maintenance Plans and select Execute) and it won’t run in a job.

This particular server contains very sensitive data and there is a LOT of security on it. I have to be careful what I do. Because of that, I can’t run the job under a sysadmin account. I have to run it under a lesser-privileged account. That means I’ll need to set up a proxy account. But for now, I can’t even get it to run under my account which has sysadmin permissions.

Here’s the error I get when I run it in a job:

Could not load package "Maintenance Plans\Plan01" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed. Source: Started: 9:00:00 PM Finished: 9:00:19 PM Elapsed: 18.594 seconds. The package could not be loaded. The step failed.

I’ve Google’d the error but haven’t had much luck. Maybe I missed something.

Unfortunately, I can’t view the history on the maintenance plans. There’s nothing there! No log files, nothing! Within the plan, when I click on the Reporting and Logging button, I can see that "Generate a text file report" and "Create a new file" are selected. A valid path and file name are in the folder text box. Under Logging, the "Log extended information" is selected. Yet, when I right click the plan name and select "View History", there is nothing listed. When I go to the server, there are no log files in that folder.

By the way, the plan right now contains only a "check database integrity" task and includes indexes. That’s all I have in there for now as a test. Under Manage connections, I have the server name specified and my login.

Does anyone have any ideas what is wrong and how I can fix it? Thanks!!!

 

[fuzzyocelot]

I probably should also mention that the database files are encrypted including the system databases. Could that be what's causing the problems?

 

[mrdenny]

The internal logging of the package isn't going to help you any. You aren't getting the SSIS package loaded.

The package could not be loaded.

What rights to the OS does your account have?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[fuzzyocelot]

Unless someone changed it, my account has local admin rights on the OS. I'll double-check.

 

[fuzzyocelot]

I just double-checked. My account has local admin rights on that particular server.

 

[fuzzyocelot]

Also, I checked and there are no SSIS packages on this server. Just out of curiosity, I logged onto the server remotely, opened Management Studio, and connected to Integration Services. (Note: I can't connect to SSIS remotely from my computer because we're not using windows authentication for this server yet.) The error I saw said something about not having a default instance installed. This server has two named instances of SQL 2005 installed. There is no default instance. Does this make a difference?

Thanks!

 

[fuzzyocelot]

I found the info in BOL about configuring the SSIS service. I thought the default instance part sounded familiar. Apparently by default SSIS is configured to manage packages stored in msdb in a local default instance of the database engine. Since there is no default instance on this server, it may help to change the configuration file to use the named instances. I'll have to get permission to try this out. Does anyone concur?

 

[mrdenny]

Yep, that's the problem. SSIS looks at the default instance by default. You have to change the XML file to use the named instance.

The file is here %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[fuzzyocelot]

The one thing that's bothering me right now is that I haven't been able to recreate the "login timeout expired" error on any other server or even on my computer. I have SQL Server 2005 installed on my computer with two named instances. I didn't install a default instance. I also didn't change the Integration Services config file. I can't connect to SSIS which is fine. The problem is the maintenance plans work on my local installation. I don't get any "login timeout expired" errors.

The only other major difference that I can think of between the installation on my computer and on the server is that the data on the server is encrypted and the data files are encrypted at the file system level. Would the file system level encryption pose a problem for maintenance plans? I believe the system databases are also encrypted at the file system level.

Thanks!

 

[fuzzyocelot]

It's looking like we may not get permission to try modifying the IS config file on the server. They're close to going live with the system and are very nervous about doing anything on the server right now because it's very touchy.

So I'm thinking of trying to create a stored procedure to perform the maintenance tasks and schedule a job to run on a weekly basis. It seems plausible.

Thanks for your help, though. I really appreciate it.

 

[mrdenny]

Encrypting the data files shouldn't be a problem except for all the extra IO. But the maintenance plans shouldn't care.

That sucks that they won't let you configure the server correctly before release.

The maintence plans should work if you save them to the file system instead of the msdb database. (Not sure if you can do that via the UI easily.)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[fuzzyocelot]

Thank you. It's good to know the file encryption shouldn't be a problem.

I'll make a note about trying to save the maintenance plan to the file system. I'll bring it up in our next meeting.

For now, it looks like they'll let me create a job with the T-SQL for some maintenance tasks. So I can at least do that much until we get this figured out. Could that be a security risk? Putting the T-SQL for maintenance tasks straight into a job step?

Thanks again!

 

[olahallengren]

>So I'm thinking of trying to create a stored procedure to perform the maintenance tasks and schedule a job to run on a weekly basis.

I have made a stored procedure based maintenance solution that does backup, integrity check and index optimization.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Ola Hallengren

http://ola.hallengren.com

 

[fuzzyocelot]

Thanks! I'll take a look at it.