Encrypted Connection String Value

Josh Cossiboon asked on July 18, 2018 14:48

Is it possible to encrypt the sql credentials of a database connection string stored in the web.config?

Ideally, we prefer to encrypt the credentials and decrypt them in code prior to the connection to the database.

I understand we can use .NET standard encryption where we can encrypt a section of the web.config but this is not ideal based on the way we operate internally.

For example:

<add name="CMSConnectionString" connectionString="Data Source=SQLSERVER1;Initial Catalog=project_database;Integrated Security=False;Persist Security Info=False;User ID={EncryptedUserId};Password={EncryptedPassword};Connect Timeout=60;Encrypt=False;Current Language=English;" />

Recent Answers


Trevor Fayas answered on July 18, 2018 16:44

I would just follow these steps, windows will automatically decrypt the connection string using this method so you shouldn't have to do anything except encrypt it!

https://www.codeproject.com/Tips/795135/Encrypt-ConnectionString-in-Web-Config

0 votesVote for this answer Mark as a Correct answer

Josh Cossiboon answered on July 18, 2018 16:49

I am familiar with this but this is not ideal for our business model. We can implement this if there are no other options.

Ideally we would like to attach to a global event occurring prior to database open where we can decrypt the SQL creds.

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on July 18, 2018 17:05

You may be in for some hassle then.

You can hook into the OnGetConnection event, but you need to pass it a IDataConnection type object which may be a pain to implement. I couldn't find the default implementation:

    // Registers the custom module into the system
[assembly: RegisterModule(typeof(CustomInitializationModule))]

public class CustomInitializationModule: Module {
 // Module class constructor, the system registers the module under the name "CustomInit"
 public CustomInitializationModule(): base("CustomInit") {}

 // Contains initialization code that is executed when the application starts
 protected override void OnInit() {
  base.OnInit();
  CMS.DataEngine.DataConnectionFactory.OnGetConnection += DataConnectionFactory_OnGetConnection;
 }
 private IDataConnection DataConnectionFactory_OnGetConnection(string connectionString) {
  return new MyDataConnection(connectionString);
 }
}
public class MyDataConnection: IDataConnection {
 public MyDataConnection(string connectionString) {
  // stuff here
 }
 public int CommandTimeout {
  get =>
   throw new System.NotImplementedException();
  set =>
   throw new System.NotImplementedException();
 }
 public bool UseScopeConnection {
  get =>
   throw new System.NotImplementedException();
  set =>
   throw new System.NotImplementedException();
 }
 public bool KeepOpen {
  get =>
   throw new System.NotImplementedException();
  set =>
   throw new System.NotImplementedException();
 }
 public IDbConnection NativeConnection {
  get =>
   throw new System.NotImplementedException();
  set =>
   throw new System.NotImplementedException();
 }
 public IDbTransaction Transaction {
  get =>
   throw new System.NotImplementedException();
  set =>
   throw new System.NotImplementedException();
 }

 public string ConnectionStringName =>
  throw new System.NotImplementedException();

 public string ConnectionString =>
  throw new System.NotImplementedException();

 public bool DisableConnectionDebug {
  get =>
   throw new System.NotImplementedException();
  set =>
   throw new System.NotImplementedException();
 }
 public bool DisableQueryDebug {
  get =>
   throw new System.NotImplementedException();
  set =>
   throw new System.NotImplementedException();
 }

 public void BeginTransaction() {
  throw new System.NotImplementedException();
 }

 public void BeginTransaction(IsolationLevel isolationLevel) {
  throw new System.NotImplementedException();
 }

 public void BulkInsert(DataTable sourceData, string targetTable, BulkInsertSettings insertSettings = null) {
  throw new System.NotImplementedException();
 }

 public void Close() {
  throw new System.NotImplementedException();
 }

 public void CommitTransaction() {
  throw new System.NotImplementedException();
 }

 public void Dispose() {
  throw new System.NotImplementedException();
 }

 public int ExecuteNonQuery(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, bool requiresTransaction) {
  throw new System.NotImplementedException();
 }

 public Task < int > ExecuteNonQueryAsync(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, bool requiresTransaction, CancellationToken cancellationToken) {
  throw new System.NotImplementedException();
 }

 public DataSet ExecuteQuery(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, bool requiresTransaction) {
  throw new System.NotImplementedException();
 }

 public DbDataReader ExecuteReader(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, CommandBehavior commandBehavior) {
  throw new System.NotImplementedException();
 }

 public Task < DbDataReader > ExecuteReaderAsync(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, CommandBehavior commandBehavior, CancellationToken cancellationToken) {
  throw new System.NotImplementedException();
 }

 public object ExecuteScalar(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, bool requiresTransaction) {
  throw new System.NotImplementedException();
 }

 public Task < object > ExecuteScalarAsync(string queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, bool requiresTransaction, CancellationToken cancellationToken) {
  throw new System.NotImplementedException();
 }

 public IDataConnection GetExecutingConnection(string connectionStringName, bool newConnection = false) {
  throw new System.NotImplementedException();
 }

 public string GetXmlSchema(string tableName) {
  throw new System.NotImplementedException();
 }

 public bool IsOpen() {
  throw new System.NotImplementedException();
 }

 public bool IsTransaction() {
  throw new System.NotImplementedException();
 }

 public bool NativeConnectionExists() {
  throw new System.NotImplementedException();
 }

 public bool NativeDBConnectionExists() {
  throw new System.NotImplementedException();
 }

 public void Open() {
  throw new System.NotImplementedException();
 }

 public void RollbackTransaction() {
  throw new System.NotImplementedException();
 }
}
0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on July 18, 2018 17:13

Additionally, i did find the GeneralConnection, but you may or may not be able to use it...

private IDataConnection DataConnectionFactory_OnGetConnection(string connectionString)
    {
        // Decrypt and pass new connection string?
        return new MyDataConnection(connectionString);
    }

+

    public class MyDataConnection : GeneralConnection
{
    public MyDataConnection(string connectionString) : base(connectionString)
    {

    }
}
0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.