In an enterprise development or deployment effort, we often see a number of clients hitting databases set up for different environments. These clients may be distributed on web servers, admin consoles, reporting servers and others. Generally, developers and architects go crazy maintaining connection strings. This problem is generally circumvented by creating too many logins in the database with restrictive access, copying the same logins and passwords to different environments to make life easier, switching roles in the database or hard coding the connection string in the code - aggravating the issue. If there are a number of environments, the matter gets even worse. Microsoft suggests putting these connection strings in config files and other places. But how secure is it to place this information in the open? If all this sounds too familiar, we all are sailing in the same boat :-)
The following article discusses a simple strategy to maintain and distribute connection strings securely.
An administrator selects a server and uses its registry to host all the connection strings. Every environment, like the system test, development or staging environment can be registered on the server using an admin console shown below.
Start the admin console by using the following command: TestECS.exe -admin
The component, EnterpriseConnString.dll, provided with this article and a keyword set by an administrator, access registry keys on the server which contain the encrypted secure connection strings. The client has to connect to the server and ask for a connection string by an environment which is demonstrated by the following code:
EntConnString connstr = new EntConnString("196.783.45.56");
string connectionString = connstr.GetConnectionString("SystemTest");
A test client is provided with the attached code which can be run using the following command line: TestECS.exe -client. The program runs as shown below:
Windows registry on a server is used to maintain encrypted connection strings. The
EnterpriseConnectionString component acts as a bridge between the client and the host server. The component contains 3 classes:
- the encryption/decryption class -
EnterpriseConnString.Cypher, uses any crypto provider of choice to encrypt/decrypt the connection strings (I have used DES for demonstration purposes).
EnterpriseConnString.Keyword class retrieves a public key. Location of the public key and its retrieval by this class can be customized. The code provided with the article can read the key from a .NET config file or the local registry.
- the main class
EnterpriseConnString.EntConnString uses both classes mentioned above and deals with the registry keys on the server. This class can be used both by admins and clients. Security is built into the scheme by using Windows authentication and Registry access. An admin has write access to the server registry keys and a client is expected to have read only rights. Even if any client intentionally tries to use the library as an admin, it is expected that Windows authentication prevents the registry from being modified. It is the responsibility of an administrator to maintain this security.
An administrator can install the public key or keyword on every client using a config file or a registry file as deemed fit by the software architect. Using this public key, a set of connection strings for every environment can be created on the server. The clients use the ECS component and call the server using the environment as the index to retrieve the connection string. Here is an example config file: TestECS.exe.config used for the demo.
<ADD value="12345678" key="keyword">
To retrieve a connection string, a public key, a server name and the provided component (which contains the private key and the encryption algorithm) are required. In addition to this, the Windows login used by the client needs read permissions on the server to access the registry. The permissions on the registry can be set by using regedt32.exe.
The test scenario should test for 3 different types of Windows users.
- Administrators or users having write permissions should be able to create and read
- Windows users having only read permissions - should be able to read but not write
- Others - should not be able to create or read connection strings
Points of interest
To enhance the security, clients may be given a Windows login and password, which has access to the server registry. This makes the administration on the server easy, as all the users need not be added to a group. One set of Windows login and password will suffice. The code inside
EnterpriseConnString can be modified to impersonate as this login, when accessing the registry.