JDBC for SQL Server - Herong's Tutorial Examples - v3.13, by Herong Yang
Enable TCP/IP with SQL Server Configuration Manager
This section describes how to SQL Server Configuration Manager to turn on TCP/IP connection support.
In order to troubleshoot the connection problem with the JDBC Driver for MS SQL Server, we need to use another tool called SQL Server Configuration Manager.
1. Find and run "SQL Server 2019 Configuration Manager". You will see a Microsoft Management Console window showing up with a list of services that are related to the SQL Server. Notice that the SQLEXPRESS instance is running ok.
SQL Server Service SQL Server Browser - Stopped SQL Server (SQLEXPRESS) - Running SQL Server Agent (SQLEXPRESS) - Stopped
2. Open "SQL Server Network Configuration" and select "Protocols for SQLEXPRESS". You will see a list of network protocols that are supported by the SQLEXPRESS instance. Noticed that by default, SQL Server only enables the "Shared Memory" protocol. which is probably why the "SQLCMD" can connect to SQLEXPRESS. "TCP/IP" protocol is disabled, which is probably why the JDBC connection is failing.
Protocols for SQLEXPRESS Shared Memory - Enabled Named Pipes - Disabled TCP/IP - Disabled
3. Right-click on "TCP/IP", select "Enable", and click OK on the warning message box.
4. Select "SQL Server Service", right-click on "SQL Server (SQLEXPRESS)", and select "Restart".
5. Go to the "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Log" folder and open the ERRORLOG file.
6. Find the log line as shown below:
... 20:07:55.74 spid15s Server is listening on [ 'any' <ipv4> 60782].
This confirms that the SQLEXPRESS instance is listening on TCP/IP port 60782, or another number like 61143, which is dynamically picked by the server.
By the way, you can also configure SQLEXPRESS to use the static port of 1433 to match the default port setting used by the JDBC Driver.
Table of Contents
JDBC (Java Database Connectivity) Introduction
Microsoft SQL Server Express Edition
►Microsoft JDBC Driver for SQL Server
Installing Microsoft JDBC Driver for SQL Server
Loading Driver Class Automatically
Loading Driver Class with Class.forName()
DriverManager.getConnection() and Connection URL
►Enable TCP/IP with SQL Server Configuration Manager
Specifying Port Number in Connection URL
Instance Name Better than Port Number
Specifying Instance Name in Connection URL
Closing the Database Connection - con.close()
Specifying Database Name in Connection URL
Incorrect Database Name in Connection URL
Creating Connections with DataSource Class
Microsoft JDBC Driver - Query Statements and Result Sets
Microsoft JDBC Driver - DatabaseMetaData Object
Microsoft JDBC Driver - DDL Statements
Microsoft JDBC Driver - DML Statements
SQL Server - PreparedStatement
SQL Server CLOB (Character Large Object) - TEXT
SQL Server BLOB (Binary Large Object) - BLOB
JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver
JDBC-ODBC Bridge Driver - Flat Text Files
JDBC-ODBC Bridge Driver - MS Access