Securely Manage Database Credentials Using Visual Studio Manage User Secrets


By:   |   Updated: 2020-03-05   |   Comments   |   Related: More > Application Development

Problem

As a developer we are all familiar with using web.config or appsettings.json to store our database connection, user credentials and other application secret keys of our web applications. Last year I had an opportunity to explore and learn .Net Core and I learned that it is not secured and those secrets should not be committed within the code of our repository. Safe storage of our application secrets are now a need and a must for every software development project. But where do we keep them? Is it necessary even if it’s in a development environment? My answer is YES and, in this tip, I will share with you how to manage application secrets in a development environment.

Solution

Microsoft has provided us a new feature to safely store our application secrets. It's a simple, but powerful tool named "The Secret Manager". This has been available since .Net Core 2.x and currently I'm using Visual Studio 2019 .Net Core 3.1. It uses the File Configuration Provider to store user secrets in a JSON file on the local system. When we right click from our project, we can see it from the menu as "Manage User Secrets". This is a great alternative to environment variables and it will not be committed within the code of our repository. The structure matches that of appsettings.json file and it has a tool to support administering secrets. Settings are still stored on disk in clear text that’s why it’s good for development environment only. But I can say this is still safe because people would still need to have access to your Windows account.

Storing Database Credentials Using Manage User Secrets

There are two ways to initialize or access the secret manager in a project when using .Net Core 3.x or later. You can use .Net Core CLI from the ASP.NET Core project directory or you can use Visual Studio 2019 from within the IDE.  This can be achieved by right-clicking on the project in Solution Explorer, and selecting Manage User Secrets from the context menu. Below are the steps to store sensitive data during development of an ASP.Net Core application on a development machine. Here I will use the Visual Studio IDE option.

  1. Using Visual Studio 2019 Community edition, open your ASP.NET Core project. Below is a screenshot of the .csproj file property group that you will see initially if you just create a new project.  If you already have an existing project the interface may vary. In this example this is a newly created project for the sample code.
Content of newly created project .csproj file.
  1. In the Solution Explorer, right-click on your project and select Manage User Secrets.
Going to Manage User Secrets option from the pop-up menu.
  1. An empty secrets.json file will be displayed.
Generated empty secrets.json file where you can keep your database credentials
  1. It will also add UserSecretsId element within a PropertyGroup of the .csproj file. By default, the inner text of UserSecretsId is a GUID. The inner text is arbitrary, but is unique to the project. Below is a screenshot of .csproj file property group where UserSecretsId was added.
phone book api
  1. Click on the secrets.json tab to add your connection string with the user credentials. If you have your connection string apart from the appsettings.json file you may cut paste your connection string to the secrets.json file.

Sample connection string configured in the appsettings.json file:

Appsettings config file sample

Sample connection string configured in the secrets.json file:

Secrets.json file sample configuration

Retrieving Database Credentials

User secrets can be retrieved via the Configuration API. ASP.NET Core Configuration API provides access to the Secret Manager secrets. User secrets configuration source is automatically added in development mode when the project calls CreateDefaultBuilder to initialize a new instance of the host with preconfigured defaults. CreateDefaultBuilder calls AddUserSecrets when the EnvironmentName is Development. You may also explicitly add the user secret configurations if CreateDefaultBuilder isn’t called. You must call AddUserSecrets in the Startup constructor only when the application runs in the Development environment.

What your code looks like on your Program.cs class file:

Contains what you need to define to read the connection string from the secrets.json file

What your code looks like on your Startup.cs class file:

Contains code to get the connection string

Summary

We must protect our application secrets during development by keeping our application secrets from being committed within the code to your repository. Manage User Secrets is a great alternative to environment variables. The structure matches that of appsettings.json files that's why it’s easy to use. It is a great feature in ASP.NET Core and makes individual settings easily accessible. Also note that if you copy the source code to another machine secrets.json file will be empty and you need to specify your own credentials or app secret there.

Next Steps

To learn more about managing user or app secrets I invite you to make time to read the following tips from the other authors:



Last Updated: 2020-03-05


get scripts

next tip button



About the author
MSSQLTips author Sherlee Dizon Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


Recommended Reading

Working with SQL Server Stored Procedures and .NET

Data Entry for SQL Server - building quick, efficient data input forms using InfoPath

Connecting a Java Program to SQL Server

Querying SQL Server Tables from .NET

SQL Server Time Series Data Visualization with Grafana





get free sql tips
agree to terms


Learn more about SQL Server tools