For a customer I needed to customize the way an Entity Framework 6 database-first project connects to a SQL Server database. Normally you specify the connectionstring including some metadata about the model in the app.config file. You can choose either to use Windows Authentication or SQL Authentication. If you want to use SQL Authentication you must provide the username and password. These are by default not encrypted and my customer did not like the idea that users who have access to the application can open the configuration file with for example Notepad, and can read and in a worst scenario misuse these credentials. To help secure information in configuration files there are alternatives, but all are very hard to implement and will still ask you to write custom code for Entity Framework. As often, more ways lead to Rome, but I came with the following solution. I build manually the connectionstring in the constructor of the DbContext. First I added these keys in the section of the config file. You can choose your own encryption method to encrypt one or more keys.
<appsettings> <add key="ServerName" value="MyServerName\MyInstanceName"></add> <add key="DatabaseName" value="MyDatabaseName"></add> <add key="UserID" value="MyEncryptedUserID"></add> <add key="Password" value="MyEncryptedPassword"></add> </appsettings>
In a database-first scenario the file containing the DbContext will be generated each time you change the model. That’s the reason why you have to add a new file with contains the other part of your DbContext class. Otherwise all changes made to the file will be lost every time the model change. Given the fact that my context has the name MyDbContext, I added a file with name MyDbContextPartial.vb and added the following code. Because the first line in the constructor must be the MyBase.New() call, we have to use a shared method to retrieve the details of the connectionstring. The connectionstring is actually not a regular connectionstring as you may expect on first sight. It is an EntityConnectionString, which contains also metadata about the used model. You can find this information in your current app.config file.
Imports System.Configuration Imports System.Data.Entity.Core.EntityClient Imports System.Data.SqlClient Partial Public Class MyDbContext Public Sub New() MyBase.New(GetEntityConnectionString()) End Sub Private Shared Function GetEntityConnectionString() As String Dim entityConnectionStringBuilder As New EntityConnectionStringBuilder() With entityConnectionStringBuilder .Provider = "System.Data.SqlClient" .ProviderConnectionString = GetProviderConnectionString() .Metadata = "res://*/EntitiesModel.csdl|" & "res://*/EntitiesModel.ssdl|" & "res://*/EntitiesModel.msl" End With Return entityConnectionStringBuilder.ToString() End Function Private Shared Function GetProviderConnectionString() As String Dim userID = DecryptText(ConfigurationManager.AppSettings("UserID")) Dim password = DecryptText(ConfigurationManager.AppSettings("Password")) Dim sqlConnectionStringBuilder As New SqlConnectionStringBuilder() With sqlConnectionStringBuilder .DataSource = ConfigurationManager.AppSettings("ServerName").ToString() .InitialCatalog = ConfigurationManager.AppSettings("DatabaseName").ToString() .IntegratedSecurity = False .UserID = userID .Password = password End With Return sqlConnectionStringBuilder.ToString() End Function End Class
In this example I’m using for decrypting the method DecryptText() which is a part of a common library I have build. However, you can use your own encryption- and decryption logic for this. On the Internet you can find a couple of libraries which can do these tasks for you. However, building and calling your custom connectionstring is not the only thing you have to do. You must also remove the constructor in the original MyDbContext class. That one is still referring to the name of the connectionstring in the app.config file. So the other part of the Partial Class MyDbContext ends up like this.
Imports System Imports System.Data.Entity Imports System.Data.Entity.Infrastructure Partial Public Class MyDbContext Inherits DbContext Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder) Throw New UnintentionalCodeFirstException() End Sub Public Overridable Property Customers() As DbSet(Of Customer) Public Overridable Property Orders() As DbSet(Of Order) End Class
Hopes this help you to implement your own custom connectionstring in combination with Entity Framework.