Securing credentials

Connection strings can contain sensitive data i.e. database credentials. It's usually not a good idea for developers to leave their database credentials inside scripts that are shared with end-users. This is an important consideration when sharing workbooks and extensions that contain SQL code.

To allow every user to supply their own credentials, connection strings can be templated. Instead of fixed credentials, the developer puts placeholders inside the connection string.

For example:

Server=mssql6.mojsite.com,1555; Database=thingieq_AdventureWorks2014; User Id={username:my_creds_123}; Password={password}

Two placeholder elements are supported:

• {username:id_of_the_credentials}
• {password}

The placeholder for the username must provide the identifier of the credentials. The identifier is used for storing and retrieving the credentials, to distinguish between credentials for different purposes.

When a script attempts to connect using the connection string in the example above, it will search for stored credentials with the id my_creds_123. If it does not find them, the user will be prompted to enter them:

The credentials are then inserted into the connection string instead of the placeholders and a connection attempt is made. If the attempt fails, the prompt reappears. If the attempt succeeds, the credentials are encrypted and stored in a local file for future use.

Once the credentials are stored, they are used automatically in the future every time a connection is attempted with a connection string that uses the same credentials ID (e.g. my_creds_123). If a connection attempt fails in the future, the user will again be prompted for their credentials.

Stored credentials are encrypted using the Windows Data Protection API and stored in a file in the user's AppData directory. Only the user that created the file can decrypt the data.