Click here to Skip to main content
Click here to Skip to main content
Go to top

Strategy to distribute secure database connection strings in an enterprise environment

, 29 Nov 2003
Rate this:
Please Sign up or sign in to vote.
The article discusses a strategy to securely configure and administer a set of connection strings which can be maintained environment wise. It also talks about distributing this information securely in a huge environment to be used by authorized clients only.

Introduction

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 Smile | :)

The following article discusses a simple strategy to maintain and distribute connection strings securely.

Strategy

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

ECS Admin Console

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");
connstr.Connect();
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:

Sample ECS client

Design

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).
  • the 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.

Administration

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.

<?xml version="1.0" encoding="utf-8"?>
<CONFIGURATION>
<APPSETTINGS>
<ADD value="12345678" key="keyword">
</APPSETTINGS>
</CONFIGURATION>

Security details

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.

Testing scenarios

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Sriram Chitturi
Architect
United States United States
No Biography provided

Comments and Discussions

 
GeneralHELP! Pinmemberdegolove18-May-04 16:43 
GeneralIssue in Reading Connection String - URGENT PinmemberGopal Thiruvengadam30-Dec-03 7:55 
GeneralRe: Issue in Reading Connection String - URGENT PinmemberSriram Chitturi30-Dec-03 11:23 
GeneralRe: Issue in Reading Connection String - URGENT PinmemberGopal Thiruvengadam30-Dec-03 14:40 
GeneralRe: Issue in Reading Connection String - URGENT PinmemberSriram Chitturi31-Dec-03 11:33 
If you are using DES make sure that your DES IV vector and the keyword are exactly 8 bytes.

 
Sriram Chitturi
GeneralA major performance hit PinsussAnonymous27-Dec-03 7:42 
GeneralRe: A major performance hit PinmemberSriram Chitturi28-Dec-03 3:20 
GeneralRe: A major performance hit PinsussHnidk28-Dec-03 4:16 
GeneralRe: A major performance hit PinmemberSriram Chitturi29-Dec-03 6:18 
GeneralRe: A major performance hit PinmemberGuditi19-Jun-04 20:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140905.1 | Last Updated 30 Nov 2003
Article Copyright 2003 by Sriram Chitturi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid