Using a proxy server or bastion host when connecting to an on-premise database provides several key benefits in terms of security, access control, and network management. A proxy server acts as an intermediary, preventing direct exposure of the database to the internet and thereby reducing the attack surface. It can enforce strict access policies, monitor traffic, and provide logging and auditing capabilities to track and control database interactions.
SSH (Secure Shell) provides a simple and effective way to establish a secure tunnel to a bastion host, enabling access to an on-premise database from a remote location. By using SSH tunneling, we can create an encrypted connection from our local machine to the bastion host, which then forwards the connection to the database server. This method is easy to set up and quick to configure, making it a convenient choice for developers who need reliable access to remote resources. With just a few commands, we can securely connect our local SQL developer or any database client to the on-premise database as if it were running locally. This eliminates the need for complex VPN setups or exposing the database to the public internet. SSH tunneling is not only a reliable solution but also an efficient one.
Using SQL Developer tools offers several advantages for database professionals and developers. First and foremost, they provide an intuitive and user-friendly interface for managing and querying databases, which enhances productivity by simplifying complex tasks. SQL Developer tools typically support various database management systems (DBMS), allowing users to work across multiple platforms without the need for separate applications.
More on SQL Developer
One of the key benefits is the advanced query editor, which includes features like syntax highlighting, code completion, and error detection, making it easier to write, debug, and optimize SQL queries. Additionally, these tools often include built-in reporting, data visualization, and data modeling capabilities, enabling users to analyze and present data effectively.
SQL Developer tools also facilitate efficient database management through features like schema browsing, object creation, and database administration utilities. They often support direct connections to both local and remote databases, making it easy to perform tasks such as data migration, backup, and restoration.
Moreover, these tools integrate well with version control systems, which is essential for maintaining the integrity of database scripts and ensuring collaboration among development teams. The ability to execute, test, and deploy stored procedures, functions, and triggers directly within the tool further streamlines the development workflow.
Overall, SQL Developer tools provide a comprehensive, integrated environment that enhances database development, management, and analysis, reducing the complexity and time required for database-related tasks.
Establish an SSH connection with Dynamic Port Forwarding
Using the Dynamic port forwarding feature you can make an SSH server acts as a SOCKS proxy server forwarding all traffic sent to that proxy over the SSH server connection and all your database connections will be secured.
To create a dynamic port forwarding, open your terminal and run the following command:
> ssh -D 8888 user@ProxySever
The option -D 8888 specifies dynamic port forwarding, meaning that SSH will listen on the local port 8888 and forward all traffic through the SSH connection as a SOCKS proxy. This allows you to route various types of network traffic securely through the SSH tunnel. The port number 8888 can be changed to any other free port on your local machine, depending on your preference or availability.
Configure the Database Connection in SQL Developer
Using the created proxy in SQL Developer is easy. Simply add the following parameters in the Advanced Tab in the Database connection settings.
oracle.net.socksProxyHost 127.0.0.1 oracle.net.socksProxyPort 8888 oracle.net.socksremoteDNS true
- Setting
oracle.net.socksProxyHostto127.0.0.1specifies that the SOCKS proxy is hosted on the local machine. - The
oracle.net.socksProxyPortoption, set to8888, indicates the port number on which the proxy is listening for connections; this port should match the one used in the SSH tunnel configuration. - The
oracle.net.socksremoteDNSoption, when set totrue, ensures that all DNS resolution occurs through the remote server instead of the local machine. This is particularly useful for accessing databases in private networks where the database hostnames are not resolvable by the local DNS.
Together, these settings enable SQL Developer to route its traffic securely through the SOCKS proxy, allowing access to remote on-premise databases as if they were local, while maintaining security and network integrity.