Use JDBC To Connect Microsoft SQL Server

This article will tell you how to connect Microsoft SQL Server using JDBC.

Table of Contents

1. You Can Access Microsoft SQL Server Using JDBC With The Following Two Methods.

Use com.microsoft.sqlserver.jdbc.SQLServerDataSource java object.

SQLServerDataSource sqlDs = new SQLServerDataSource();
						
// Use window integrate authentication.
//sqlDs.setIntegratedSecurity(true);
			
/* Use sql server account authentication.*/
sqlDs.setIntegratedSecurity(false);
sqlDs.setUser("sa");
sqlDs.setPassword("sa");
					
// Set ds server name or ip.
sqlDs.setServerName("localhost");
// Set sql server listening port number.
sqlDs.setPortNumber(1433); 
// Set the database name.
sqlDs.setDatabaseName("TestDB");
			
// Get connection
dbConn = sqlDs.getConnection();

Use the JDBC connection URL likejdbc:sqlserver://serverName\ serverIP:portNumber;databaseName=dbName;integratedSecurity=true;property1=value1;property2=value2;
If you set the parameter integratedSecurity value to true, it means that you log in to the SQL Server using windows authentication.
If setting it to false means login SQL server uses SQL server account, you should provide username and password in this case like below. jdbc:sqlserver://serverName\ serverIP:portNumber;databaseName=dbName;integratedSecurity=false;user=sa;password=sa;

But before this, you should download and install the Microsoft SQL Server JDBC driver first.

2. Download Microsoft SQL Server JDBC Driver File.

Click here to get the latest version of the SQL Server JDBC driver.
After you download the enu.tar.gz file, unzip it to a local folder like below.C:\WorkSpace\dev2qa.com\Lib\sqljdbc_6.0\enu

3. Add The JDBC Driver Jar Into Java Project.

After download and extract, you can locate the Microsoft SQL Server JDBC driver jar file in the extracted folder ( such as \sqljdbc_9.2.1.0_enu\sqljdbc_9.2\enu ).
Add the related jar file to your java project build path. If you use JDK 15 then add mssql-jdbc-9.2.1.jre15.jar, if you use JDK 8 then add mssql-jdbc-9.2.1.jre8.jar. You can read the article How To Add Selenium Server Standalone Jar File Into Eclipse Java Project And Maven Project to learn how to add them to your java project build path.
Now you can write java code to use JDBC to connect to Microsoft SQL Server.

4. Connect Microsoft SQL Server Use JDBC DataSource.

The below method shows how to use the JDBC DataSource object to access the SQL Server.

/* Use SQLServerDataSource object to access sql server in jdbc. */
public void connectSqlServerUseDS()
{
	// Declare the JDBC objects.
	Connection dbConn = null;
	CallableStatement cstmt = null;
	ResultSet rs = null;
		
	try {
		// Create a sql server data source object. 
		SQLServerDataSource sqlDs = new SQLServerDataSource();
		
		// Set ds related info.
		
		// If you want to use window integrate authentication.
		//sqlDs.setIntegratedSecurity(true);
		
		/* If you want to use sql server account authentication.*/
		sqlDs.setIntegratedSecurity(false);
		sqlDs.setUser("sa");
		sqlDs.setPassword("sa");
					
		// Set ds server name or ip.
		sqlDs.setServerName("localhost");
		// Set sql server listening port number.
		sqlDs.setPortNumber(1433); 
		// Set the database name.
		sqlDs.setDatabaseName("TestDB");
		
		// Get connection
		dbConn = sqlDs.getConnection();
		 
                // Run a callable statement which will return data list.
                cstmt = dbConn.prepareCall("{call dbo.uspGetStudentTeachers(?)}");
                
                // Set the prepared statement input parameter value.
                cstmt.setInt(1, 100);
            
                // Execute the stored procedure and get the returned data in result set.
                rs = cstmt.executeQuery();

	        // Show the result data in a loop.
	        while (rs.next()) 
	        {
	           		
	            System.out.println("Student: " + rs.getString("StudentLastName") + ", " + rs.getString("StudentFirstName"));
	            System.out.println("Teacher: " + rs.getString("TeacherLastName") + ", " + rs.getString("TeacherFirstName"));
	            System.out.println();
	        }
	}catch (Exception ex) {
	    ex.printStackTrace();
	}finally {
	    if (rs != null) 
	    {
	    	try { 
	    		rs.close(); 
	    	}catch(Exception ex) {
	    		ex.printStackTrace();
	    	}
	    }
	    	
	    if (cstmt != null) 
	    {
	    	try { 
	    		cstmt.close(); 
	    	}catch(Exception ex) 
	    	{
	    		ex.printStackTrace();
	    	}
	    }
	    	
	    if (dbConn != null) 
	    {
	    	try { 
	    		dbConn.close(); 
	    	} catch(Exception ex) {
	    		ex.printStackTrace();	    			
	    	}
	    }
        }
}

