by Redge Shepherd
Bookmark

How to Connect to SQL Server from Java

Connecting to SQL Server may appear to be more complicated than it should be, but the solution is often easier than it looks.

Getting Started – The Tools

  • SQL Server – Several Editions are available from Microsoft. The Developer Edition and SQL Express are available free of charge. SQL Express is used for our purposes here.
    • Microsoft SQL Server Studio – This tool is included in the SQL Server Installation package.
    • SQL Server Configuration Manager – This tool is included in the SQL Server Installation package.
  • IDE – Several options are available including Netbeans, Eclipse, and IntelliJ IDEA. The community version of IntelliJ is adequate, however, IntelliJ IDEA Ultimate is our preferred edition.
  • Java / Java SDK (Oracle) – The code presented here was written using the latest Java SDK (15 as of this writing).
  • Java JDBC – Java Database Connector for SQL Server (Microsoft). Others are available from the respective database providers.
  • JavaFX development is a third party GUI development kit from Gluon. If you plan to develop a GUI application, JavaFX coupled with SceneBuilder from Gluon makes it easy and it integrates readily with IntelliJ IDEA.

SQL Server

When using SQL from Java, the connector requires specific ports to be open to enable TCP/IP. Knowing this matters when you are installing SQL Server.

We present a working solution that, once known, makes connecting to SQL Server from Java a quick and simple matter. Some of the content may require further reading and testing on your part. I have easily connected to SQL Server using C#, but never expected to find the myriad of options and quirks that Java brings to the experience.

The SQL Server installer performers a series of tests to ensure your computer configuration is correct and meets the requirements for a successful installation.

The message offers a link to “Configure the Windows Firewall to Allow SQL Server Access” that presents several options to clear the warning and allow access to the SQL Server database.

In Windows, running the following script at a command prompt (administrator privileges) will open TCP port 1433:

netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

FIREWALL: SQL Server and associated database engine services typically use ports: TCP 1433, 4022, 135, 1434, UDP 1434. A named instance uses dynamic ports.

Disabling all forms of Firewall protection is NOT RECOMMENDED.

Allow access to instances of “sqlservr.exe” using Windows Defender Firewall with Advanced Security. Follow the instructions in “Configure a Windows Firewall for Database Engine Access.“

Open a port in the Windows firewall for TCP access

Start Menu > Run > WF.msc to load the Windows Firewall with Advanced Security.

Click Inbound Rules, Right Click and select New Rule.

In the Protocol and Ports dialogue box, select TCP. Select Specific Local ports, and then type the port number of the instance of the Database Engine (1433).

In the Action dialog box, select Allow the Connection, and click next.

In the Profile dialogue box, select any profiles that describe the computer connection environment when you want to connect to the data engine and click next.

In the Name dialogue box, type a name and description for this rule, and then click Finish.

Configure a Server to Listen on a Specific TCP Port

This approach is described in detail in “Configure a Server to Listen on a Specific TCP Port.” Named instances of the SQL Server Database Engine are configured for dynamic ports. Port 1433 is the known standard for SQL Server.

By default, MS SQL Server Express is configured to use dynamic TCP/IP ports.

Open the SQL SERVER 2017 CONFIGURATION MANAGER and allow the app to make changes. Note that you will have to stop and start the server to affect the changes.

The Sql Server Configuration Manager window will appear as pictured above. Click on SQL Express to show the current protocols and respective status.

Double Click on the TCP/IP protocol to display it’s properties. A window will appear as pictured below:


The top value should be set to “Yes” to enable the TCP/IP protocol for this instance.

Click on the IP Addresses tab and scroll down to the last entry IPAll. Clear the value that appears next to TCP Dynamic Ports and leave it blank. Then add a port value (1433 is the industry standard) or other port value of your choosing.

Stop and restart the server for the changes to take effect.

When using Windows Authentication Integrated Security, add the path to the applicable “.dll” file for your system. In our case, the file is stored at d:\jdbc-SQL-Server-8.4.1.0\sqljdbc_8.4\enu\auth\x64 as pictured below:

We are using IntelliJ IDEA Ultimate. File > Project Structure > Libraries. Click on the “+” , then Java, and browse to the applicable file location. You will notice the change under the “Native Library Locations” section as pictured below:

You are now able to connect to an instance of SQL Server Express and we’ll test it with a simple code snippet in the following section.

Create a Database

We created a simple database named “MicroQueue,” using Microsoft SQL Server Studio (18.7). We then added a single table named “users” containing the fields id, strUserName, strPassWord, dateCreated, and dateModified.

Several entries were added to the table to test the connection from Java.

As an aside, IntelliJ IDEA Ultimate provides the ability to work with your database directly from inside the IDE. This makes for effective and efficient development of database driven applications.

Java Code – SQL Server Connection

After completing the above steps, the following script successfully connected to the database using Windows Authentication via Integrated Security.

package com.microjam;

import java.sql.*;

public class Main {

    public static void main(String[] args) {

        // Create a variable for the connection string.
        // Using Windows Authorization - Integrated Security 

        String connectionUrl = "jdbc:sqlserver://LAPTOP-S2K3BNR7:1433;" +
                "databaseName=MicroQueue;" +
                "IntegratedSecurity=true";
        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement()) {
            String SQL = "SELECT TOP 10 * FROM users";
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.
            String hdr1 = String.format(" %6s ", "id");
            String hdr2 = String.format(" %10s ", "UserName");
            String hdr3 = String.format(" %10s ", "PassWord");
            String hdr4 = String.format(" %21s ", " dateCreated");
            String hdr5 = String.format(" %21s ", "dateModified");
            String hdrs = hdr1+hdr2+hdr3+hdr4+hdr5;
            System.out.println(hdrs);

            while (rs.next()) {
                // System.out.println(rs.getString(1) + " " + rs.getString(2));
                String str1 = String.format(" %6s ", rs.getString("id"));
                String str2 = String.format(" %10s ", rs.getString("UserName"));
                String str3 = String.format(" %10s ", rs.getString("PassWord"));
                String str4 = String.format(" %21s ", rs.getString("dateCreated"));
                String str5 = String.format(" %21s ", rs.getString("dateModified"));
                System.out.print(str1);
                System.out.print(str2);
                System.out.print(str3);
                System.out.print(str4);
                System.out.println(str5);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The output of the Java code appears on the console as follows:

Connecting to SQL Server can be challenging from JAVA and is seemingly more difficult to accomplish than it should be. Of course, now that we’ve succeeded, it will be that much easier the next time.

Related Articles and Resources

  • JDBC with SQL Express – Microsoft SQL Server Forum
  • JDBC and Windows Integrated Security – Answer on StackOverFlow
  • Java Language Extensions in SQL Server 2019 by Prashanth Jayaram (SQLShack.com).
    • The Microsoft SQL Server 2019 MLS extensibility framework provides a solid base for allowing extensions in R, Python, and now Java.
    • The extensibility framework APIs reference for running external scripts such as R and Python are supported in SQL Server 2017. This extensibility framework API now exposed to run JAVA programs using the sp_execute_external_script system stored procedure.
  • ApexSQL – Offers a variety of free and paid tools for working with SQL Server and Azure.

Microsoft SQL Server Management Studio – Current Installation:

Leave a Reply