How to prevent the DataAdapter from writing connection strings to .config?

[Administrator]

The default storage for DataAdapters in ADO.NET 2.0 is the .config file, such as web.config for ASP.NET apps. How can we change this? I don't want connectionstrings stored in a .config file. This makes abstracting the DataSets to a class library more difficult while keeping security in mind, not having your confidential data in a .config file. Not so much an issue for the web side, but when using this same DataSet class library in a Winforms app, now you have a problem for confidential web service calls!

 

[Administrator]

Hmm, I'm thinking you can't! As I continue to "think" that .NET only wants you to use the built in DataAdapters for apps where connection string data is not sensitive. Otherwise you should use the older style DataAdapter object to fill your DataSets! Odd! I just don't see any properties or overloads to pass in connection strings to the DataSet designer class! Hmmmm....

 

[cjard]

Well, you can, but it s abit of a hack.

Firstly, you know you can encrypt sections of a config with a command line tool, and VB will auto decrypt them when it uses them. THeres usually a load of google noise about the issue but if you turn up nothing let me know

But the answer youre after? Well, here's the hack:

After you have designed your app, got the datasets designed etc, there is nothing stopping you from changing the ConnectionString setting to User scope rather than application scope. It thus becomes readwrite rather than readonly, to your code. Set the text of it to be "hi mum" or something, and then in code, BEFORE you do any DB access, put it to the correct value for the database.

This removes sensitive info from the config file. I struggled for a while trying to add the password into the conn string if i said "No, leave sensitive info out of the settings file" during the dataset wizrd thing.. Then I came to realise that APplication and User settings are not distinct in the My.Settings.. so the tableadapters shouldnt care if the setting is user scoped or app scoped.. it jsut goes for it by name. If it uis user scoped it is editable to your code, so get it from wherever you wanna keep it, in code, before your TA tries to access it.


Note i said you have to finish designing your app.. The designer has some issues with using the setting if it is user scope, i dont know why, but mine errored and crashed all over the place. I now leave setting the COnnStr to user scope to be the last thing I do.

Be careful not to save the setting too! Maybe add some code to the settings so that, when saving, the text is set back to "hi mum" to prevent security problems

 

[cjard]

I just don't see any properties or overloads to pass in connection strings to the DataSet designer class! Hmmmm....

There arent.. if you look at the autogenned code, it just gets a connectionstring out of the settings. It was seeing this code that started me thinking I could edit the string in runtime before the TA gets to it

 

[Administrator]

Thanks cjard, however, IMHO I believe the built in data adapter is for use in applications that would employ a app.config file such as ASP.NET 2.0 or uses as you describe. I personally prefer to use the DataSet in this case with the traditional "internal" data adapter on the form that I use to fill my dataset. I have complete control over the connection string and other properties this way and it is additionally protected by obfuscation.

To each their own, it would just be nice if it had a public property to set the connection string and not solely depend on the app.config for its use. I do like building queries bound to the Dataset and having the different fill overloads, that is a loss in the traditional method, but I have to overcome the app.config issue and ensure protection and privacy.