Forum Discussion

Hanila's avatar
Hanila
Occasional Contributor
2 days ago

How to pass in a parameter to DB connection string

 var Qry = ADO.CreateADOCommand()
    Qry.ConnectionString = "Provider=PROVIDERDB;Data Source=SOURCEADDRESS;Initial Catalog=MYDBNAME;User ID=USERID;Password="+ProjectSuite.Variables.sqlReadOnlyPWDINT+";";
    Qry.CommandText = Query;
    Qry.commandType = cmdText;
    var RecordSet = Qry.Execute();
    return RecordSet;

 

This structure is not working for me . I am not able to get the sqlReadOnlyPWDINT passed down to connection string unless its stored as string instead of password. 

Either I need to hardcode it in my script or store it as variable of SRTING  type for this connection string to work. Neither of them will satisfy my risk team. Any solution to this problem?

4 Replies

Sort By
  • rraghvani's avatar
    rraghvani
    Icon for Champion Level 3 rankChampion Level 3
    2 days ago
    Copy Link

    Only the SetText and Keys methods can properly access the values of Password variables. Other methods get a special variable moniker and the path to the variable instead of the variable value itself.

    Some possible workarounds, see https://support.smartbear.com/testcomplete/docs/reference/program-objects/builtin/inputbox.html or https://support.smartbear.com/testcomplete/docs/testing-with/advanced/user-forms/entering-passwords.html .

    Depending on your db connection string, it might not be necessary to provide a username and password. Within my scripts, I access SQL Server DB using the following connection string,

    "Provider=MSOLEDBSQL19.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=rsg_ALT_TestDataMaster; Data Source=E22-BE-SQLAL01D; Use Encryption for Data=False;"

    which uses Windows Authentication i.e. trusted connection.

  • Hassan_Ballan's avatar
    Hassan_Ballan
    Regular Contributor
    2 days ago
    Copy Link

    As rraghvani pointed out and due to security constraints, prompting user for credentials is not a good solution for CI/CD. Depending on your dbms technology you will need to use a different authentication method other than user credentials. Ask your developers how do they connect, I am sure they are not storing credentials in the project.

  • Hanila's avatar
    Hanila
    Occasional Contributor
    17 hours ago
    Copy Link

    Thank you RRaghavani and Hassan.

    I will surely keep this in my learning record. In our structure, the user with access to the tool and the pipeline has no 365 account, which means I cant use the above structure. 

    Customer care team gave me the solution to Decrypt at the end of calling parameter.    Parameter.DecryptValue. This way I can store the details in password format and retrieve it as required for the connection string. 

    Do you guys have any suggestions on this approach ? 

     

    • rraghvani's avatar
      rraghvani
      Icon for Champion Level 3 rankChampion Level 3
      8 hours ago
      Copy Link

      A while back, I provided a possible solution, see  Encrypt/Decrypt Base64 string in TestComplete | SmartBear Community - I didn't have dotNet extension installed or the appropriate license, so I was unable to provide the full code. However, the snippet of code was able to help. You could implement something similar.

      If the automation scripts is being used by a team, would it not be best to have a dedicated user account to access the database?