question

StefanVogel-0693 avatar image
0 Votes"
StefanVogel-0693 asked StefanVogel-0693 edited

SQL 2019 Express - ODBC connection via Network does not work

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-general
unbenannt.jpg (27.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @StefanVogel-0693,

Just checking in to see if the answer(s) helped. If the response helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
StefanVogel-0693 avatar image
0 Votes"
StefanVogel-0693 answered StefanVogel-0693 edited

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



unbenannt.jpg (36.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StefanVogel-0693 avatar image
0 Votes"
StefanVogel-0693 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered

Hi @StefanVogel-0693,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.