Click here to Skip to main content
15,867,308 members
Articles / Desktop Programming / MFC
Article

Strategy to distribute secure database connection strings in an enterprise environment

Rate me:
Please Sign up or sign in to vote.
4.75/5 (11 votes)
29 Nov 20034 min read 78.2K   1.1K   30   10
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 :-)

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:

C#
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
<?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


Written By
Architect
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralHELP! Pin
degolove18-May-04 16:43
degolove18-May-04 16:43 
GeneralIssue in Reading Connection String - URGENT Pin
Gopal Thiruvengadam30-Dec-03 7:55
Gopal Thiruvengadam30-Dec-03 7:55 
Hi Sriram,

Indeed it was a great feature that you built. Though i had a small issue. May be its a mistake on my end or so.. i am not sure. I connect to the server and get all connection strings. Every connection string got appended with some "\0Bz!fdgdg " something like this or different. Whats the cause of this? Is it a permission issue or Cypher program issue or some characters in a DB Connection string (which i assume may not be the issue)..

Let me know the issue

Gopal
GeneralRe: Issue in Reading Connection String - URGENT Pin
Sriram Chitturi30-Dec-03 11:23
Sriram Chitturi30-Dec-03 11:23 
GeneralRe: Issue in Reading Connection String - URGENT Pin
Gopal Thiruvengadam30-Dec-03 14:40
Gopal Thiruvengadam30-Dec-03 14:40 
GeneralRe: Issue in Reading Connection String - URGENT Pin
Sriram Chitturi31-Dec-03 11:33
Sriram Chitturi31-Dec-03 11:33 
GeneralA major performance hit Pin
Anonymous27-Dec-03 7:42
Anonymous27-Dec-03 7:42 
GeneralRe: A major performance hit Pin
Sriram Chitturi28-Dec-03 3:20
Sriram Chitturi28-Dec-03 3:20 
GeneralRe: A major performance hit Pin
Hnidk28-Dec-03 4:16
sussHnidk28-Dec-03 4:16 
GeneralRe: A major performance hit Pin
Sriram Chitturi29-Dec-03 6:18
Sriram Chitturi29-Dec-03 6:18 
GeneralRe: A major performance hit Pin
Guditi19-Jun-04 20:36
Guditi19-Jun-04 20:36 

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

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