Fixing SQL Server Express Remote Connection Issues for Tick ERP Using Static TCP Port Configuration

Tick ERP and many similar accounting or ERP applications rely on Microsoft SQL Server Express as their backend database. A common real-world issue faced in LAN environments is that the ERP works perfectly on the server system, but fails to connect from client PCs, showing errors like:

SQL Server does not exist or access denied
DBNETLIB ConnectionOpen failed

Advertisement

This article explains the root cause, diagnostic process, and permanent solution for this problem using static TCP port configuration (1433) and correct ODBC setup.

? Root Cause Analysis

By default, SQL Server Express uses dynamic TCP ports.
This creates problems in LAN setups because:

• Clients do not know which port SQL Server is using

• Firewall rules usually allow only standard ports (like 1433)

• SQL Browser dependency causes intermittent failures

• ERP applications like Tick ERP rely on stable ODBC connections

As a result:

• SQL works locally (Shared Memory)

• SQL fails remotely (TCP/IP blocked or unknown port)

? Key Concepts Involved

• SQL Server Network Protocols

• TCP/IP vs Shared Memory

• Dynamic Ports vs Static Ports

• Windows Firewall Inbound Rules

• 32-bit ODBC System DSN

• ERP → ODBC → SQL Server connectivity flow

?️ Step-by-Step Technical Solution

1️⃣ Identify SQL Server Instance

From SQL Server Management Studio (SSMS), confirm:

SERVER\SQLEXPRESS

and SQL version (e.g. SQL Server 2012 Express).

2️⃣ Configure Static TCP Port (Critical Step)

Open SQL Server Configuration Manager:

SQL Server Network Configuration
→ Protocols for SQLEXPRESS

• Enable TCP/IP

• Open TCP/IP → IP Addresses

• Scroll to IPAll

• Set:

TCP Dynamic Ports = (blank)
TCP Port          = 1433

Restart:

• SQL Server (SQLEXPRESS)

• SQL Server Browser

3️⃣ Verify SQL Is Listening on Port 1433

On the server, run:

netstat -ano | findstr 1433

Expected output:

TCP 0.0.0.0:1433 LISTENING

This confirms SQL Server is bound to port 1433.

4️⃣ Configure Windows Firewall (Server Side)

Create Inbound Rules:

• TCP Port 1433 → Allow

• Program Rule → sqlservr.exe

• (Optional) UDP 1434 for SQL Browser

5️⃣ Test Network Connectivity from Client

On client PC, run:

Test-NetConnection SERVER_IP -Port 1433

Expected:

TcpTestSucceeded : True

This confirms:

• Network is reachable

• Firewall is open

• SQL Server is accessible

6️⃣ Configure Client ODBC (Correct Way)

Open 32-bit ODBC Administrator:

C:\Windows\SysWOW64\odbcad32.exe

Create System DSN:

• Driver: SQL Server / SQL Native Client

• Server:

SERVER_IP,1433

• Authentication: SQL Server

• Username: sa

• Database: Tick ERP database

• Test Data Source → Success

✅ Why SERVER_IP,1433 Is the Best Practice

Benefits:

• No SQL Browser dependency

• Faster connections

• Stable ERP performance

• Predictable firewall rules

? Final Verification

• Tick ERP opens from all client PCs

• Multiple users connect simultaneously

• No DBNETLIB or ConnectionOpen errors

• Stable LAN ERP environment achieved

? Conclusion

The issue was not Tick ERP, not SQL login, and not ODBC drivers.
The real problem was SQL Server Express using a dynamic TCP port.

By fixing SQL to use static port 1433, opening firewall rules, and configuring ODBC correctly, the ERP environment becomes stable, secure, and production-ready.

This approach is recommended for all LAN-based ERP deployments using SQL Server Express.

#SQLServer #SQLServerExpress #TickERP #ERPSupport #DatabaseNetworking #ODBC #TCPIP #Port1433 #SQLTroubleshooting #ERPImplementation #LANSetup #DatabaseAdmin #ITSupport #SQLFirewall #SQLConfiguration #ERPDatabase #SystemAdmin #NetworkTroubleshooting #SQLExpressIssue #TechDocumentation #SQLServer2012 #ERPNetworking #ITInfrastructure #SQLODBC #ClientServer #DatabaseSecurity #WindowsServer #SMBvsSQL #SQLPorts #ERPDeployment #SQLServerLAN #SQLAdmin #DatabaseSupport #ERPTech #SQLBestPractices #ServerConfiguration #NetworkPorts #SQLConnectivity #ERPHelp #ITKnowledgeBase #SQLFix #DatabaseErrors #SQLGuide #ERPSetup #SQLServerFix