Solved

C# Connection String Storing

Posted on 2004-03-25
18
11,465 Views
Last Modified: 2012-05-04
Hello,

I currently have an application in which i am storing a connection string in a class.  I would like to not have to hardcode this into the class, but instead store it in some sort of dll or text file.  I have multiple clients on this system, and each time I make an update, i have to alter the connection string in the software, and compile for each separate location.

We thought of placing this into a text file, but we want to be able to hide this file, and not allow them to view the connection string.

Thanks for your help!
0
Comment
Question by:FTIISD
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 9

Expert Comment

by:Joeisanerd
Comment Utility
You could add a config file to the project and store the settings in there. Either as a connectionstring or seperated out like ServerName, DataBase, UserID, PWd

You can also encrypt the data stored.
0
 
LVL 9

Accepted Solution

by:
Joeisanerd earned 200 total points
Comment Utility
Sample config file

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <appSettings>
            <add key="SqlServerName" value="computername" />
            <add key="Database" value="MyDB" />
   </appSettings>
</configuration>

using System.Configuration;

string ServerName = "";
// Retrieve settings from the App.Config file
AppSettingsReader asr = new AppSettingsReader();
                        
// Read the value from the file
ServerName= asr.GetValue("ServerName",System.Type.GetType("System.String")).ToString();

// the AppSettingsReader will attemp to read the file called appname.exe.config from the app's working directory.
0
 
LVL 22

Assisted Solution

by:_TAD_
_TAD_ earned 100 total points
Comment Utility

If this is a web application you can store the connection string in the global.asa file I am quite certain that the users do not have access to this file.


If it is a windows app...


First, for security reasons you ever want to store a connection string in an assembly a client has access to (which you mentioned).  With a free tool from Microsoft you can view the connection string as text even though it is in a compiled dll.  Use the ILDASM.exe to do this (it's a very nifty tool, it comes with .Net sdk).

A config file works, but there again you are storing this as text somewhere.  You could store this information in an encrypted/obfuscated text file, but there again it is a text file on the user's machine.  Given enough time and access a user *can* crack any encryption.


The absolute best method I've seen is to programmatically create an ODBC client on the machine where the application is installed.  The username and password can be very basic (user/password) and published.  This ODBC connection goes out to the database (along with the user's *real* credentials) and authenticates it against the one or two views the user/password has access to.  You can then build a trigger or stored procedure to "Alter user_role" and change the login from user/password to JohnDoe/JohnsPwd and then continue from there.
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
You can use what Joeisanerd says and encrypt the connection string, thus preventing the user from seeing what value is actually in it.
0
 

Author Comment

by:FTIISD
Comment Utility
I am interested in creating the ODBC client on each machine.  I have gone this route before when using Visual Basic.  My problem is that this application is already running using a dataconnection class that returns a sqlConnection.  Is there anyway to incorperate this ODBC connection, replacing my connection string, but still allowing me to return the sqlConnection?

(if that makes any sense?)

thanks
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
sqlConnection will give enough performance improvement that I would think that it's better to figure a way to store the connection string and use sqlConnection directly, instead of slowing down and using odbc.  It's got more layers.
0
 
LVL 9

Expert Comment

by:Joeisanerd
Comment Utility
Are you trying to read the sqlserver name and database from the ODBC connection to created you SqlConnection String? You can do that by reading the registry under
local mahcine, software , ODBC, ODBC.INI, datasourcename
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


SRigney>  Say... Why is everyone so down on ODBC connections?  It's like a programmers mantra... ODBC connections are Soooo slow compared to OLEDB.  But all of the tests I have run have prooved out to be pretty much inconclusive.

Here's a few pages asking the same question:
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=18686

http://www.4guysfromrolla.com/webtech/070399-1.shtml


Anything less than a dozen concurrent connections there is NO noticable difference between DSN and DSN-less connections.  Even if you have over 60 concurrent connections to your database you will mere 10% improvement.  Hell, I've lost more cpu processes with bad programming structures.  My personal opinion is to add the security and not quibble about milli-seconds.



Having said that... using a SQL data provider for SQL server 200 can be nearly 60% faster than a regular OLEDB connection when using .Net.   .Net was geared specifically to work best with SQL server 2000 (and other MS products).