5. Connect Microsoft SQL Server Use Connection URL String.

The below method shows how to use connection URL string to access Microsoft SQL Server.

/* Use jdbc connection url to access sql server.*/
public void connectSqlServerUseURL()
{
	// Build SQL server JDBC connection URL use SQL server account authentication.
	String sqlServerConnectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=TestDB;user=sa;password=008632";
			
	// Use windows authentication. 
	//String sqlServerConnectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=TestDB;integratedSecurity=true;";
	
	// Declare the JDBC objects.
	Connection dbConn = null;
	Statement stmt = null;
	ResultSet rs = null;
	
      	try {
       		// Load jdbc driver class.
       		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            	// Get connection.	
       		dbConn = DriverManager.getConnection(sqlServerConnectionUrl);
            
            	// Execute SQL and return data results.
            	String SQL = "SELECT * FROM Student";
            	stmt = dbConn.createStatement();
            	rs = stmt.executeQuery(SQL);
            
            	// Loop the data result and display the data.
            	while (rs.next()) {
            		System.out.println(rs.getString(1));
            	}
       	}catch (Exception ex) {
       		ex.printStackTrace();
       	}finally {
    	if (rs != null) 
    	{
    		try { 
    			rs.close(); 
    		}catch(Exception ex) {
    			ex.printStackTrace();
    		}
    	}
    	    	
    	if (stmt != null) 
    	{
    		try { 
    			stmt.close(); 
    		}catch(Exception ex) 
    		{
    			ex.printStackTrace();
    		}
    	}
    	    	
    	if (dbConn != null) 
    	{
    		try { 
    			dbConn.close(); 
    		} catch(Exception ex) {
    			ex.printStackTrace();	    			
    		}
    	}
    }
}

6. How To Fix Connection Refused Error When Using Authentication Mode To Connect MSSQL Server In JDBC.

6.1 Question.

I use windows authentication mode to connect to my local Microsoft SQL Server in JDBC connection.

When I run the java source code, it throws the SQLServerException with the error message Connection refused: connect. Verify the connection properties, below is the detailed exception message.

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)

How can I fix this error?

6.2 Answer1.

You should make sure your JDBC URL is written correctly. If you use windows authentication, your JDBC URL string should contain the attribute integratedSecurity=true. If you use SQL server authentication, your JDBC URL string should contain the user and password. Please check it carefully first.

6.3 Answer2.

You said you use windows authentication, so you should make sure to add the integratedSecurity=true in your JDBC URL string like below.
```
JDBC Url String: jdbc:sqlserver://<<Server>>:<<Port>>;databasename=<<DatabaseName>>;integratedsecurity=true
```
And you also need to make sure the sqljdbc driver 4 or later version jar file (sqljdbc.jar) has been added in your classpath.

You can get the JDBC driver version using the below source code.

// get the jdbc connection database meta data.
java.sql.DatabaseMetaData metaData = connection.getMetaData();

// get the jdbc driver version string.
driverVersion = metaData.getDriverVersion());

Copy the file sqljdbc_auth.dll from your SQL server installed directory ( such as C:\Program Files\sqljdbc_4.0\enu\auth\x86 ) to your project folder.
Then specify the VM argument Djava.library.path‘s value to the above sqljdbc_auth.dll saved path in your java project.