Connecting to SQL Server Through an SSH Tunnel with Python
Keeping your data secure should always be a top priority when creating, storing or using your data. One of the methods you should employ to keep your data safe is to put your databases into a private network where they cannot be accessed by the public internet. Access to your databases can then be limited to a select few resources with elevated security controls that sit within your network but also allow inbound access from the public internet.
This approach significantly reduces the the area of attack for hackers and reduces your risk space down to a single server, or set of servers. While this approach does help reduce the risk it does not eliminate it and your security will only be as strong as your weakest link so make sure you properly harden your jump servers.
Here at Tree Schema, our data catalog can securely connect to many of your different databases through jump-servers, one of which is SQL Server. For one reason or another, this particular database was rather troublesome to get the networking correct across all of the different cloud providers when compared to Postgres, MySQL, Oracle, etc. so we’re sharing some lesson’s learned.
Connecting to SQL Server
If you have an Azure SQL database, an AWS RDS SQL Server instance or a self-hosted SQL Server database that is not accessible via the internet you can connect to your SQL Server database using the following Python packages:
- sshtunnel: establishes the SSH tunnel to the jump server and then forwards traffic from the jump server to your database
- JayDeBeApi: creates a JBDC connection to the database
It should be noted that pymssql and pyodbc libraries do not work when forwarding traffic through a jump-server. There seems to be a connection issue wherein they can create a tunneled connection to the server but only if the database is not specified. In turn, this causes you to connect to the default database, dbo. Because of this I suggest that you use the JDBC connection when connecting to a SQL Server database through an SSH tunnel.
Connections to the remote database can be made with three steps:
1. Download the Jar
The SQL Server .jar can be downloaded here. I will be placing the file in my /tmp/ directory for this tutorial.
2. Install the libraries in a virtual environment
I have listed specific versions for the two libraries to ensure compatibility but you may be able to use more recent ones.
virtualenv sqlserverssh
source sqlserverssh/bin/activate
pip install sshtunnel==0.1.5 JayDeBeApi==1.2.33. Test the connection
You will need a few values from your jump server and database, namely:
- Jump server host or IP address and port (should be 22 for SSH)
- Jump server username and password/private key
- Database server or IP address (should be 1433 for SQL Server)
- Database name
- Database username
- Database password
Now, before getting to the actual code, there is one very important rule that must be followed when connecting through an SSH tunnel on Azure: the username must be in the <username>@<server> format. For example, if your username is my_user and your Azure server is mysqlserver.database.windows.net then your full username should be defined as my_user@mysqlserver.database.windows.net. You can use this fully qualified username even when not connecting through a jump server but it is not required.
After you add your jump server and database variables you should see this output when you run the script:
Connected!!
[(1,)]There are a plethora of additional parameters you can pass in with the JDBC connection which you should use to tailor your SQL Server connection..
How Does This Work?
Instead of sending traffic directly to your database you will instead send it to a port on your local host that has been used to establish an SSH session with the jump server. Once your traffic reaches the jump server the SSH tunnel gives instructions for where it should be routed next — in this instance it routes traffic to the database.
