Connection String management for a desktop application

Question

I created a desktop application in C#/WPF which connects to a SQL Server 2008 instance through a constant connection string specified in code as follows (for testing purposes):

private string GetConnectionString()
{
    //test
    return "Data Source=[server IP]; Initial Catalog=[database name]; User ID=[user ID]; Password=[smart password];";
}

The application will be used by various users and will be deployed via ClickOnce, a .zip archive or a custom installer. It also has a separated custom login functionality by requesting an application-access username and password.

Which is the best practice to store the connection string details for my desktop application (IP, database, SQL Server user, password)? If the connection string changes over night, which is the best method to update it without forcing users to update to the latest version of my application? Users should not be able to see/intercept/decompile the connection string, so I guess I must use some sort of encryption. Do you have any kind of suggestion for my inquiry?

If the connection is changeable, then somewhere central would be indicated (I'm assuming this is on a single company LAN), such as `NETLOGON`. As to preventing the users reading it - spend more time working on features valuable to the users, rather than a futile effort such as this. If a program, running under the user's account, can access the information, then so can the user. — Damien_The_Unbeliever, Feb 25 '12 at 19:28
Do the users have to authenticate to use the application? I'm pondering having an "unsecured" connection to the database that only supports authenticating a user and returning connection information needed to perform additional functions. The authentication function can have a built-in time delay if you are concerned about an attacker finding the connection and trying out username/password pairs, along with the usual failure counting and account lockout. — HABO, Feb 25 '12 at 21:06
@Damien_The_Unbeliever: I consider this issue as being a security issue that I must take care of by evaluating all the possibilities. I'm looking for the best practice, not the perfect 100% secure way to do it :) You're right, working on useful features is a priority, but let's just not ignore the small details. — Claudiu Constantin, Feb 27 '12 at 06:31
@user92546: yes, users will need to authenticate in the application by providing an username and a password, as I specified in the question. Your idea seems nice theoretically, but I need the Connection String prior to authenticate the user, right? — Claudiu Constantin, Feb 27 '12 at 06:34
@ClaudiuConstantin: What I was thinking was that you could have a connection string that you don't bother trying to secure for `User Id=MyAppLogin; Password=Pr3tty3asyt0gu355`. Set up the database so that the `MyAppLogin` account only has access to execute a single stored procedure. The s'proc accepts the user's username and password for validation and returns a status. If the login is valid it also returns another username/password pair (or a complete connection string) that provides full access to the database. The access may be specific to the user or general for the application. — HABO, Feb 27 '12 at 21:31

score 5 · Accepted Answer · edited May 23 '17 at 12:21

5

Even if you compile your connection strings into the application, they still can be viewed using the Ildasm.exe (MSIL Disassembler) tool because strings are a part of assembly's metadata.

Maybe this question can help you.

edited May 23 '17 at 12:21

Community

1
1

answered Feb 25 '12 at 19:30

Yuriy Guts

2,180
1
14
18

score 3 · Answer 2 · answered Feb 25 '12 at 19:23

3

In a desktop application, you can't prevent a determined user from seeing the connection string. Even if you use encryption, a determined user will be able to find and use the encryption key.

answered Feb 25 '12 at 19:23

Joe

122,218
32
205
338

That is soo true. I've even used .NET reflector to export an assembly to a csproj and rebuild the DLL with some code changes just so I could work out the password. Also I wanted to then change the password in the database and on the client so that I wasn't using the default password used by the vendor. – Christopher Painter Feb 25 '12 at 20:13

paparazzo · Answer 3 · 2012-02-27T18:49:09.950

If the client is connecting to the database then the connection can be hacked.

This is a sample of connection data in App.Config

    <appSettings>
        <add key="dbServer" value="svr"/>
        <add key="dbDataBase" value="db1"/>
        <add key="dbUser" value="sharedUser"/>
        <add key="dbPassword" value="easyPassword"/>
     </appSettings>

Need a reference to system.configuration

        string SvrName = ConfigurationManager.AppSettings["dbServer"];
        string DBName = ConfigurationManager.AppSettings["dbDataBase"];
        string DBUser = ConfigurationManager.AppSettings["dbUser"];
        string DBPassword = ConfigurationManager.AppSettings["dbPassword"];

As for security the answer is a 2 tier application where only the secure server side code connects to the database. This code sample from server side code.

The other benefits of server side is repeated queries from the same connection can gain from indexes in memory from prior query.

You could salt and hash the password read from the AppSettings and obsfuscate the application but you would have to use a static salt so it could be hacked. It would just slow down the hacker.

score 1 · Answer 4 · answered Feb 25 '12 at 19:27

You need to add an "Application Settings" file to your application. Just right click on your solution -> add -> find something similar to "app configuration". In order to do this you will need some kind of external config file to store the connection string. You could even use a simple file. If worried about people finding the file, you can always encrypt the string and decrypt it in your app.

score 1 · Answer 5 · answered Feb 27 '12 at 09:19

1

My opinion is that the safest solution is to have a local DNS entry point to the current SQL machine and the authentication to be Windows authentication.

For example : SQLMACHINE host name pointed to 192.168.1.3 in the DNS server.

This way if the name/IP of the SQL machine changes, only the DNS server needs updated (and possibly the local DNS caches to be invalidated).

Having Windows authentication means that no password will be stored on the local machine so you can safely store the connection string in the .config file with no worries.

My 2 (euro)cents.

answered Feb 27 '12 at 09:19

Andrei Rînea

20,288
17
117
166

But this does not secure the DB. If the user has access to the DB via their Windows authentication they can select, insert, update, and delete records using TSQL. I agree the user must be authenticated and the password is not stored on the local machine. DB can only be hacked by authenticated users. – paparazzo Feb 27 '12 at 18:37
I didn't say it's a perfect solution... it has drawbacks :) – Andrei Rînea Feb 27 '12 at 19:56

Connection String management for a desktop application

5 Answers5