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!

DSNLess connection with Failover to a SQL Server 2005 cluster

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

When you configure Mirroring without a witness, failover is not automatic. You must manually failover the database - you do this via alter database or using the button provided in the database mirroring properties.

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

DSNLess connection with Failover on SQL Server 2005 Cluster ?

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

Failover Partner is used in the case of database mirroring, not for the case of cluster. Check here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Cluster is transparent to client, i.e., client should be able to connect to the server without knowing the server is a cluster or not. In cluster case, use "server=your_virtual_server_name" in the connection string. Please check my blog at http://blogs.msdn.com/sql_protocols/archive/2005/12/05/500013.aspx and let us know if you have further problem.