No successful connection to microsoft sql after upgrade to 10.2

hakiru's profile image hakiru posted 5 years ago in Creating a connection Permalink

When I use 10.1 everything works correctly.

Of course I use the same credentials, it's TCP/IP connection. On 10.2 receive the error: SQL Error (17): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

I noticed that in 10.2 I have new option "Library" and I can't change it when I use MSQL connection - libmariadb.dll is chosen and select box is disabled.

ansgar's profile image ansgar posted 5 years ago Permalink

The library dropdown is just for MySQL connections. On MSSQL it does nothing.

Perhaps you need the updated OLE DB driver?

buttm2's profile image buttm2 posted 5 years ago Permalink

The library dropdown is just for MySQL connections. On MSSQL it does nothing.

Perhaps you need the updated

Hi, i am having the same issue. I have MSOLEDBSQL selected in the Library dropdown. The connection fails and i get the following error: SQL Error (0): TCP Provider: No connection could be made because the target machine actively refused it Login timeout expired. Thanks

ansgar's profile image ansgar posted 5 years ago Permalink

https://blogs.msdn.microsoft.com/sql_protocols/2007/07/19/tcp-provider-no-connection-could-be-made-because-the-target-machine-actively-refused-it/

buttm2's profile image buttm2 posted 5 years ago Permalink

Thanks ansgar, I think this problem exists in the nightly builds. I am using ver. 10.2 and the connections work fine in it but as soon as i update to the nightly build, the same connection stops working. Hope this helps.

ansgar's profile image ansgar posted 5 years ago Permalink

Does it then work with SQLOLEDB library?

buttm2's profile image buttm2 posted 5 years ago Permalink

Does it then work with SQLOLEDB library?

No. It does not work with both SQLOLEDB and MSOLEDBSQL.

jonttu's profile image jonttu posted 4 years ago Permalink

Hey Ansgar,

sorry for necrobumping this thread, but this problem seems to have suffered a regression in Version 11.x

In Version 11, connecting via TCP/IP to an MSSQL instance of 10.0.0.1\INSTANCENAME fails with a timeout using both SQLOLEDB and MSOLEDBSQL libraries. MSSQL servers without instances work as intended.

Reverting to Version 10.1, everything works perfectly - instances or none.

Thanks for all your hard work and let me know if I can supply more information to help troubleshooting this issue.

ansgar's profile image ansgar posted 4 years ago Permalink

Other users reported they used a wrong port number. Could that be the case for as well?

jonttu's profile image jonttu posted 4 years ago Permalink

Hey Ansgar,

(wow you're fast)

sadly, no. Port 1433, on both clients, identical settings and connection credentials, etc. Also can connect flawlessly to the server in question using Microsoft's SSMS.

It's not a huge issue for me personally, as I just keep a portable copy of Version 10.1 around to connect to instanced SQL Servers, and 11.0.0.5919 for everything else. But other people might hit a snag on this, so I thought I'd let you know!

Thanks for your support and please don't hesitate to ask for more information if you need it.

ansgar's profile image ansgar posted 4 years ago Permalink

You say that servers "without instances" work as intended. And one other user reported that he also ran an Analysis Service on that server, on the port 1433 if I remember right. After he changed the port to the one from the instance, HeidiSQL connected normally. I have a gutt feeling that this issue is similar.

ansgar's profile image ansgar posted 4 years ago Permalink

See this thread. I'm not sure what port he finally used.

jonttu's profile image jonttu posted 4 years ago Permalink

Hi Ansgar,

sorry for the delay in getting back to you on this.

So I went and ran 10.1 to connect to the SQL instances that didn't work in 11.x. I hit up netstat -b in a cmd window with Admin rights and sussed out which port heidisql 10.1 was using to connect (you can't change ports in 10.1 manually, as it does the autodiscovery thing - which makes it work I guess).

So I found out it seems to (always?) resort to 65442. I mention this, because it may be pertinent to the next poor soul hitting a snag on this issue.

As soon as I keyed in port 65442 to 11.x, it connected like a charm. So, in lieu of autodiscovery, you can always use the above method to find the right port and aren't in any position to look into the sql server directly (because you don't have those privileges).

Hope that helps the next guy, and thanks for the hint!

SFischer's profile image SFischer posted 4 years ago Permalink

Hello,

