2

We have a legacy WinForms application (written by other people) which connects to SQL Server directly using ADO.NET (.NET 2.0, C#). We want to teach this application to talk to SQL Server which is far-far away (not in the same LAN where the app is working).

The first idea came to my mind is to make it in the same way as SSL Explorer (http://sourceforge.net/projects/sslexplorer/) works: to make a tunnel (still don't know how) and change the ADO.NET connection string to 127.0.0.1:34761 (some port), and to make so that the .NET application connects to local port thinking that this is SQL Server instance, our code will redirect this traffic somehow to our web service (that is close to where the real SQL Server is), and our web service connects to real SQL Server.

So, in general the goal is next: without modifying all the code of C# application, to add some lines in the beginning of Main() method (to initialize a tunnel), and to develop an ASP.NET web service which gets all packets and forwars to real SQL Server instance.

Question: does anyone know how to make such a "tunnel"?
Maybe someone knows if there are some working samples?

Improve this question
2
  • 3
    Setup a VPN between the two sites? Commented Jan 14, 2014 at 15:31
  • 4
    This is why people starting developing WCF/WebAPI applications. Code to a Service, not a RDBMS. Good luck dude. Commented Jan 14, 2014 at 15:34

2 Answers 2

Reset to default
2

If you don't want to change your C# code, you basically have the following options:

  • Create a VPN (for example using PPTP, IPSec or OpenVPN). This is the most secure option.

  • Configure your SQL Server's firewall to allow access to SQL Server's port from the client's IP. This is the least secure option.

  • Install a Terminal Server in the same network as the SQL Server and run your application there.

There was once an option of connecting to SQL Server through HTTP, but that feature has been deprecated with SQL Server 2008 and removed with SQL Server 2012. But even if it were still available, I don't think it would allow for a direct OLEDB or SqlClient connection.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You could either employ some kind of VPN connection, or establish SSH tunnel to your server (I'd personally recommend http://www.bitvise.com as server side option). Please take the following into consideration though:

  1. If your application should be responsible for establishing a connection, SSH tunnel would be easier. But in either case some kind of dependency emerges between your application and an environment it is running in. Not only does it need to know what is IP of SQL server, but also how to establish underlying connection (server, port, username, password/key, protocols, etc.). This makes your application more prone to errors caused by environment changes. Consider getting rid of this dependency, by leaving connection maintenance to administrative purposes. The same way as no application tries to configure network interfaces when local server is unavailable - it simply reports connection error.

  2. WAN connection (either public or tunneled) is more prone to runtime connection failures. Rarely application code is written in a way that connection, once successfully established, can be dropped in any random moment. Make sure that application code is aware of such possibility by either employing transactions or gathering code in remote stored procedures.

  3. As of some kind of web service that performs SQL operations on behalf of your application this should be relatively easy to implement (probably boils down to single SOAP operation that performs query and returns rows or number or rows affected). The problem is that if all database operations in your application can be easily replaced with SOAP/remote operations. Moreover - there are new issues that should be taken into consideration (such as security issues or atomicity of operation - i.e. one must make sure that no query is performed twice).

Improve this answer

3 Comments

Dear All, thank you very much for all the posts. I understood that this is a possibility to make SSH, the question is "how to programmatically make SSH connection using C#?". In another words, "how to programme SSH inside our app?".
I would like to explain something else. 1) VPN is not an option. 2) changing the code of this application is also not an option (long to explain why). Of course, theroretically, we could take the code, add there some additional level which communicates with our web services, and would teach the GUI to get data from not direct connection but from those classes. THEORETICALLY. But this is a huge code - this is a very long way. We won't do it. 3) opening direct port to that SQL server is also not an option
Seems that I've found something - sshnet.codeplex.com. The question is if this is the thing we need? Will we be able to make with the help of this library several calls in Main() method to initialize a tunnel, and to make a web server which accepts http traffic from our app and converts it to tcp/ip traffic to our SQL Server?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.