Storing Datasource Credentials

I signed up for a subscription that emails me my favorite comics every day. When creating the login I entered my standard credentials for sites where it's no big deal if someone can log in as me. And it wasn't acceptable. The login required a password of 12 characters that had to have alpha, numbers, & punctuation. Guys – this isn't Fort Knox – it's a list of cartoons.

On the flip side, at one of my banks I called up to find out how to change my password. The standard way is I tell the person on the phone my new password and she enters it. And this then is full access to my bank account including transferring money. (They did have a way to change it myself – but I had to insist.)

The trick is finding the best balance between convenience (no security) and security (no access possible). And that requires weighing out the trade-offs. I've also found that any approach to security can generally be improved by discussing it and seeing what was missed. Hence this post. Any comments, suggestions, etc are greatly appreciated.

With AutoTag we have a wide variety of use cases. First off, in all cases the user only needs read (select for SQL) access. This means that even if the credentials are broadcast to the world, if they are set to only allow a select, the data cannot be changed in any way.

So if the datasource is test data, scrubbed of any real info, then security is essentially unnecessary. For example, we have a test database that has the Northwind and AdventureWorks databases on it – and that's all. If the world has the read-only credentials to that, there is no downside. In this case any security efforts that get in the way of the user are an inconvenience for no reason.

On the flip side, a programmer may need to test against an exact copy of the corporate payroll database. While they have read-only access and cannot change anyone's pay, they can easily read the monthly salary of every employee. These credentials you want to keep very safe.

We are implementing four options for handling the login credentials to each datasource tied to a report template.

1. If the datasource can be opened using integrated security (i.e. it uses your login to Windows), we use this. This is the best approach as we store nothing and the user does not need to enter anything.
2. We store the credentials in the template in plaintext (it, and other information, is uuencoded – but that is not any real protection). These credentials travel with the template so anyone that has the template can pretty easily get the credentials. But this is very easy to use as it logs in anyone using the template in to the datasource automatically.
3. We use the .NET ProtectedData class to encrypt and decrypt the credentials. This uses the currently logged in user credentials to encrypt the info and as such requires no action on the part of the user. The info is then decrypted with no user interaction, but only if logged in as that same user. On a system logged in to any other user the info cannot be decrypted.
   We store the encrypted data in the template, not on the local computer. We considered storing this info in the registry but there are some complexities in then matching a given registry entry to a given template and there is no way to know when entries are no longer used so the registry just grows over time. The downside of this is to get the credentials, someone merely needs the template and your Windows login – but they do not need to be on your computer.
4. We require the user to enter the username & password when opening the template. In this case we never write the credentials to any file or other repository. This requires the user to enter it each time, but provides a very safe approach.

There are two other decisions of note we took in our approach:

1. The choice of approaches 2 – 4 above are set on a system basis. (For any given datasource you can select integrated security). So you cannot mix and match based on the datasource. We did this to keep the datasource UI simpler as we believe 99.99% of our users will always be using a single approach.
2. We do retain the credentials, in plaintext, in memory while the program is running. We do this even for approach (4). We do this because we need to pass those credentials every time we open a connection and that is occurring a lot in the wizards. So the credentials can be pulled from the memory of the app while running – which includes the disk if it is paged out by the memory manager.

What do you think?

Comments

[ben logan]

I used to teach science and I always said safety 3rd. I am a big believer in Darwinism. However, I would want to go the extra mile with the encryption in the .NET ProtectionData class. My data might not be important to you, but some of it could be critical to my team and our project. A little inconvenience is minor to having my data corrupted at any point along the way. Ben