handling the connection string with SQL Authentication

Question

Hello,

I am deploying a Windows Application that uses SQL Server 2005. The program will use SQL Authentication.

My question is, how do you handle the connection string when you don't know what the username/password will be? Do you load this from an encrypted file? Or are there provisions for handling this already?

Answer 1

If the enduser will provide the password you don't need to do anything, dont save the usernamne/password in the config file. If you don't want the end user to provide the password you could put it in the config file at installation. But that could be a problem if the username needs to be changed and you have encrypted the connectionstring.

Answer 2

Encrypting sections of the configuration is not as simple for a windows app as for a web app, but it is certainly doable. Here's a sample.

Answer 3

If the user will provide their login details (username and password) then you just need to provide the ability to enter them in your app, e.g. show a dialog asking for these details. You can then use those values the user gives to build the connection string in your code.

Alternatively, if all your users are going to be using a single SQL account to connect then you can put the connection string in your app.config file using encryption if you want to hide it from your users, see cmsjr's answer for an example of how to do this.

Alternatively, if you're developing this on an internal domain (intranet) then switch your database to integrated security and put your users domain accounts into the relevant access group on your database server. Then you won't have to worry about collecting username or passwords at all.

Answer 4

Just make sure to check the username/password for "weird" characters that the user might enter. The last thing you want is for them to change around your connection string. Then basically you just specify the driver (if using ODBC), the database, the server, but leave all the username/password and trusted connection info out. Then just tack on username= and password= which will be set equal to what was entered by the user on the end. However watch out for semicolons. I've never tried to see what happens if there is both a username/password and a trusted_connection = true.