I've been trying to create a simple DTS Import package using the "Import"
Wizard. I can get the package created, executed and saved but when I try to
open it up in designer or even execute it again I get an "sa" login failure
error from the "Microsoft OLE DB Provider for SQL Server".

Any ideas what's happening??

Environment: SQL Server 7 with latest sp.

I'm connecting via the Enterprise Manager from a networked workstation.



Any help is definitely appreciated.

Cathy

Has anyone at your place changed the sa password? And when does the error
occur? Is it when you open the package, or open a object within the package?

"caeriel1" wrote:

The sa password is fine. When I created the data import package I was asked
for the sa password for both servers. I ran the package PRIOR to saving it
and it functioned fine. The I saved the package and again I gave it the sa
password; it saved fine. Now, when I attempt to open the package in designer
to edit it I get that error message.

"Mutley1" wrote:

Hello caeriel1,

OK so when you was asked for logon details to which you gave "sa" this was
on setting up the package right?. This means the connections inside the
package should be using "sa" "whatever".

what i suspect is happening is that the password was not persisted and when
you execute the package it is passing blank for the password. This would
account for the failure when executing. What I am having a problem with
is when you say you get this error when openeing the package.

Are you prompted for a password when you open the package?

Allan

No, I'm not prompted for a password. I just get that "sa login failure".

Here's another piece to the puzzle. Since my attempts to save the DTS
package to the SQL Server resulted in an unusable DTS package, I tried
saving it as a file. I was able to successfully save the package to a file.
I was then able to open up the DTS file in the Designer and edit the package
and resave it to the file all with no problems. I've been able to
successfully execute the file DTS from the "dtsrun" command-line utility and
also from within the Designer. The package is constructed correctly with all
the required sa passwords. However, when I attempt to save this to the SQL
Server I get the "sa login failure".

If, as you suspect, the sa password is not "persistent" how can I fix that?

"Allan Mitchell" wrote:

Hello caeriel1,


The persisted sa password thing should only really be a problem when you
execute not save or view.

Can you create a brand new blank package and save to the SQL Server?

I can save the package to SQL Server with no problems. But, as soon as I try
to execute it or to open it in designer I get the "sa login error"

"Allan Mitchell" wrote:

I also tried to create a blank package. Still will not work. I can save it
to SQL Server with no problems but as soon as I try to open it with Designer
I get the "sa login failure..." error message. Now, how do I fix the
persistent password problem??

"Allan Mitchell" wrote: