How to connect to local database via website?

Question

I have a website used by clients for reporting purposes and would like to give clients the option of my website reading their local database directly (instead of them providing data in a file format). In most instances their database would be SQL Express setup on their localhost.

How can I connect to a clients local SQL server via my website, using asp.net / javascript etc? If the client provides their connection string properties, localhost would just refer to my server's localhost...

Consider the following :

Most people connect to the internet via an ISP. THeir database will run on their localhost and will not have a public address as such.

Ideally, the client must supply some connectionstring (database name, login etc.) and my server must connect.

VPN's wont work for this - I must connect to them (in a programmatic way) and pull data, not the other way around.

Thanks

Answer 1

I would setup a local SQL database and connect via a SilverLight application. Then you could read the data and submit data back to your web server / main database using web (WCF) services.

I found this link about connecting Silverlight to a local SQL database..

http://erikej.blogspot.com/2010/02/access-local-sql-compact-database-from.html

Answer 2

You need to use a dns server such as dyndns. You can try it for free for a single web address. Then set up an iis server.

Answer 3

I think you will find it impossible to connect to a remote database if the computer the database is running on isn't on a public address. They will need to 1) specify their public address 2) open the correct port in their firewall. Depending on their setup (home office with simple router/firewall, corporate office with firewall they cant freely change etc) this might or might not provide quite a hoop to jump through.

A solution to this is making the clients first connect to a VPN you all share, like Moo-Juice mentioned.

Edit: Sorry, missed the last part about VPN in your question. If I'm understanding you correctly you essentially want them to be able to connect to your application as "clients" but the data they are sending would be their databases. But you want this to work with clients that aren't publically sharing their database servers on the internet? To me this seems very hard to accomplish, but then again I'm not really good at client-side scripting.

Answer 4

But if your customers provide the SQL Server host address, ADO.NET or any layer relying on it would connect to the right server, not your local one.

Am I wrong?

Answer 5

Providing your clients firewall has been configured to allow connection to SQL Server (the default port is 1433), there's nothing stopping you creating a connection to it. There are security considerations to doing this, however. your client may wish to only allow connection from your web-servers IP Address.

You might also consider a VPN such as the VPN provided by Windows or Hamachi.