SQL Express ODBC Connection

Use a structured connectivity checklist and compare the working (old) server with the new one. Start with basic reachability, then SQL configuration, then client/ODBC specifics.

1. Verify SQL Server service and instance

• On the new server, open SQL Server Configuration Manager or services.msc.
  • Confirm the SQL Server service for the instance (for example, SQLEXPRESS or default MSSQLSERVER) is running.
  • If there are multiple instances, confirm which one should be used by UPS WorldShip.

2. Confirm instance name and connection target

• In Configuration Manager, check the instance name:
  • If the instance name is MSSQLSERVER, it is a default instance; clients should connect using just the server name (or IP), no \InstanceName.
  • If it is a named instance (for example, SQLEXPRESS), clients must use ServerName\SQLEXPRESS or ServerName\SQLEXPRESS,Port.
• From the machine running UPS WorldShip, test connecting with SSMS or another tool using the exact same server/instance string WorldShip uses.

3. Check protocols and ports

• In SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for <Instance>:
  • Ensure TCP/IP is Enabled.
  • Optionally enable Named Pipes if the application expects it, but TCP/IP should be enabled for remote connections.
• In TCP/IP → Properties → IP Addresses:
  • Under IPAll, either:
    • Use a static port (for example, 1433) and leave TCP Dynamic Ports blank, or
    • Use dynamic ports and rely on SQL Browser (see step 4).
• After changes, restart the SQL Server service.

4. SQL Browser and dynamic vs static ports

• If using a named instance and dynamic ports:
  • Ensure SQL Server Browser service is running on the new server.
  • Ensure UDP port 1434 is open between the WorldShip machine and the SQL server.
  • If UDP 1434 cannot be opened, switch the instance to a static port and always connect using ServerName\InstanceName,Port.
• If using a static port:
  • Always specify the port in the connection string or ODBC DSN: ServerName,Port or tcp:ServerName,Port.

5. Firewall and network checks

• Even if the Windows firewall is disabled for testing, confirm there is no network firewall blocking:
  • The SQL TCP port (1433 or the configured static port).
  • UDP 1434 if using SQL Browser.
• From the WorldShip machine, test connectivity:
  • telnet ServerName 1433 or use PowerShell Test-NetConnection ServerName -Port 1433 (adjust port as needed).
  • If this fails, the issue is network/firewall/port, not credentials.

6. Test with ODBC and alternate tools from the WorldShip machine

• On the WorldShip machine, open ODBC Data Source Administrator and create/test a DSN using the same driver and server/instance/port.
• If ODBC test fails:
  • Try using the IP address instead of server name.
  • Try specifying protocol explicitly: tcp:ServerName,Port.
  • Try a different SQL ODBC driver version if available.
• Also test with another client (for example, sqlcmd or SSMS) from the same machine using the same connection details.

7. Narrow down where it fails Use the following sequence (from the client/WorldShip machine):
8. Can a TCP connection be made to the SQL port? (telnet/Test-NetConnection)
   • If no: network/firewall/port configuration issue.
9. If yes, can a connection be made using SSMS or ODBC DSN with tcp:ServerName,Port?
   • If yes: SQL Server is reachable; focus on driver, protocol, or connection string differences in WorldShip.
   • If no: check SQL Server protocols, TLS support, aliases, and error messages in SQL ERRORLOG.
10. Compare old vs new server

• On the old (working) server, note:
  • Instance name.
  • TCP/IP enabled/disabled.
  • Port (static vs dynamic).
  • Whether SQL Browser is running.
• Replicate those key settings on the new server or adjust the WorldShip connection to match the new configuration (for example, add ,Port or change instance name).

9. Use UDL/ODBC troubleshooting patterns

• Create a UDL file on the WorldShip machine and test connectivity by varying:
  • Provider/driver.
  • Protocol prefix (tcp:, np:, lpc:).
  • Server name vs FQDN vs IP.
  • With/without port and instance name.
• Note which combinations succeed or fail; this often reveals whether the problem is DNS, protocol, or SQL Browser.

Following these steps systematically from the WorldShip machine to the new SQL Express instance will identify whether the issue is instance name, port/protocol, SQL Browser, firewall, or driver/connection-string configuration.

References:

• Consistent SQL Server network connectivity issues
• A network-related or instance-specific error occurred while establishing a connection to SQL Server
• Test OLE DB connectivity to SQL Server by using a UDL file
• how to fix SQL server management on my PC - Microsoft Q&A
• How to fix ODBC test result failed - Microsoft Q&A
• Cannot connect to SQL Server - Microsoft Q&A
• error 26 trying to connect to my local server - Microsoft Q&A
• My SSMS is installed but cannot connect. Error 40. SQL services are running but default instance fails. - Microsoft Q&A
• Connect to a SQL Server Data Source (SQL Server Import and Export Wizard)