so I found this thread after having significant problems connecting to my named MsSQL-instance on newer version of HeidiSQL. It works fine in 10.1, but doesn't work at all in 10.3, 10.4 or 11.0 as far as I can tell. I may be able to connect to the instance if I can find out what the actual port of it is, but that kind of defeats the point of named instances and is also not always possible (plus it's not a fixed port either).

Typically, I connect by entering SERVERNAME\INSTANCE into the Hostname/IP field (same as I would in MS Management Studio, or any other client). Port stays on 1433. On 10.1 I can connect just fine, on the others it eventually times out.

As far as I can tell, named instances just flat out don't work at all anymore. If I can help fix this, just let me know!

Thanks Sebastian

ansgar's profile image ansgar posted 4 years ago Permalink

Did you already try out named pipe network type?

Description

SFischer's profile image SFischer posted 4 years ago Permalink

Yes I have, different errror but also no connection. I get a "protocol not supported" instead of a "timeout". see attachments below.

2 attachment(s):
  • Error-namedpipe
  • Error-tcpip
ansgar's profile image ansgar posted 4 years ago Permalink

Ok, and you also tested both OLE drivers?

At least user jonttu (see some messages above) reported it worked for him, so I wonder what's the issue here.

SFischer's profile image SFischer posted 4 years ago Permalink

Yes, I did try both OLE drivers, same result.

Jonttu said that he had manually determined the port to access the instance (in his case 65442) by looking at what the old HeidiSQL connected to, then just used that port on the newer version. So it didn't work for him, he just essentially used a workaround using BOTH versions to access the server. As I wrote, this would of course work too, but defeats the purpose of named instances. The name is essentially resolved to the port via the agent, or whoever resolves this. And it seems that connection seems to fail. Note that that port is NOT stable, and can change on reboot or sql-server restart.

It used to work just fine. I can have a look with wireshark to see which part exactly fails.

ansgar's profile image ansgar posted 4 years ago Permalink

With named pipe and the SQLOLEDB driver it still works for me:

Description

ansgar's profile image ansgar posted 4 years ago Permalink

By the way: the port is only used in TCP/IP mode, and only if it's not "0". So you could also try to set it to 0.

SFischer's profile image SFischer posted 4 years ago Permalink

Ok so from your screenshot it looks like you're connecting locally, I am not though. So it might require just additional firewall rules, but as it stands it doesn't work either (same "server doesn't support requested protocol" message I posted above). Firewall rules aren't that easy, it's a domain environment.

I can't change the port once set to named pipe, the field is greyed out. If I change it in TCP-IP mode, then change to named pipe, same error again.

And again, connecting via TCP-IP works fine using Heidi 10.1. Microsofts own client (SQL Management Studio) also works fine, and I'm assuming it also uses TCP-IP. So at least for that protocol it's not a Firewall issue and the required rules are there.

ansgar's profile image ansgar posted 4 years ago Permalink

I'm not sure if it's a firewall issue, from what you're saying SSMS works, and probably uses a different protocol as HeidiSQL. In SSMS, I can't get TCP/IP working. Only "<default>" and "Shared Memory" works on my local SQLExpress installation. Could you test these in SSMS by click on "Options >>"?

Description

SFischer's profile image SFischer posted 4 years ago Permalink

Wait, I just understood what you meant with setting port to 0. You actually meant for TCP-IP mode. and YES! that finally works!

This is kind of unfortunate that the port has a default value then, I suppose? It's still very very weird, since the same settings (including the port) work fine in 10.1.

So I'm still confused either why it works and why it doesn't...

SFischer's profile image SFischer posted 4 years ago Permalink

Just to clear that up though, since I hadn't seen your last post yet: In management studio, network protocol was set to <default>. Shared memory obviously doesn't work cause it's a remote database. TCP/IP works. Named pipes does not.

It might be relevant (or not) but the Server is an Express 2014 (unfortunately, a bit old-ish, but nothing I can do).

ansgar's profile image ansgar posted 4 years ago Permalink

I think "0" would be the better default for new MSSQL sessions, instead of 1433.

Code modification/commit a34e2ab from Ansgar Becker <anse@heidisql.com>, 4 years ago, revision 6073
Set "0" as default port for new MSSQL TCP/IP connections, which leaves the port away from the connection string, enabling auto-detection. See https://www.heidisql.com/forum.php?t=34256#p36709

Please login to leave a reply, or register at first.