A client has a Windows 2000 server they've been running since around 2000, and finally decided to upgrade. They're making an internal applications server, a testing server, and a front line external facing web server. They have a bunch of ASP Classic pages, several hundred in all, that talk to the database by means of System DSNs. The idea was to be able to repoint the web pages at a different SQL Server if they ever grew to that point, so they wanted to make that possible without having to make code changes. Since ASP Old School doesn't understand the concept of an XML web.config file to store these types of settings in, they choose ODBC DSN as a technology to allow this.

The trouble is there are a few SQL databases on the same server, and different parts and users of different web applications run under different SQL users. For security and testing purposes, because you can give a user select but not update, insert, or delete rights to a table.

Which means they have almost 200 DSNs on this old server, that need to get replicated out to 3 new servers before any of them work. I realize it would probably be easier to change the code, even to use an ini file to store connection strings, but the client feels that's "an unjustifiable risk" at the same time they're bringing these new boxes online and retiring an older one. What kills me is they agree they need to change the code anyway, but want to do the work first before making the work unnecessary.

I always thought DSNs were DSN files, stored under \Windows\System or somewhere like that. I could have sworn. But it turns out they're in the registry. Can we just migrate a registry hive, or does it need to be more involved? Has anyone ever tried anything like this?

HKLM\SOFTWARE\ODBC\ODBC.INI

subkey -> ODBC Data Sources has the names

then each DSN will have a subkey with the same name.

You should be able to export and merge this.

Wow.

If they want to change their code to something a bit more modern - and it doesn't even have to be the web.config - you can easily build a page listing all of your servers, that will make your applications use the one you want ... it sounds like the only reason they want somebody to replicate 600 DSNs is so they can recode the system at a later date, when they're also not installing more servers. And then get rid of all the DSNs, I would assume? If it's all about risk management, what's the risk of micro-managing to the point where you can't be productive enough? You might need a more diplomatic way to address that concern. But you said 'client', and not 'boss' so maybe you're better off charging them to build a bridge and then tear it down...?

Take all your connection strings, put them into a DataTable, then serialize them to an xml file named db.config. Write a class with methods and properties to do everything, and write a COM wrapper. Create an aspx page - you can run asp 3.0 and .net x side by side in the same app - to maintain the data.

Probably these applications you're working have an include folder ... probably a "page" with a bunch of functions to open a database, going through a DSN. Find the calls were a recordset object opens a query against a connection; change all of these to use the class you write and wrapped for COM. It should have connection strings for everything you need, so it should be easy enough to just change where the database is called ... hoping that part is abstracted away.