Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

C# Connection String Storing

Posted on 2004-03-25
18
Medium Priority
?
11,552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 9

Expert Comment

by:Joeisanerd
ID: 10680288
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 800 total points
ID: 10680320
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 400 total points
ID: 10680503

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:SRigney
ID: 10680534
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
ID: 10680710
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
ID: 10680840
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
ID: 10681282
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_
ID: 10681405


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
ID: 10681475
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
 

Author Comment

by:FTIISD
ID: 10681538
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 800 total points
ID: 10681566
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
ID: 10681667
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_
ID: 10681783

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
ID: 10681919
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
ID: 10682851
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_
ID: 10683265


SRigney>  Insurance company?
0
 

Author Comment

by:FTIISD
ID: 10683473
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
ID: 10686810
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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