Practical benefit of using SqlCredential vs username/password in connection string

Question

Currently I'm using SQL Server Authentication with the username/password as part of the connection string for my web application. As far as I know this standard procedure within the .NET world and possibly beyond. However, I was perusing the docs for the SqlConnection.ConnectionString property due to an unrelated matter and noticed they say:

SqlCredential is a more secure way to specify credentials for a connection that uses SQL Server Authentication.

I assume that most of this security is due to the use of SecureString to store the password. This class comes with its own set of problems when used in the context of a web application because it implements IDisposable. That means I have to do one of three bad options:

1. Purposefully not dispose of the SecureString leaving it a static global variable which reads the password from the config file once.
2. Accept the performance hit of reading the password from the config file every request.
3. Bypass the benefits of SecureString by keeping the password around as a string so I can dispose of the SecureString regularly without incurring the penalty of reading from the config file every request.

Given those problems and the fact that if someone has access to the server you're hosed anyway. Plus it appears as if the benefits of encrypting the connection string basically boil down to reducing the risk of accidentally leaking details to people who shouldn't have them:

By keeping the connection details out of source control, and out of general distribution, you reduce the possibility of loss or leakage. ... Due to this if encryption is in use, separating the key from the connectionstring and separating the connectionstring from the source is an essential action. Doing this has separated the duties and protection of the encryption keys (or configuration files) becomes a system administrator function, rather than a developer function.

However, it seems to me the most efficiently way to reduce that risk is by restricting access to the server and the connection string to only those who need to know via the automated build/release pipeline¹, and securing your server is obviously the first and most important step to protecting the connection string. It seems to me that there is probably a minor theoretical benefit to using the SqlCredential class in a web application², but there isn't a real practical benefit over having the username/password in the connection string.

That being said there is probably some fundamental flaw in my reasoning because the folks at Microsoft are a smart group. So what am I missing?

---

^{_{1: Transforming/adding the value automatically in the build/release pipeline.}}

^{_{2: I'm using an Azure Cloud Service and connecting to an Azure SQL Database, with most of the application layer logic written in C# if it matters.}}

Answer 1

I'm not a .net developer, but Java is very similar. I believe the use-case of SecureString, and SQLCredential is for any password that isn't long lived. i.e. you log into a remote SQL Server briefly, and then drop the connection and go on with anything else the app has to do. Using SecureString would appear to remove this briefly used password from memory ASAP rather than wait for it to be removed at a (possibly much later) time.

From your description your application uses a persistent database connection, and likely a database connection pool. So you need the username/password to be in memory all the time. There's little or no benefit to removing the password from memory since it'll be loaded into memory again very shortly, and ultimately is stored in some config file anyway.

As far as questioning your own thinking, it's good to do this, but don't give experts ultimate authority. They can't know your specific situation. I'd advise caution when reading generalized advise. It may or may not apply to you. In this case, Microsoft has to give a broad based answer that applies to everyone, in a wide range of uses. Microsoft has to advise anyone developing .net apps on the desktop as well, which could include a user simply typing in a password. Desktops have an entirely different threat model than servers do. For a desktop user who entered in a password once in the morning and dropped the connection, it would be "bad" if that password lived on for hours afterward, and the machine was then compromised by some completely unrelated security hole.

In other words, it's a "best practice" to just tell everyone to use SQLCredential rather than try to explain everyone threat models, etc. Most people aren't going to go to the depths of understanding you're looking at.

Answer 2

If you set user-password in the connection string instead of using the SqlConnection.Credential property (which internally uses SecureString), a malicious administrator can dump the memory of a running app and read the password in clear-text.