Best practice for storing connection string outside of LinqPad file
I'd like to be able to check my LinqPad files into Git - without storing the connection string there.
What's the best practice here?
We might also want to store API keys externally, but the connection string is the most important thing.
Comments
Best practice is to ensure that your connection doesn't requirement a password, using Windows Authentication or Azure Universal Authentication. Then your queries will be usable by others without any extra steps.
If you do need to specify a password, it will be encrypted using local account DPAPI. This means that other users won't be able to read it, until they edit the connection and re-enter the password. They should only need to do this once, however: if they click "Remember this connection", the locally re-encrypted password is stored in the local connection repository and will override the connection password stored in the queries themselves.
For API keys, use Util.GetPassword - this uses LINQPad's Password Manager which stores passwords and API keys locally.
Thanks for the prompt reply. We're connecting to a MySQL database and the .linq file contains a CustomCxString element that contains a password. Are you saying it's safe to check this file into Git as the password cannot be decrypted on other machines? Instead, users will be prompted for the password and then the password will be stored locally? So the "password" in the connection string is more like a cache key?
Yes, although it's the connection ID property that serves as the cache key, not the password. Do you plan to check it into a public repository or a company repository? Checking encrypted data into a public repository may still have risks; for instance, it may expand the consequences should your machine be compromised.
Thanks. If it's the connection ID, not the password, that serves as the cache key, why is the password stored in the LinqPad file at all? How is the encrypted password used?
We use a private repository on GitHub and the database server is only accessible over VPN. So the risk is limited, but I'm still not sure if it's really a good idea to check in the file with that information.
It's so that passwords will persist regardless of whether you click "Remember this connection". It's not actually the best solution: as well as the issue you just raised, it makes updating passwords potentially harder than it needs to be, because the same password could be required in multiple connections.
A better solution would be to use LINQPad's Password Manager, with password slots keyed to a name of your choosing. Will give this some more thought.
Hmm, I didn't quite get that. Why would you want passwords to be persisted? Especially if the password isn't what's actually used...?
Is this just some random string or is it an encrypted form of the actual password...?
I just checked again and the CustomCxString element actually contains the password in clear text. This is not good...
What version of LINQPad, and what driver are you using?
7.6.6 (x64) and LINQ to DB (MySQL database).
Have you ticked the "Encrypt connection string" checkbox in LINQ-to-DB's connection dialog?
Aha! I hadn't. Thanks. So now there is only an encrypted string - you're saying that is not decryptable? It's only a hash? So it's safe to check in?
It's not a hash - it's encrypted using local account DPAPI. I would consider checking this into a company repository to be low risk, but that's ultimately your call.
So it's encrypted using symmetric encryption? But the key to decrypt it is stored locally, so it's useless on another machine?
What would be the alternative if we don't want to check in the CustomCxString? And is there an option to just not have it persisted? Or only have something like a cache key persisted?
I might look at adding an option to use LINQPad's Password Manager - this would have a number of benefits. However, it would work with LINQPad's EF Core driver, not the LINQ-to-DB driver. I suspect it wouldn't be easy for the authors of the LINQ-to-DB driver to leverage this functionality because LINQ-to-DB doesn't appear to separate the password from the rest of the connection string. Are you able to use the EF Core MySQL driver?
I wasn't aware that there's a EF Core MySQL driver - sure, I could use that. Would that give me more options immediately or would you have to add a new feature first?
What is "LINQPad's Password Manager"? Is it some already existing API or it should be added? Cannot find anything similar in DataContextDrivers7.docx
We (linq2db) could add external storage support for passwords/auth/conection strings, but we need to know which storage you need.
The API is Util.GetPassword and Util.SetPassword (although you would probably need to use only the former).
Keep in mind that I've not tested - or even 100% thought through - the ideal of using LINQPad's Password Manager for connection passwords, but it seems to make sense. The connection dialog would include a check box "Use LINQPad Password Manager", and if checked, the password control would be replaced with a textbox labelled something like "Password Slot Name". This is a simple text string. To convert the slot name into a password, you would call Util.GetPassword (slotName). Util.Password is an existing API - it's used in scripts whenever you don't want to hard-code a password or API key into the script. It results in LINQPad checking whether a password has already been saved to this slot, and if not, a UI appears asking for the password, offering to save it in encrypted local storage. You can manage saved passwords in File | Password Manager.
All of this would work right now. The only thing that I think is missing is an easy mechanism to ensure that only one password request appears at a time, in case multiple connections request passwords - or even if the same connection requires a password via different mechanisms (schema generation, active query, "Test" button on the connection dialog, etc.) You could code this yourself, but it should probably done at the LINQPad end - possibly built into the implementation of Util.GetPassword.
It would be fine for me if no connection string information was persisted at all.
Another user cloning the repo would then see an error message because the entity classes cannot be resolved.
The user could then select a connection from their local connection dropdown.
That would be more than good enough for us.
Update: password manager support was added to linq2db driver v5.2.2. Just add token in format {pm:password-name} to main or secondary connection string.
Technically you could put whole connection string to password manager by using something like {pm:my-connection-string} instead of connection string
Nice work!