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.

Enabling TCP/IP with SQL Server Configuration Manager
Enabling TCP/IP with SQL Server Configuration Manager

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 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

 JDBC-ODBC Bridge Driver - MS SQL Server

 Archived Tutorials

 References

 Full Version in PDF/EPUB