Hi,
I need some help on setting up a convenient way to tunnel an secure access to a remote SQL Server database.
So let me introduce the story.
I have a small network, which is connected to the internet, and that contains an SQL Server database. The server is used from within the local network, and everything works flawlessly.
Now a client if it is possible to host his database on our server. The reason is that he has 2 diferent networks that must access one instance of SQL Server. He also doesn't have any server, and doesn't want to have one. He just prefers to put the database somewhere else, and I agreed it would be a better solution to him.
So now I have my network, and setup a secure SSH tunnel to the SQL Server.
Let's say that my external address is office.mydomain.com, and I have the following setup:
Router: Opens port 22 (SSH) and forwards requests to 192.168.1.60 (LISTENER01)
LISTENER01: (IP 192.168.1.60) Windows 2003 Server, SSH service listening on port 22 with security and tunneling rules
SERVER01: (IP 192.168.1.70) Windows 2003 Server, SQL Server 2008 Express, Instance name "SQLExpress" listening on TCP port 2048, SQLServerBrowser listening on UDP port 1434
Tunneling rules: -L 2000:192.168.1.70:2048
So the situation is, on a remote computer:
1. I use an SSH client to open a tunnel connection as above;
2. I can connect to the database using 127.0.0.1:2000;
3. SSH client tunnel the communication, and the SSH server will deliver to SERVER01 (192.168.1.70).
This works like a charm, when I'm connecting directly to the database instance, but I'm having another big difficulty here.
The remote client desktops will use a client application, and to configure the database connection the user is presented with a textbox to insert the database instance. Normally, a string like "SERVER01\SQLEXPRESS" is inserted here, and obviously something more needs to be addressed beacause this doesn't work.
As I see it, this type of connection string will delegate to SQLServerBrowser an initial request to check the port under which the required instance. In this case, I know the database instance port is 2048, as it was fixed this way to enable remote connection directly.
Now, I know the SQLServerBrowser is listening on port 1434, under UDP protocol, and it seems it is not possible for SSH to tunnel an UDP protocol port.
I need some help on the possible ways to bypass this situation. The SSH tunneling solution was my choice, as it allowed me to forward to a specific IP/PORT with high security, as I wanted the client to access the database, and nothing more on my network. The SSH connection is setup with public/key authentication, and the SSH server would only allow that specific client to tunnel to that specific IP/PORT ... thus, giving me a complete secure external access.
Some of my possible and crazy solutions would be:
1# In the textbox that configures the database connection, instead of putting "127.0.0.1\sqlexpress" which would make it search for the SQLServerBrowser, just put some connection string that forces the connection directly to the database instance on "127.0.0.1:2000"
I can't find a way of making it to work. The client software just gives an error telling it cannot find the database.
2# I've tried -L 2000:192.168.1.70:2048 -L 1434:192.168.1.70:1434 and using "127.0.0.1\sqlexpress" on clients.
That second tunnel rule would expose the SQLServerBrowser service ... but it's UDP protocol, so it seems to be the reason of it not working.
3# Creating a VPN "could" be a solution, but I'm worried about exposing to much of my internal network.
If only that would be a safe way of creating a VPN only to a specific IP, and allowing access only through a specific port. That way I could allow it to access TCP on port 2048 and UDP on 1434 ... I think that would do the trick.
It seems to be the most uggly solution, and the most harder to setup.
4# A new alternative just appeared ... bridging UDP to TCP and back again.
This "can" be a better approach to the situation, reusing the current security config.
In the client, I could create a listening UDP 1434 port, that bridges to, say, a TCP 3000 port, that would then be tunneled (for example -L 3000:192.168.1.70:3500) into the server on port 3500 ... and there I would have a bridge listening, and converting back to UDP 1434.
Does it make any sense? Would it work?
5# Alternative crazy solutions:
I also thought of creating a small program for clients, that would be listening on UDP 1434 and mimic SQLServerBrowser response, so that it could inform of the tunneled port.
I also tried on the client, to put SQLServerBrowser running, and since it uses the registry to get info about the SQL Server instances port numbers, I could just update the registry with the tunneled port number.
Need some help on whether any of these solutions is appliable. Of course the best one would be to provide a string to the client software to connect to SQLServer instance directly, and not with "127.0.0.1\sqlexpress", since this seems to make it search for the SQLServerBrowser service first.
Anyone still around reading this?
Thank you for any advices/tips/tricks ...
b4u
I need some help on setting up a convenient way to tunnel an secure access to a remote SQL Server database.
So let me introduce the story.
I have a small network, which is connected to the internet, and that contains an SQL Server database. The server is used from within the local network, and everything works flawlessly.
Now a client if it is possible to host his database on our server. The reason is that he has 2 diferent networks that must access one instance of SQL Server. He also doesn't have any server, and doesn't want to have one. He just prefers to put the database somewhere else, and I agreed it would be a better solution to him.
So now I have my network, and setup a secure SSH tunnel to the SQL Server.
Let's say that my external address is office.mydomain.com, and I have the following setup:
Router: Opens port 22 (SSH) and forwards requests to 192.168.1.60 (LISTENER01)
LISTENER01: (IP 192.168.1.60) Windows 2003 Server, SSH service listening on port 22 with security and tunneling rules
SERVER01: (IP 192.168.1.70) Windows 2003 Server, SQL Server 2008 Express, Instance name "SQLExpress" listening on TCP port 2048, SQLServerBrowser listening on UDP port 1434
Tunneling rules: -L 2000:192.168.1.70:2048
So the situation is, on a remote computer:
1. I use an SSH client to open a tunnel connection as above;
2. I can connect to the database using 127.0.0.1:2000;
3. SSH client tunnel the communication, and the SSH server will deliver to SERVER01 (192.168.1.70).
This works like a charm, when I'm connecting directly to the database instance, but I'm having another big difficulty here.
The remote client desktops will use a client application, and to configure the database connection the user is presented with a textbox to insert the database instance. Normally, a string like "SERVER01\SQLEXPRESS" is inserted here, and obviously something more needs to be addressed beacause this doesn't work.
As I see it, this type of connection string will delegate to SQLServerBrowser an initial request to check the port under which the required instance. In this case, I know the database instance port is 2048, as it was fixed this way to enable remote connection directly.
Now, I know the SQLServerBrowser is listening on port 1434, under UDP protocol, and it seems it is not possible for SSH to tunnel an UDP protocol port.
I need some help on the possible ways to bypass this situation. The SSH tunneling solution was my choice, as it allowed me to forward to a specific IP/PORT with high security, as I wanted the client to access the database, and nothing more on my network. The SSH connection is setup with public/key authentication, and the SSH server would only allow that specific client to tunnel to that specific IP/PORT ... thus, giving me a complete secure external access.
Some of my possible and crazy solutions would be:
1# In the textbox that configures the database connection, instead of putting "127.0.0.1\sqlexpress" which would make it search for the SQLServerBrowser, just put some connection string that forces the connection directly to the database instance on "127.0.0.1:2000"
I can't find a way of making it to work. The client software just gives an error telling it cannot find the database.
2# I've tried -L 2000:192.168.1.70:2048 -L 1434:192.168.1.70:1434 and using "127.0.0.1\sqlexpress" on clients.
That second tunnel rule would expose the SQLServerBrowser service ... but it's UDP protocol, so it seems to be the reason of it not working.
3# Creating a VPN "could" be a solution, but I'm worried about exposing to much of my internal network.
If only that would be a safe way of creating a VPN only to a specific IP, and allowing access only through a specific port. That way I could allow it to access TCP on port 2048 and UDP on 1434 ... I think that would do the trick.
It seems to be the most uggly solution, and the most harder to setup.
4# A new alternative just appeared ... bridging UDP to TCP and back again.
This "can" be a better approach to the situation, reusing the current security config.
In the client, I could create a listening UDP 1434 port, that bridges to, say, a TCP 3000 port, that would then be tunneled (for example -L 3000:192.168.1.70:3500) into the server on port 3500 ... and there I would have a bridge listening, and converting back to UDP 1434.
Does it make any sense? Would it work?
5# Alternative crazy solutions:
I also thought of creating a small program for clients, that would be listening on UDP 1434 and mimic SQLServerBrowser response, so that it could inform of the tunneled port.
I also tried on the client, to put SQLServerBrowser running, and since it uses the registry to get info about the SQL Server instances port numbers, I could just update the registry with the tunneled port number.
Need some help on whether any of these solutions is appliable. Of course the best one would be to provide a string to the client software to connect to SQLServer instance directly, and not with "127.0.0.1\sqlexpress", since this seems to make it search for the SQLServerBrowser service first.
Anyone still around reading this?
Thank you for any advices/tips/tricks ...
b4u
Facebook
Twitter