DTS & ADO Password woes

Hi all,

I'm having a problem with DTS. I need to use DTS to connect to a DB using ADO.

Is there any way I do not have to hard code my user ID and Password within the DTS package? Or is there any setting to set the password and userID else where?

PLease help.

Thanks!Are you using an ActiveX script component? Or are you using the DTS connection object? I assume (because it's ADO you referred to) that you are using the ActiveX object (and that you are using VBScript). If so, what's the DB you are connecting to? If it is SQL, then try using integrated security:

oConn.ConnectionString =
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=MyServer

If the DTS package is scheduled by the SQL Agent service, just make sure that the SQL Agent service account (don't use local system) has the right privileges.

If it is not a SQL Server you are connecting to, you will need to provide some additional info...

Regards,

Hugh Scott

Originally posted by tswern
Hi all,

I'm having a problem with DTS. I need to use DTS to connect to a DB using ADO.

Is there any way I do not have to hard code my user ID and Password within the DTS package? Or is there any setting to set the password and userID else where?

PLease help.

Thanks!|||Originally posted by hmscott
Are you using an ActiveX script component? Or are you using the DTS connection object? I assume (because it's ADO you referred to) that you are using the ActiveX object (and that you are using VBScript). If so, what's the DB you are connecting to? If it is SQL, then try using integrated security:

oConn.ConnectionString =
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=MyServer

If the DTS package is scheduled by the SQL Agent service, just make sure that the SQL Agent service account (don't use local system) has the right privileges.

If it is not a SQL Server you are connecting to, you will need to provide some additional info...

Regards,

Hugh Scott

Hi hmscott,

You guessed correctly! Would give it a shot and see what happens.

Thanks!