Share via

SQL 2019 Express - ODBC connection via Network does not work

Stefan Vogel 96 Reputation points
2021-06-07T17:22:33.4+00:00

Hi,
I have the following situation
old server 2012R2 with SQL 2012 (Standard)
now I am moving to a new Server
Server 2019 (Essentials) with SQL 2019 (Express)

I transfered the database, and the web application on that server can access it just fine.
Now I want to change the ODBC connection on my Win10 client to access this DB, which worked fine on the 2012-SQL.

I made sure Remote access is enabled, the port is fixed to 1433, the protocolls are all enabled and also the firewall is allowing in coming 1433 connections.
The SQL Browser-Service is started (though it wasn't on the old SQL, but I read this may be a reason)

I tried the Windows integrated "SQL Server" Driver, as well as the "ODBC Driver 17 for SQL Server", neither is working
I get the error from the screenshot (German saying "The SQL Server cannot be found")

Name resolution works of course fine.

I tried "servername\instance" as well as just "servername", none works.

Any suggestions, what I might be missing?

103020-unbenannt.jpg

SQL Server | Other
Accepted answer
  1. Stefan Vogel 96 Reputation points
    2021-06-09T08:58:07.53+00:00

    the whole thing was even easier than I thought
    in the details of the ODBC connection on the client, it looked OK... but was not.

    I had to enable dymanic port, then deselect it and then the port field was empty... I entered 1433 there and now it works...

    This is what it looked like before, so I thought it is all set....
    103780-unbenannt.jpg

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2021-06-08T08:23:09.887+00:00

    Hi @Stefan Vogel ,

    Please check your SQL server port from error log or SQL server configure manager as below screenshot. This is a fixed port or dynamic port?

    103300-screenshot-2021-06-08-161434.jpg103373-screenshot-2021-06-08-162247.jpg

    If it is not work, please check below steps,

    1. If a named instance, make sure SQL Server browser service is running. Make sure the instance name is spelled correct and there is actually such an instance on your target machine.
    2. Test server connectivity with PING from the client machine
    3. Test port connectivity using TELNET to the server and port (from step 4) from the client machine. For example
      TELNET <server-name> 1433
    4. Check firewall settings if step 2or 3 connectivity test fails

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  2. Stefan Vogel 96 Reputation points
    2021-06-07T17:23:45.197+00:00

    forgot to mention: when setting up ODBC connection with the same server name (servername\instance), locally on the server, it is all fine

    0 comments
  3. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2021-06-07T21:33:33.777+00:00

    Is SQL Server actually listening on port 1433? It seems that it is a named instance, so by default it would be using some other port.

    If you supply the instance name, keep in mind that you need to open port UDP 1434 in the firewall, for the Browser service to be available.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.