Another edited repost from my old website. For the past few years, Microsoft's Azure Data Studio was a way to use one interface for accessing multiple database platforms. But since Microsoft has announced that ADS was going to be moved to end of life very soon, this old article of mine has been given a new lease on life.
Its been my experience that if you work on ETL projects, you eventually accumulate client software for a number of database systems on your development PC. The reason is pretty straightforward – you need to be able to access the systems you are working with to determine data types, schema structures, and occasionally to check that a User account and Password you have been given actually works.
One problem I’ve run into though is that not all operating systems are supported by different database vendors with their tools. While Windows has the largest installation base, Mac OS X, and Linux also are used for ETL development. Proprietary management tools for specific systems like Microsoft’s SQL Server management tool will usually only work on one or possibly two operating systems. Also, because each tool is laid out differently, it can be difficult to find what you need quickly when you only work infrequently on a specific platform. Personally, remembering where I need to go in a specific tool will often take me longer than getting the actual information I was looking for.
All of this leads to the point of this post – using a free open source product call SQuirreL SQL Client to access multiple database platforms via one application regardless of whether you are running Windows, Mac OS X or any of a large variety of Linux distributions. Let me state that I am in no way associated with the SQuirreL platform.
Installation of SQuirreL SQL Client
To get started, make sure you have a JAVA client installed on your system. The SQuirreL website recommends Adoptium's OpenJDK version which is available for Windows, Mac OSX and multiple Linux distros. On most operating systems, enter the following to see if you have Java installed from a terminal prompt:
Next go to this website: http://www.squirrelsql.org/ and click the “Download and Installation” link in the toolbar and then click the link for your operating system. A small JAR file will be downloaded. If you receive a message that these types of files can be harmful to your system, click the option that lets you download the file anyway.
On Windows, execute the MSI file from where your downloaded the installation file.
An lzPack window will appear. Click NEXT on the welcome screen, and NEXT on the please read window.
You can decide where to install the application on the following screen. Because of increased security settings in Windows 11, which is what I am working on now, the default location of C:\Program Files\squirrel-sql-4.8.0 will generate an error message if you are trying to run it as a normal user. You can use a different path, like C:\squirrel-sql and it will create the folder for you.
If you would like to have it place the executables in the Program Files location, there is a workaround. Open a terminal window as Administrator, by navigating to it in the menu system, right clicking it and choosing Run as Administrator. Then proceed with the lzPack and Welcome screens listed above.
On the following screen, choose the add-ins you would like to use such as any DB platforms you anticipate connecting to, the optional plugins for Smart Tools and SQL (located between PostgreSQL and Sybase in the list), and any translations plugins you may need. Some additional plugins you may want to select are:
- Session Scripts – run SQL when opening an session
- SQL Parametrisation – put variables into your SQL statements
- SQL Replace – place environment variables into your SQL statements
- SQL Validator – validate SQL against the ISO SQL-99 standard
Click NEXT to watch the installation progress, and then NEXT when it finishes. Finally, you have the option to create shortcuts and allow all users to access the application. Select specific options according to your needs, and then click NEXT again.
On the final window, there are a couple of notes on the installation and you have the option to generate an automatic installation script in XML format and save it where you like. Click DONE.
The program is now installed. To run it in Linux, run the squirrel-sql.sh bash file. In Windows, navigate to the SquirreL client application listing in the START menu if you installed it as Administrator (if not - you'll need to run squirrel-sql.bat from wherever you installed it). On a Mac, run the SquirreLSQL.app file.
Configuration of SQuirreL SQL Client
At this point, you can run SquirreL but it won’t be able to connect to much. In order to access different DB platforms, you’ll need to download the appropriate JDBC drivers for the databases you want to use. Check with your database vendor for web locations where the driver can be found (some more common ones are listed at the end of this article).
Once you have the JDBC JAR files, extract them as needed and copy the appropriate file to the /lib subfolder where you install the software. (For SQL Server, the file is mssql-jdbc-12.10.0.jre11.jar). Once you have the JDBC JAR file installed, restart SQuirreL, and click the DRIVERS tab on the left side. The installed drivers should now appear with a check mark. If there is a red circle with an X, then the driver is not installed correctly.
Click on the Aliases tab to add a connection to database.
Click the Plus icon to add a new alias. Enter a name in the appropriate box. Click the Driver drop down and choose the appropriate checked driver.
Update the URL to the appropriate one for your database. For example for a locally installed MySQL database, enter: jdbc:mysql://localhost:3306 or for a specific database: jdbc:mysql://localhost:3306/databasename.
For SQL Server, you could use jdbc:sqlserver://<ipaddress>. Add the clause ";databaseName=<db_name>" to the end if you want to limit the connection to just a particular database.
Enter a user name in the space for it, and a password if you want to store it. If you enter one in, and want to save it, be sure to check the box labeled Save password encrypted, otherwise it will be saved in clear text. This is new since the original version of this article.
Click OK to exit, and a new entry with the name you specified will appear in the Aliases panel.
Double click the entry you created to initiate a connection. A Connect To box will appear with the user name filled in. Enter your password, and click the Connect button. If all goes well, you’ll see a new window appear on the canvas with an object tree on the left. At the top, the catalog drop down will show the currently connected schema or database. You can only work in the database or schema that is selected here.
There is one thing you need to be careful of with later versions of SQL Server, which use SSL certificates. If you don't have the certificate installed, you can override the check in the driver properties window for the connection. DO NOT DO THIS WHEN CONNECTING TO A PRODUCTION SYSTEM. Its probably not a good idea when using a Dev system either/
Click the Properties button. In Properties for <connection name> window, click on the Driver Properties tab. Check the "Use driver properties" box to enable the properties.
Scroll down through the properties until you see the option for trustServerCertificate opton. Check it, and then change the value to true. (There is a drop down list with true and false options). Click OK at the bottom. Back at the Add Connection window, click OK again (not Close). Your connection will be saved in the Alias list panel.
Double click the connection alias, and if you didn't save your password, you will be prompted to enter it. If you did save it, it will be entered for you, but masked out. Click the Connect button and if all was setup correctly, you'll be connected to your database server.
Links to some common JDBC files:
- MySQL - https://dev.mysql.com/downloads/connector/j/
- PostgreSQL - https://jdbc.postgresql.org/
- SQL Server - https://go.microsoft.com/fwlink/?linkid=2310306
- Oracle - https://repo1.maven.org/maven2/com/oracle/database/jdbc/
- DuckDB - https://central.sonatype.com/artifact/org.duckdb/duckdb_jdbc
Comments
Post a Comment