Now, back to your question.  Can you move transparently from SQLServer provider to an ODBC provider.  The answer is "Maybe".


It depends on how you set up your connection class and how your application uses it.  If you did it correctly (streamlined connections, passing datasets/datatables and not SQLAdapters), then you can change approximately 7 lines of code and have the exact same application.

If you are passing SQLDataADapters around all over... then you have some work to do and you'll have to convert everything to an ODBCDataAdapter
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
I'm only down on it because I've read and tested it and using SqlConnection against SQL2000 (which is what I use at work) has shown significant advantages.  And some of my databases have as many as 3000, yes that's three thousand, connections on them.

As far as security goes, storing an encrypted string in the config file is as secure as any ODBC connection is.   We already lock the machines down preventing the users from writing to most locations, so a read only file that they can't decipher works great.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:FTIISD
Comment Utility
Hi, I've actually taken the config file route.  I have now created the file, and am successfully taking in the information.  My question now, is how to encrypt this information.  I have yet to encrypt information, and I was wondering how this was properly done.

Thanks
0
 
LVL 15

Assisted Solution

by:SRigney
SRigney earned 200 total points
Comment Utility
Here's a link to some cryptography code that makes using it very easy.

http://www.codeproject.com/dotnet/encryption_decryption.asp
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
There is one small bug in his code which may or may not present itself.

To fix it you will need to change
            byte[] bytOut = ms.GetBuffer();
            int i = 0;
            for (i = 0; i < bytOut.Length; i++)
                if (bytOut[i] == 0)
                    break;

to

            byte[] bytOut = ms.GetBuffer();
            int i = 0;
            for (i = 0; i < (int)ms.Length; i++)  <---- This is the only change.
                if (bytOut[i] == 0)
                    break;
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility

And, in your case... I would agree.  3,000 connections (against clustered servers I assume).

Of course, with 3,000 connections I would also just to the assumption that this was a web based application (installing the app on 3,000 computers would not be fun).


At any rate, as you can guess, I am a big proponent for ODBC connections... or at the very least doing the minimum that is required to be secure and functional with good/decent performance.   If you don't HAVE to mess around with encrypting and decrypting text files/registry keys, etc then don't do it.  Let the database do the encryption and decryption for you.  



Ultimately, I feel that if an ODBC connection is good enough for Enterprise level software like PeopleSoft (sold around the world, works on every platform and every database) then I guess its good enough for me.
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
No, it's a desktop app, in centers located in 8 different cities.  The connections all sit on clusterd COM+ servers and the database resides on a SAN.

We push everything out with ActiveDirectories, so we don't have to actually go to each desktop to deploy.
0
 
LVL 9

Expert Comment

by:Joeisanerd
Comment Utility
I haven't used the encryption features of .net yet, I jsut know they are there. I would follow the link given and try that.  Check the msdn website for the System.Security.Cryptography
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


SRigney>  Insurance company?
0
 

Author Comment

by:FTIISD
Comment Utility
I've been working on the example from the given site http://www.codeproject.com/dotnet/encryption_decryption.asp
when i use the encrypt method, is the key that I am passing it just a random hardcoded string?

I have been passing it a string, and depending on the string, I get the error :
An unhandled exception of type 'System.Security.Cryptography.CryptographicException' occurred in mscorlib.dll Length of the data to decrypt is invalid.

when using the decrypt method.

Here is my code:

                  String connection = "Connection string to be encrypted"
                  mobjCryptoService = new RC2CryptoServiceProvider();
                  String output = this.Encrypting(connection, "3523");
                  Console.WriteLine(output);
                  String input = this.Decrypting(output, "3523");
                  Console.WriteLine(input);
                  return output;

This returns the error.  I have however been able to get it to work with other strings.  I also was wondering what type of encryption to use?:

DESCryptoServiceProvider, RC2CryptoServiceProvider or RijndaelManaged.

Thanks for your help
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
That error sounds like you did not implement the change that I mentioned previously.

The Key is the password key that is used to encrypt the file.  If someone knows the key and the type of encryption they can reproduce the original string, so make it something that's somewhat complex.

I don't know which of the three types of encryption are better, they all end up looking like junk to me.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now