We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

C# Connection String Storing

FTIISD
FTIISD asked
on
Medium Priority
11,730 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!
Comment
Watch Question

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.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

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

Author

Commented:
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

Commented:
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.
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

Commented:


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

Commented:
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.

Author

Commented:
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
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
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;

Commented:

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.

Commented:
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.
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

Commented:


SRigney>  Insurance company?

Author

Commented:
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

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.