Today, I came across an issue in work which meant I needed to access our Microsoft SQL Servers that provide a database back-end for some of our legacy applications. I thought that gaining access would be straight forward but it turns out accessing SQL Server from a Linux machine can be a bit tricky.
I wanted to use PHPStorm as the database client. I use PHPStorm as my main IDE and connect to all of our MySQL servers using it so it seemed like a logical choice.
I spoke to my colleagues who unfortunately both use Mac's. For them, access was reasonably straight forward as they could use Microsoft's SQL Server Management Studio client which handled most of the heavy lifting. Unfortunately this software is not available on Linux so I have been forced to get creative.
If you have a similar setup, and need to access SQL Server from a Linux machine, these instructions should get you up and running.
Accessing SQL Server with PHPStorm
In PHPStorm, create a new SQL Server database connection. To do this, go to the View -> Tool Windows -> Database window. In the top left of the database window, you should see a small plus icon to add a database connection. Click it and go to the Data Source -> SQL Server options.
A new window will appear allowing you to set up your new SQL Server connection. Populate the following fields:
- Port (usually defaults to 1433 for SQL Server)
- User (this should be your LDAP username you use to authenticate. Ensure that you do not include the domain)
Install the SQL Server (jTds) Driver
I unfortunately couldn't connect with the driver SQL Server (Microsoft) that you are prompted to install by default. No worries though. On the left-hand side, you should see a list of Drivers. Scroll down until you find one called SQL Server (jTds) and click it. You will then be presented with the options for this driver.
If you don't have this driver already installed, you will need to download it. Luckily you can do this in PHPStorm. In the driver files section, there will be a link prompting you to download the latest driver if you don't already have it installed. Click this to get the driver on your system.
Select the SQL Server (jTds) Driver
Once you have confirmed your driver is installed, scroll back up the left-hand side list until you find your SQL Server connection. Once selected, we need to change the driver from the Microsoft version to the jTds version.
This is done by clicking the driver name and selecting the one we want from the drop down menu that appears. The driver name is highlighted in the screenshot below:
Configure Our Driver
Finally we need to configure our driver. Select the Advanced tab.
You will be presented with a list of key value pairs. You need to set the following options:
- DOMAIN - This should be the active directory/LDAP domain your username is listed under.
- USENTLMV2 - I had to set this to true but you might not have to. It will depend on the authentication settings used by your network.
That should be all there is to it. Click on the test connection and if all details have been entered correctly, you should receive notification of a successful test.