Member Avatar for fx.eko

I tried to create the database connection SQL Server 2008 R2 Express with ODBC connection using TCP / IP
After I try the following error appears :
Connection failed :
SQLState : '01000 '
SQL Server Error : 10060
[ Microsoft ] [ ODBC SQLServer Driver ] [ TCP / IP Sockets ] connectionOpen
[ Connect ( ) ] .
Connection failed :
SALState '08001 '
SQL Server Error : 17
[ Microsoft ] [ ODBC SQL Server Driver ] [ TCP / IP Sockets ] SQL Server does
not exist or acces denied .

I 've made a firewall rule on the inbound and outbound to enable port 1433 , but the result is the same connection can not be made , the SQLServer2008R2 Express configuration through Configuration Manager Tools for TCP / IP I was already set on the SQL Server Network Configuration - Protocols for SQLEXPRESS ( or equivalent ) - TCP / IP , by filling in the following directions:
• Double click on TCP / IP
• Click on the IP Addresses tab
• Under IP1 sets the TCP port to 1433 if it has not been already
• Under the IP All TCP Port set to 1433 if it has not been already

But when trying to re- wear an ODBC connection via TCP / IP the result is the same error message appears as shown above

Best regards,

  • 2 Contributors
  • 1 Reply
  • 2K Views
  • 3 Months Discussion Span
  • Latest Post Latest Post by Member #1111100

Dani AI

Generated

Those two errors (SQLState 01000/10060 and 08001/17) usually mean the client cannot reach the instance or does not know which port it is listening on. With SQL Server Express, the SQLEXPRESS named instance often uses a dynamic port unless you pin it, and without the SQL Browser service the client will not discover that port. @h4ng4r18 is right about clearing dynamic ports and setting a static one, but also double‑check the instance is actually bound to the port you chose and that discovery is possible.

Quick checklist that fixes most cases for :

  • On the server, in SQL Server Configuration Manager: TCP/IP Properties -> set Listen All = Yes, clear TCP Dynamic Ports everywhere, and set TCP Port only in IPAll (e.g., 1433). Restart the SQL Server (SQLEXPRESS) service.
  • Start the SQL Server Browser service if you will connect as ServerName\SQLEXPRESS. Open UDP 1434 in the firewall in addition to TCP 1433.
  • If you prefer not to run Browser, connect by port: use ServerName,1433 or IP,1433 in your ODBC DSN. That bypasses UDP 1434.
  • Verify the instance is really listening and nothing else is using 1433. Check the error log for the “listening on” line, or run netstat -ano | find ":1433" on the server.
  • From the client, test reachability before ODBC: telnet ServerName 1433 (or use IP). Then try SQLCMD to rule out DSN issues.
sqlcmd -S tcp:ServerName,1433 -E -l 15

If the above works but ODBC still fails, create a client alias (run cliconfg.exe) pointing an alias to tcp:ServerName,1433, and select that alias in your DSN. Finally, confirm you are using the Native Client driver (SQL Server Native Client 10.0) and that the server name is exact, including the instance or port.

Member Avatar for Member #1111100

Make sure that the instance has remote connections switched on and that the port is statically assigned to 1433 (as I think dynamic could be default).

On the server go to Start > Programs > Sql Server 2008 R2 > Configuration Tools > SS Configuration Manager

Then Network Config > Protocols > Switch on Named Pipes & Shared Memory

Then Open TCP/IP > Make sure Enabled is set to yes > IP Addresses > Ensure TCP Dynamic Ports is BLANK in all IPs > set TCP Port to 1433 in all IPs (IP1, IP2 ... & IPALL) > Apply > OK > Restart Service

What do you get then?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.