Introduction
I write code in an ecosystem that involves four "environments", each of which exist in one of two "areas". For the math challenged out there, that makes eight environments, each of which connects to its own database server. Of less significance is that we support over a dozen web applications, at least seven windows services, and several desktop applications that run on the web and database servers within those environments. Currently, we have to maintain dozens of config files that contain the connection strings for the database.
Deployments are messy because we have to create a deployment package for each app/environment/area combination, and having to do that introduces the possibility of having an app work in one environment/area, but not in another. I don't think I have to tell anyone here that the possibility of something going sideways means that something will eventually, in fact, go sideways, and at the worst possible time. This will cause extensive regression testing as the deployment proceeds through the dev, test, acceptance, production lifecycle, causing life to suck on a massive scale.
We've begun transitioning to the cloud, and the project manager wants us to use a single deployment package on all of the environments, so we had to explain to him that the fact that the connection strings being contained in config files kept us from doing that. After that discussion, I decided to pull some code out of a planned rewrite of our flagship project and massage/tweak it to support our multi-environment/area/application ecosystem. This article is the result of that work.
This library is intended for use in a multi-environment system (such as the one I've described), but it certainly be used under less stringent environmental confines, even down to a single desktop machine taht needs to connect to a single database server. However, this article is aimed squarely at the developers who write code in those multi-environment systems.
Finally, this code (as presented) only supports MS SQL Server connection strings. If you're using any other database system, you will probably only have to change the PWConnectionString class in order to adapt it for your use. The last time I worked with anything other than MS SQL Server was 2000-2001, so I will be less than useful in helping you convert it to other databases. However, I will try my best to provide a sympathetic ear to those of you that need to perform such a conversion. If you provide your conversion code, I might even augment this article with it (with the appropriate call-outs to the author in said code).
Note: - there are no screen shots because this code does not require an interface in order to implement it. There is a WPF app in the sample project that makes it easy to generate encrypted user id's and passwords if you need/want to use that functionality in the PWConnectionStringMgr system, but it's not needed to actually implement the code.
Features
The following capabilities are implemented in the PWConnectionStringMgr class:
- UPDATE 2021.03.06 - Support for MS Sql Server, Oracle, and PostgreSQL connection strings. Just select the appropriate connection string class for your particular situation.
- Support for an unlimited number of possible developer-defined environments.
- Support for all application types (web, desktop, windows service).
- The ability to choose no data obfuscation, where connection string data is in plain text while at rest and during transport.
- The ability to use Base64 encoding for connection strings during transport
- The ability to use 256-bit Rijndael encryption for connection strings during transport (this is the default behavior)
- Allow each app within an environment to define its connection strings via a callback method.
- Allow the user id and password to be encrypted at rest. This means that you can actually START with an encrypted userID and password, so that they never exist in your source code as plain text.
- Complete elimination of the need for config files to store connection strings.
One limitation of this code is that it only supports a single database server per environment. If you need to support more than that, you're going to have to get creative with how you define your environments, as well as how you determine your application host. It should be fairly obvious where and how the creativity has to happen once you've dug deeper into this article.
Usage
After removing the proprietary data, I was left with a chunk of code that requires what I consider to be a not insignificant amount of work in order to implement the library in your own code. Unfortunately, it cannot be helped, and you'll see why as we progress through this section. Some of the steps may be quite complex in terms of description, so there will be sub-sections following the basic list of implementation steps. Example code shown below was extracted from the code attached to this article.
0) Add the PWConnectionString project to your own solution(s). Of course you could just copy the code into your own common assembly solution, but just using the assembly provided may be simpler and more tidy.
1) Open the PWConnectionStringMgr.cs file, and add the necessary code to the PopulateServerMap() method (discussed in detail below).
2) In your application(s), create the required callback method that specifies the application's connection string(s) for that environment.
3) In your application(s), add code to determine the host name, and instantiate the PWPWConnectionStringMgr object.
Populating the Server Map (Step 1)
This library works on the premise that every application (be it web or desktop) can independently identify it's host. A web app can use the host part of its URL, and a desktop application can use its IP address, machine name, and/or its ActiveDirectory domain name. The premise also extends to the assumption that a given URL host name, or desktop machine is operating within a well-defined and segregated environment.
In order to actually make this code useful, it is the job of the developer to hard-code the identifiable environments in his/her ecosystem. This is done by adding code to the PopulateServerMap method.
private static void PopulateServerMap()
{
// Environments are constrained by the host
// Since we're using a HashSet, we can't possibly insert items with duplicate
// host names into the collection.
// if you encrypt your userid and pass, do not specify a passphrase. This
// will allow the encryption routine to use the DefaultSessionID. The reason
// is that we don't save the passphrase in the ServerMapItem object.
// Example below:
string testuid = "John";
string testpwd = "12345";
//testuid = testuid.Encrypt();
//testpwd = testpwd.Encrypt();
PWConnectionStringMgr.ServerMap
.Add(new ServerMapItem("MARSVM",
"MARSVMDB",
Environment.Dev,
ClassLevel.Unclass,
testuid,
testpwd));
}
As you can see by the comments, the server map is represented as a HashSet, which prevents duplicate entries from being added. This means that you can call this method over and over again, and using the example above, you will never have more than one entry in the collection with the hostname of "MARSVM". Please refer to the The Code section below for an explanation of the ServerMapItem class.
You can add as many ServerMapItem items as is necessary to define your available environments
The CallBack Method (Step 2)
When the manager is instantiated, it calls the callback method that you pass into the manager's constructor. This mechanism is used to avoid having the developer put application-specific code into the manager class. This also serves the purpose of not having to recompile the manager class or take a chance on messing up the core code therein.
The callback method is implemented somewhere in your application's code. It can be anywhere, but I like to put in in the same code file where the manager is instantiated (personal preference). You can give the method any name that might suit you. It must conform to the delegate prototype defined in the PWConnectionStringMgr.cs file:
// Every app that uses the PWConnectionStringMgr class needs to provide a callback method that
// will allow that app to define its own connection strings. This keeps this code from needing
// significant changes when a new environment is added to the server map collection.
public delegate void CreateConnectionStringCallBack(string sessionID,
ServerMapItem server,
List<PWAbstractConnectionString> list);
The sample app implements its version of this method as follows:
public static void BuildMyConnectionStrings(string sessionID, ServerMapItem server, List<PWConnectionString> list)
{
// To create a connection string object, do something like this:
// if you want to encrypt uid/password at rest, it is advised that you use
// the default passphrase defined in the ExtendConnectionString class (this
// is done by calling the Encrypt method without specifying a passphrase.
// Of course, feel free to substitute this default value if desired.
// the following simplify typing
string theS = server.DbServer;
string theU = server.TheU;
string theP = server.TheP;
// DO NOT encrypt the userID/password here. That's done in the
// PWConnectionStringMgr.PopulateServerMap method.
// for the purposes of example, we're creating four connection strings that
// will allow connection to the indicated catalogs.
list.Add(new MsSqlConnectionString("ConsoleApp1", theS, "MyDatabase", theU, theP, false, true, sessionID));
list.Add(new MsSqlConnectionString("Users" , theS, "Users" , theU, theP, false, true, sessionID));
list.Add(new MsSqlConnectionString("Common" , theS, "Common" , theU, theP, false, true, sessionID));
list.Add(new MsSqlConnectionString("Invoices" , theS, "Invoices" , theU, theP, false, true, sessionID));
}
Instantiating the Connection String Manager (Step 3)
Before you can instantiate the manager you must identify your app and it's host environment.
Typically, for a web app that's deployed to from dev to test to QA (acceptance) and finally to production, the servers on which they're running are on different domains. So, in each case, you have the following possible (highly simplified) example urls:
- https://mycompany.dev/myapp.aspx
- https://mycompany.test/myapp.aspx
- https://mycompany.staging/myapp.aspx
- https://mycompany.com/myapp.aspx
It is assumed that you populated your server map with something like the following. Notice there's a mapped server for every environment:
ClassLevel lvl = ClassLevel.Unclass;
ServerMap.Add(new ServerMapItem("nycompany.dev" , "pcdbvmdev" , Environment.Dev , lvl, "dev_userid" , "dev_password" );
ServerMap.Add(new ServerMapItem("nycompany.test" , "pcdbvmtest" , Environment.Text , lvl, "test_userid" , "test_password" );
ServerMap.Add(new ServerMapItem("nycompany.staging", "pcdbvmstaging", Environment.Staging, lvl, "stage_userid", "stage_password");
ServerMap.Add(new ServerMapItem("nycompany.com" , "pcdbvmprod" , Environment.Prod , lvl, "prod_userid" , "prod_password" );
For desktop applications, it could get a bit tricky, but it's certainly doable. There are many ways to establish the host identity, include the machine name, the IP address, or even the Active Directory network name. You could even use non-app-specific config files that contain some sort of environment-oriented definitions. The possible permutations of identifying info are many and varied, and all I can do here is make suggestions about how you might approach the task. For the purposes of example, the sample application presented here makes use of the machine name.
string host = System.Environment.MachineName;
Another pre-requisite for instantiating the manager is to come up with what I euphemistically call a "session id". What this value actually represents in the pass phrase used to encrypt/decrypt connection strings. Personally, I like to use guids, because every time you generate one, it's unique, making the encryption even more random and difficult to break.
// the sessionID is used as the passphrase to encrypt/decrypt connection strings
string sessionID = Guid.NewGuid().ToString().Replace("-","").MangleSessionID();
You may have noticed the call to MangleSessionID(). This method is used to alternate the characters between upper and lower case, and then maybe reversing the string. It's a kind of over-the-top extra step to make the pass phrase more difficult to discern at any given point in the process. It's totally unnecessary, but it was fun to code. You certainly don't have to use it.
Finally, you're at the point where you're ready to actually instantiate the manager.
try
{
// Note that creating an instance of the manager will throw an exception if the
// specified host isn't found in the server map (thus the encapsulating
// try/catch block).
PWConnectionStringMgr connStrings = new PWConnectionStringMgr(sessionID, BuildMyConnectionStrings, host);
// once the manager's connection string list has been created, you need to add it
// to your session variables (if you're writing a web app). It won't be
// too big of an object since most apps generally create fewer than 10 connection
// strings. It's recommended that you don't put this list into a static class if
// you're writing a web app because it will be visible to all requests by all
// users.
//-------------------------------------------------------
// the following code is simply for testing for testing
StringBuilder encodedText = new StringBuilder();
StringBuilder decodedText = new StringBuilder();
foreach(PWAbstractConnectionString connStr in connStrings)
{
encodedText.AppendLine(connStr.ConnectionString);
decodedText.AppendLine(connStr.ConnectionStringDecoded);
}
Console.WriteLine("Encoded text:");
Console.WriteLine(encodedText);
Console.WriteLine();
Console.WriteLine("Decoded text");
Console.WriteLine(decodedText);
Console.WriteLine();
Console.WriteLine("Press a key...");
Console.ReadKey();
}
catch (Exception ex)
{
// avoid compiler warning, but allow inspection of ex if necessary
if (ex != null) { }
// why an exception would be thrown:
// - the host you specified was not found in the server map. This
// means you need to add the host to the server map, recompile,
// and try again.
// - You specified that the user id and password were encrypted
// inside the connection string, but failed to encrypt those
// items in connection string manager when you added a given
// server map item. (this will generate a "salt not at least
// 8 characters" exception)
}
The Code
The code is presented below in decreasing order of dependency. This lets you become familiar with the objects being used before seeing the code that uses those objects. For the most part, the code is hyper-commented so I can just copy it into this article and let the comments replace external narrative text, but feel free to remove any comments you don't think you need when you incorporate this code into your own projects.
Encryption
The code in this section is ubiquitous to the point of being pointless in terms of the requirement for a narrative, beyond telling you where in the project it resides, and the type of implementation.
All encryption and encoding code is contained within the PWEncryption.cs file. The encryption used by this code implements a Rijndael (256-bit symmetric key) encryption scheme. The encryption/decryption code is implemented as a pair of string extension methods. My personal opinion is that since we're encrypting strings, it makes perfect sense do it this way. Like the encryption code, all of the Base64 encoding code is also implemented as a series of string extension methods.
The one aspect of the encryption code you should be aware of is that the extension method class contains a constant named _DEFAULT_SESSION_ID_. This value is used when you try to encrypt a string without specifying a pass phrase yourself. This is exactly what's done when you specify that the userid and password should be encrypted when at rest.
Web app caveat: Static code is shared by ALL requests, regardless of the session/user. For this reason, the _DEFAULT_SESSION_ID_ is defined as a constant to avoid allowing the code to change its value. Of course, desktop apps are not subject to this behavior, but since this is essential "cross-platform" code, the constraint is maintained across all platforms. This does not prevent you from changing the value to something you prefer, but the code it should not be changed to allow it to be altered during program execution.
.Net Core Caveat:(2021.03.05) .Net Core does not support the ability to specify the block size, so we have to use 128-bit blocks. The only way around this was to use something like BouncyCastle, and I didn't want to make this code dependent on an external NuGet package, so for .Net Core projects, it uses 128 bit blocks.
using System;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
namespace PWConnectionStringManager
{
public static class ExtendConnectionString
{
const string _DEFAULT_SESSION_ID_ = "D26B118F240D4552A9F9DD00A98EA333";
public static int Keysize { get; set; }
public static int DerivationIterations { get; set; }
static ExtendConnectionString()
{
ExtendConnectionString.Keysize = 256;
ExtendConnectionString.DerivationIterations = 1000;
}
public static string MangleSessionID(this string text)
{
int startPos = MakeRandomNumber(1,100) % 2;
char[] charText = text.ToArray();
for (int i = 0; i < charText.Length; i++)
{
charText[i] = (i % 2 == startPos) ? char.ToUpper(charText[i]) : char.ToLower(charText[i]);
}
text = new string(charText);
text = (startPos == 1) ? text.Reverse().ToString() : text;
return text;
}
public static int MakeRandomNumber(int min, int max)
{
Random random = new Random();
return random.Next(min, max);
}
public static string Base64Encode(this string text, Encoding encoding = null)
{
string value = string.Empty;
if (!string.IsNullOrEmpty(text))
{
encoding = (encoding == null) ? Encoding.UTF8 : encoding;
byte[] bytes = encoding.GetBytes(text);
value = Convert.ToBase64String(bytes);
}
return value;
}
public static string Base64Decode(this string text, Encoding encoding = null)
{
string value = string.Empty;
byte[] bytes;
if (!string.IsNullOrEmpty(text))
{
encoding = (encoding == null) ? Encoding.UTF8 : encoding;
try
{
bytes = Convert.FromBase64String(text);
value = encoding.GetString(bytes);
}
catch (Exception)
{
value = text;
}
}
return value;
}
public static string Encrypt(this string plainText, string passPhrase = null)
{
passPhrase = (string.IsNullOrEmpty(passPhrase)) ? _DEFAULT_SESSION_ID_ : passPhrase;
var saltStringBytes = Generate256BitsOfRandomEntropy();
var ivStringBytes = Generate256BitsOfRandomEntropy();
var plainTextBytes = Encoding.UTF8.GetBytes(plainText);
using (var password = new Rfc2898DeriveBytes(passPhrase, saltStringBytes, DerivationIterations))
{
var keyBytes = password.GetBytes(Keysize / 8);
using (var symmetricKey = new RijndaelManaged())
{
symmetricKey.BlockSize = 256;
symmetricKey.Mode = CipherMode.CBC;
symmetricKey.Padding = PaddingMode.PKCS7;
using (var encryptor = symmetricKey.CreateEncryptor(keyBytes, ivStringBytes))
{
using (var memoryStream = new MemoryStream())
{
using (var cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write))
{
cryptoStream.Write(plainTextBytes, 0, plainTextBytes.Length);
cryptoStream.FlushFinalBlock();
var cipherTextBytes = saltStringBytes;
cipherTextBytes = cipherTextBytes.Concat(ivStringBytes).ToArray();
cipherTextBytes = cipherTextBytes.Concat(memoryStream.ToArray()).ToArray();
memoryStream.Close();
cryptoStream.Close();
return Convert.ToBase64String(cipherTextBytes);
}
}
}
}
}
}
public static string Decrypt(this string cipherText, string passPhrase = null)
{
passPhrase = (string.IsNullOrEmpty(passPhrase)) ? _DEFAULT_SESSION_ID_ : passPhrase;
var cipherTextBytesWithSaltAndIv = Convert.FromBase64String(cipherText);
var saltStringBytes = cipherTextBytesWithSaltAndIv.Take(Keysize / 8).ToArray();
var ivStringBytes = cipherTextBytesWithSaltAndIv.Skip(Keysize / 8).Take(Keysize / 8).ToArray();
var cipherTextBytes = cipherTextBytesWithSaltAndIv.Skip((Keysize / 8) * 2)
.Take(cipherTextBytesWithSaltAndIv.Length - ((Keysize / 8) * 2))
.ToArray();
using (var password = new Rfc2898DeriveBytes(passPhrase, saltStringBytes, DerivationIterations))
{
var keyBytes = password.GetBytes(Keysize / 8);
using (var symmetricKey = new RijndaelManaged())
{
symmetricKey.BlockSize = 256;
symmetricKey.Mode = CipherMode.CBC;
symmetricKey.Padding = PaddingMode.PKCS7;
using (var decryptor = symmetricKey.CreateDecryptor(keyBytes, ivStringBytes))
{
using (var memoryStream = new MemoryStream(cipherTextBytes))
{
using (var cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read))
{
var plainTextBytes = new byte[cipherTextBytes.Length];
var decryptedByteCount = cryptoStream.Read(plainTextBytes, 0, plainTextBytes.Length);
memoryStream.Close();
cryptoStream.Close();
return Encoding.UTF8.GetString(plainTextBytes, 0, decryptedByteCount);
}
}
}
}
}
}
private static byte[] Generate256BitsOfRandomEntropy()
{
var randomBytes = new byte[32];
using (var rngCsp = new RNGCryptoServiceProvider())
{
rngCsp.GetBytes(randomBytes);
}
return randomBytes;
}
}
}
ServerMapItem
The ServerMapItem class allows us to define servers that, in turn, identitify the environment in which they exist. The theory used here is that a given web app's URL is dfferent when that web app is running in different environments, so they should be uniquely identifiable when running on a test server, or on a production server.
Desktop apps don't have URLs, so you have to come at it from a different direction. If you want to go by what the .Net framework provides, there are numerous methods you can use to determine a machine's name or IP address. In point of fact, you could even use this method for web applications as well.
A third method for uniquely identifying a given machine is to use the serial number from machine or root certificate. This means that a given server on the network MUST have a machine certificate, but keep in mind that certificates have limited life spans, so you'll have to get new certs and change your code to get the new certs' serial numbers every few years.
The last method I can come up with is a machine config file that identifies the machine based on the environment it's in. This is actually the most reliable way to do it, IMHO, because the file always exists (or at least should), and it always contains the expected data (or at least it should). You can then write a common method to retrieve the data in this file and use that method in all of your apps. The only trick involved is that you have to be able to convince your system admins to put the appropriate file on each server.
Update 2021.03.06 - When I added support for Oracle connection strings, I had to add two new parameters to the constructor to support proxy credentials. While they're default credentials, you need to know that if you encrypt your database credentials, you MUST ALSO encrypt your proxy credentials. It's all encrypted, or nothing is encrypted (as far as at-rest credentials are concerned).
public class ServerMapItem : IEquatable<ServerMapItem>
{
#region IEquality implementation
public override int GetHashCode()
{
return this.Host.GetHashCode();
}
public bool Equals(ServerMapItem otherItem)
{
return this.Host.ToLower() == otherItem.Host.ToLower();
}
#endregion IEquality implementation
public string Host { get; set; }
public string DbServer { get; set; }
public Environment Environment { get; set; }
public ClassLevel Classification { get; set; }
public string TheU { get; set; }
public string TheP { get; set; }
public ServerMapItem(string host, string dbServer, Environment env,
ClassLevel classLevel=ClassLevel.Unclass,
string theU="", string theP=""
string theProxyU="", string theProxyP="")
{
this.Host = host;
this.DbServer = dbServer;
this.Environment = env;
this.Classification = classLevel;
this.TheU = theU;
this.TheP = theP;
}
public override string ToString()
{
return string.Format("{0}/{1} host={2} db={3}", this.Environment.ToString(), this.Classification.ToString(), this.Host, this.DbServer);
}
}
UPDATE 2021.03.06 - PWAbstractConnectionString
If you used prior versions of this code, please re-read this section thoroughly.
In the update cited in the section title, I refactored the connection string object so that it was abstract. This allowed me to create derived classes for the following specified databases - MS Sql Server, Oracle and PostgreSQL. You should be aware that I have almost no expereience with the latter two (the last time I did any Oracle stuff was 2010, and PostGre was 2001 or so), and I merely googled the connection string properties for them. I'm also not inclined to install either database system on my machine in order to test them, so CHECK THEM BEFORE YOU USE THEM TO MAKE SURE THEY'RE CORRECT.
As you might guess, the PWAbstractConnectionString class is the basis for expected product of the code. The idea is to have the connection string exist in its component parts (instance name, catalog, credentials, etc), and only present a complete connection string when requested. Optionally (and by default), the assembled connection string is presented in encrypted form. I recognize that this code is most likely to be retrofitted into already running projects, and those projects utilize actual connection *strings*, but if you're starting a new solution, there's no reason you can't actually pass a PWAbstractConnectionString-derived object to your DAL code and let it handle the decryption completely out of site of the calling code.
using System;
using System.Text;
namespace PWConnectionStringManager
{
public class PWAbstractConnectionString
{
public string SessionID { get; set; }
public bool EncryptTraffic { get; set; }
public string Name { get; set; }
public string Server { get; private set; }
public string Database { get; private set; }
protected string UserID { get; set; }
protected string Password { get; set; }
protected bool UPAreEncoded { get; set; }
#region virtual properties
public virtual string ConnectionStringDecoded
{
get { return this.ConnDecode(this.ConnectionString, this.SessionID); }
}
protected virtual bool IsValid
{
get
{
return (
!string.IsNullOrEmpty(this.Server) &&
!string.IsNullOrEmpty(this.Database) &&
(
(!string.IsNullOrEmpty(this.UserID) && !string.IsNullOrEmpty(this.Password)) ||
(string.IsNullOrEmpty(this.UserID) && string.IsNullOrEmpty(this.Password))
)
);
}
}
protected virtual string Credentials
{
get
{
string value = "Integrated Security=";
if (string.IsNullOrEmpty(this.Password) && string.IsNullOrEmpty(this.UserID))
{
value = string.Format("{0}true;", value);
}
else
{
value = string.Format("{0}false; user id={1}; password={2};",
value,
(this.UPAreEncoded) ? this.UserID.Decrypt() : this.UserID,
(this.UPAreEncoded) ? this.Password.Decrypt() : this.Password);
}
return value;
}
}
protected virtual string WithEncryptedTraffic
{
get
{
string value = string.Empty;
if (this.EncryptTraffic)
{
value="Encrypt=true; TrustServerCertificate=true;";
}
return value;
}
}
protected virtual string InstanceAndPort
{
get
{
return string.Format("Server={0}{1};",
this.Server,
(this.Port == null) ? string.Empty
: string.Format(",{0}", this.Port));
}
}
#endregion virtual properties
#region abstract properties
public abstract string ConnectionString { get; }
#endregion abstract properties
public PWAbstractConnectionString(string name, string server, string database,
string uid, string pwd, bool upAreEncoded,
bool encryptTraffic, string sessionID,
ConnEncodeType encodingType)
{
if (string.IsNullOrEmpty(name )) { throw new ArgumentNullException("name" ); }
if (string.IsNullOrEmpty(server )) { throw new ArgumentNullException("server" ); }
if (string.IsNullOrEmpty(database)) { throw new ArgumentNullException("database"); }
this.Name = name;
this.Server = server;
this.Database = database;
this.UserID = uid;
this.Password = pwd;
this.UPAreEncoded = upAreEncoded;
this.EncryptTraffic = encryptTraffic;
this.SessionID = sessionID;
this.EncodingType = encodingType;
}
public override string ToString()
{
return this.ConnectionString;
}
protected string FormatValue(object value, string propertyName)
{
return (value == null) ? string.Empty : string.Format("{0}={1};", propertyName, value.ToString());
}
#region encoding/encrypting
public string ConnEncode(string text, ConnEncodeType encodeType, string passPhrase=null)
{
string value = text;
switch (encodeType)
{
case ConnEncodeType.Base64 : value = value.Base64Encode(Encoding.UTF8); break;
case ConnEncodeType.Encrypt : value = text.Encrypt(passPhrase); break;
default : break;
}
return value;
}
public string ConnEncode(string text, Encoding encoding)
{
return text.Base64Encode(encoding);
}
public string ConnEncode(string text, string passPhrase)
{
return text.Encrypt(passPhrase);
}
public string ConnDecode(string text, ConnEncodeType encodeType, string passPhrase=null)
{
string value = text;
switch (encodeType)
{
case ConnEncodeType.Base64 : value = value.Base64Decode(Encoding.UTF8); break;
case ConnEncodeType.Encrypt : value = text.Decrypt(passPhrase); break;
default : break;
}
return value;
}
public string ConnDecode(string text, Encoding encoding)
{
return (EncodingType == ConnEncodeType.Base64) ? text.Base64Decode(encoding) : text;
}
public string ConnDecode(string text, string passPhrase)
{
return (EncodingType == ConnEncodeType.Encrypt) ? text.Decrypt(passPhrase) : text;
}
#endregion encoding/encrypting
#if _USING_ENTITY_FRAMEWORK_
public string ConnectionStringWithMetadata
{
get { return this.BuildEntityConnectionString(this.Name, this.ConnectionString); }
}
public string BuildEntityConnectionString(string name, string shortConnString)
{
string providerName = "System.Data.SqlClient";
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(shortConnString);
string providerString = sqlBuilder.ToString();
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
entityBuilder.Provider = providerName;
entityBuilder.ProviderConnectionString = providerString;
entityBuilder.Metadata = String.Format("res://*/{0}{1}.Model{0}{1}.csdl|res://*/{0}{1}.Model{0}{1}.ssdl|res://*/{0}{1}.Model{0}{1}.msl", name, this.dbNamePostfix);
return entityBuilder.ToString();
}
#endif
}
}
Database-specific Connection String Classes
There are currently three database specific connection string class provided. I have no desire to actually install anything on my dev box beyond SQL Server, so know that I haven't tested any of the foreign databases noted below for viability or appropriateness.
- MSSqlConnectionString - this class is for use with reasonably modern (2012 and higher) Microsoft SQL Server databases. Be aware that there is no ODBC provider or LocalDB support in the class, because I don't personally need that kind of support (yet). If you DO need it, I recommend that you create a new class that inherits this class, and add the desired support to it.
- Oracle - I don't know how old a version of Oracle that this code supports because I googled the connection string elements and put them in this class. Once again, I have not tested this in any way beyod verifying that the parameters are indeed added to the connection string when specified.
- PostgreSQL - Substuitue "PostgreSQL" for all instances of "Oracle" in the previous item, and there ya go.
The constructors for these connection string classes are pretty much identical, except for oracle which allows you to specify proxy credentials 9userid and password. Other than that, they're identical.
The additional "non-standard" properties (that support the esoteric functionality of a given database type, and that are NOT included in the constructor parameter list) can be populated as needs as automatic properties, like so:
list.Add(new MSSqlConnectionString("Users", theS, "Users", theU, theP, false, true, sessionID){Port=1444,Async=True...});
PWConnectionStringMgr
The PWConnectionStringMgr wrangles all of the above into a cohesive object that allows you to instantiate it, and get back the connection string(s) appropriate for the environment in which the application is running. I assume you've read the Usage section, so here's where we'll see what's actually going on in the manager class.
using System;
using System.Collections.Generic;
using System.Linq;
namespace PWConnectionStringManager
{
public delegate void CreateConnectionStringCallBack(string sessionID, ServerMapItem server, List<PWAbstractConnectionString> list);
public partial class PWConnectionStringMgr : List<PWAbstractConnectionString>
{
public static HashSet<ServerMapItem> ServerMap = new HashSet<ServerMapItem>();
public CreateConnectionStringCallBack CreateMethod { get; set; }
public string SessionID { get; set; }
public ServerMapItem MappedServerItem { get; set; }
public PWConnectionStringMgr(string sessionID, CreateConnectionStringCallBack createMethod, string host)
{
if (string.IsNullOrEmpty(sessionID))
{
throw new ArgumentNullException("sessionID");
}
if (createMethod == null)
{
throw new ArgumentNullException("createMethod");
}
if (string.IsNullOrEmpty(host))
{
throw new ArgumentNullException("domain");
}
this.SessionID = sessionID;
this.CreateMethod = createMethod;
PWConnectionStringMgr.PopulateServerMap();
this.DetermineEnvironment(host);
this.CreateMethod(this.SessionID, this.MappedServerItem, this);
}
private static void PopulateServerMap()
{
string testuid = "John";
string testpwd = "12345";
PWConnectionStringMgr.ServerMap.Add(new ServerMapItem("MARSVM", "MARSVM",
Environment.Dev, ClassLevel.Unclass,
testuid, testpwd));
}
private void DetermineEnvironment(string host)
{
try
{
ServerMapItem server = ServerMap.First(x=>x.Host.ToLower() == host.ToLower());
this.MappedServerItem = server;
}
catch (Exception ex)
{
throw new Exception(string.Format("Server instance not found for domain \"{0}\"", host), ex);
}
}
public virtual PWConnectionString Get(string name)
{
if (string.IsNullOrEmpty(name))
{
throw new ArgumentNullException("The name parameter must be specified.");
}
PWAbstractConnectionString obj = this.FirstOrDefault(x => x.Name.ToUpper() == name.ToUpper());
if (obj == null)
{
throw new InvalidOperationException("Specified connection string is not available.");
}
return obj;
}
public virtual string GetAsString(string name)
{
string value = string.Empty;
PWAbstractConnectionString obj = this.Get(name);
if (obj != null)
{
value = obj.ConnectionString;
}
return value;
}
}
}
Points of Interest
A lot of the setup and implementation is on the developer implementing the code. This simply cannot be helped due a combination to the wildly different sys admin belief systems, network organizational schemes, security nazi restrictions, and other stuff that's quite simply out of my control. In my case, we have a well(?) defined organizational scheme and network infrastructure. We can count on certain "things" being true with a high percentage of regularity. I cannot divulge that organization, but I can and have inferred a reasonably generic method that allows pretty much anybody to implement this code. Knowing this, it only makes sense when I respond with, "I don't know", when you ask me a question about your infrastructure or organization.
It is entirely within reason to run tools from a database server, so yes, a mapped database server can refer to itself in the server map collection:
ServerMap.Add(new ServerMapItem("MARSVMDB", "MARSVMDB", Environment.Dev, ...));
Update 2021.03.06 - If you need to create a connection string class for an as-yet unspported database, I recommend that you use the OracleConnectionString class as a guide, becaus ethat class does all the normal stuff, but also overrides a couple of the base properties in order to add functionality that Oracle connection strings require.
Finally, you most definitely don't have restrict the use of this code to a multi-environment system. It is equally at home (if not a bit over-the-top) in a single environment setup, because it's basic aim was to eliminate the need to store connection strings in a config file. You don't even need to encrypt your connection strings if you don't feel the need.
Finally #2 - For .Net Core apps, I couldn't use 256-bit encryption, so I put a copiler directive in the encryption code that sets the "bitness" to 1298, but for .Net Core apps. For .Net framework, it still uses 256-bit encryption.
Standard JSOP Disclaimer
This is the latest in my series of articles that describe solutions to real-world programming problems. There is no theory pontification, no what-ifs, and no hypothetical claptrap discussed here. If you're looking for something that's ground-breaking, world-shaking or even close to cutting edge, I recommend that you seek out other reading material (the .Net Core fan boys seem to be pretty proud of themselves). I'm not known to be one that forges a path for others to follow (indeed, the only example I should serve is of what *not* to do), and will never claim that mine is the "one true way" (except where the evil that *is* Entity Framework is concerned). If this article doesn't spin your tea cups, by all means, move along, happy trails, and from the very depths of
History
- 2021.03.09 - Added a new download file that contains a connectuion string class for MySql 8.n. Sinply copy that file into the PWConnectionString project(s), and do a build.
- 2021.03.06 - At the suggestion of an early adopter (thanks pkfox!), I refactored the code to allow you to easily create database-specific connection string classes. Make sure you look at the sample console app where we add connection strings to the manager. Keep in mind that the Oracle and Postgre classes have NOT BEEN TESTED beyond making sure the specified elements are included in the connection string. Finally, I made all the changes to the .Net Framework version, and then transcribed them to the .Net Core Version. If something appears wonky when you run the Core version, simply find the differences and update the Core version code. (It compiles, so I'm shipping it.)
- 2021.03.05 - The following changes were made:
- Changed the ServerMap to be non-static
- I made doble-damn sure the startup app was ConsoleApp1.
- When using in .Net Core, the encryption can't use 256-bit encryption, so I changed it to 128-bit (for .Net Core only).
- For testing the encryption fix for .Net core, I had to create .Net core versions of the sample app and the DLL. I left those in the project for those of you that are using .Net Core.
- 2021.03.04 - Initial publication.
