Best practices for connecting from ASP.NET Core to SQL Server?

Question

I've had some recent difficulty with SQL Server not liking the default AppIdentityUser for logins, so I went ahead and created a custom DB user with write access.

But it made me wonder - is this the best approach?

I was wondering what the best SQL Server login approach would be for Asp.Net Core. I know there's a question similar to this for normal .NET, but you can't encrypt a Core web.config/appsettings.json (well, in a quick and straightforward manner).

Here are the options as I see them:

Connect via SQL Server ID that is stored in appsettings.json.

• Pro: Already configured.
• Cons: Password in web.config/appsettings.json; have to specifically configure SQL Server ID. Not centrally revokable.

Connect via user NT ID via ASP.NET "AppIdentityUser".

• Pro: No passwords in appsettings.json.
• Cons: Not centrally revocable. Seems to be restricted to the server name for user.

Connect via Active Directory user.

• Pro: Easily revokable.
• Cons: Active directory user password in appsettings.json. Could be bad if somebody accidentally reuses that user in another application in the company, and that user gets breached.

Are there other options that I'm missing? Which of these options are used in which situations? Which are more standard? Are there pros and cons that I'm not thinking about?

Answer 1

You should absolutely use a custom SQL Login to connect to the database. Under the hood, the SQL Login could be tied to a local account, service account, network account, etc. It doesn't actually matter.

The real issue you seem to be having here is in not wanting (rightly) to expose login credentials in plain text. I'm not sure why you keep referring to Web.config here, as ASP.NET Core doesn't use that. Instead, there's various configuration providers that can be optionally utilized. By default, ASP.NET Core (at least since 2.0) adds a JSON config provider that looks for appsettings.json and appsettings.{environment}.json in your project, a command-line configuration provider, a user secrets config provider, and finally an environment variable configuration provider.

The last two are the most interesting for your circumstances. In development, you should use user secrets. In production, you should use environment variables. However, neither stores secrets in an encrypted way. The benefit to either approach is that the secrets are not in your project, and therefore also not in your source control. Even though neither is encrypted, it's not as big of a concern as you might think. Getting at the secrets in either would require direct access to the server/development machine. Additionally, user secrets is by default tied to a particular user account, accessible only to that user, and environment variables can be set up the same way. Therefore, someone would need to both gain access to the machine and gain access to the particular account. That's actually a pretty high bar, and if it were to occur, exposing a database password is really the least of your concerns at that point.

Nevertheless, if you want true encryption, you have the option of using Azure KeyVault. KeyVault can be used whether or not your application is actually hosted in Azure, and while it's not free, it's exceedingly cheap.

Finally, you can always create your own config providers or source third-party ones. For example, while the default JSON provider doesn't support encryption, you could potentially write one that does.