Dynamic SQL Authentication

• Rafferty Uy

  SSC-Addicted

  Points: 428

  More actions

  November 25, 2004 at 8:50 pm

  #63570

  Hello there!

  I want to use an ActiveX Script Task object to dynamically set the username/password of the Server object which uses SQL authentication. How is this done?

  I'm sorry for the very trivial/basic question. It's just that I don't know where to look these things up. Please advice

  Thanks!

  Rafferty

• Robert W Marda

  SSChampion

  Points: 13413

  More actions

  November 29, 2004 at 1:57 pm

  #531786

  If you are planning to use the connection in the ActiveX script where you plan to set the username and password then this code should do the trick:

       Logintoyourdb = "Server=" & DTSGlobalVariables("gvServerName").Value & ";Database=dbN30D;UID=" & DTSGlobalVariables("gvUserName").Value & ";PWD=" & DTSGlobalVariables("gvPassword").Value

  Each portion of the above code uses a global variable to provide the required value.

  I hope this helps.

  Robert W. Marda
  Billing and OSS Specialist - SQL Programmer
  MCL Systems

• Rafferty Uy

  SSC-Addicted

  Points: 428

  More actions

  November 29, 2004 at 6:19 pm

  #531801

  What I meant was to do something like this:

  Set objConn1 = DTSGlobalVariables.Parent.Connections("gDBConnection")

  objConn1.DataSource = DTSGlobalVariables("gDataSource").Value

  objConn1.Catalog = DTSGlobalVariables("gCatalog").Value

  objConn1.Username = DTSGlobalVariables("gUsername").Value // <-- doesn't work

  objConn1.Password = DTSGlobalVariables("gPassword").Value // <-- doesn't work

  Do you know how? Do I just add a UID and PWD definition in the DataSource and it'll automatically set the SQL server to using SQL Authentication?

  Also will the connection object be automatically be set to use SQL Authentication instead of Windows Authentication?

  Thanks!