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:
- Purposefully not dispose of the
SecureStringleaving it a static global variable which reads the password from the config file once.
- Accept the performance hit of reading the password from the config file every request.
- Bypass the benefits of
SecureStringby keeping the password around as a
stringso I can dispose of the
SecureStringregularly 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 pipeline1, 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 application2, 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.