Dynamic Connection String

[sohb2015]

Hello All,

I have received a requirement from my client where due to security reason he wants to create dynamic connection string instead of using the direct user id and password. I am aware of the fact that we can use $(include=file location) to use a file for connection string. However, the requirement is such that a table/excel to be created where the password will be stored and maintained by the client alone. I have to store the password in a variable and use that variable in the connection string. I can't use $(include) statement. How could I achieve this.

Thanks

[jonathandienst]

If you are storing the password in any way, are you securing the document as well so that only that user can run it? If it is only reloaded and used by that user, put this into a folder on its own and secure using network security. If you plan to secure text or Excel output, I would look again to network security as the solution. But perhaps I have not properly understood your requirement?

[Peter_Cammaert · Accepted solution]

That's fairly simple to implement.

• Use regular Script statements to load a single row from an Excel
• Use the peek() function to extract username/password values from that single row and store them in variables. In the example I use vUsername and vPassword.
• Use $-sign substitution to replace username and password at the end of your connection string when executing a CONNECT TO statement

That's about it. You can best start from an existing CONNECT TO statement with unscrambled credentials. For an OLE DB connection, there are user ID and password parameters that accept unscrambled values. For example (using the SQL Server Native Client):

CONNECT TO [Provider=SQLNCLI11;Server=$(vServerAddress);Database=$(vDataBase);Uid=$(vUsername);

Pwd=$(vPassword);];

Best,

Peter

[sohb2015]

Thanks Peter.

However, the userid and password are encrypted, hence while reloading,error occurred : incorrect username or password.

What do you suggest.

Thanks