connection issue to SQL server with release 10.3

Hello,
I recently changed laptop (Windows 10) ,and thought i would take the opportunity to download new release of Heidisql 10.3.0.5771 and install it; however i cannot get it work and to connect to MS SQL server environment. upon connection setting It is now providing a dropdown fording to select one of the 2 libraries : MSQSLOLEDB/SQLOLEDB. neither work for me. when I revert to an old installation of Heidi (namely 10.2.0.5999) it works fine and does not prompt me to select a Library. the sql server version is"SQL Server 2008 R2 (SP2) - 10.50.4339.0 (X64)". could you explain the main différences between 10.2 and 10.3 on that connectivity aspect (and why 10.2 works and 10.3 does not by default) . apart from this , HSQL is a great software .

Regards

HeidiSQL 10.2 used the older SQLOLEDB driver, and did not warn about its insecurity. The dialog in v10.3 is just a warning, which can be ignored. Or do you get some error message?

Hello Ansgar,

thanks for your feedback, indeed i get the warning about security issue on 10.3 with SQLOLEDB, followed by an error message ; "Error SQL (17): [DBNETLIB][ConnectionOpen(connect().] this SQL sever does not exist or acces to it is denied." I am using a (TCP/IP Connection) ; as said no issues with the previous versio(10.2) on the same latptop.

regards

Ok, so we're talking about another message, not the security related one:

this SQL sever does not exist or acces to it is denied

That sounds as if you provide wrong user and/or password, or the servername is wrong. Can you please compare both session settings.

I hope you don't use v10.2 together with v10.3?

Hello Ansgar,

i'll double check but i am quite sure that the connexion strings and credentials are identical as i exported themm through seeting file from 10.2 and imported them through in 10.3 .

i had the issue initally when only 10.3 installed on my new laptop.

regards

The ADO connection string is internally created in HeidiSQL, and has not changed between v10.2 and v10.3, unless you select the newer provider. So I can only guess the server on your laptop has been reconfigured, not started, renamed or moved in any way?

Hello,

I have the same issue with connecting to SQL Server - same warning and error with the OP.

If i rolled back, then the program worked fine.

if you have any pointers, I'd greatly appreciated.

thanks

So you both get this security warning, and the error "SQL sever does not exist or acces to it is denied" , although the connection logic did not change since v10.2? I must have overseen something.

Hi ansgar,

I also have the same issue since I upgraded.

Version: 10.3.0.5771

SQL Server Version: SQL Server 2012 R2 (11.0.5532)

Library: SQLOLEDB

Warning: Security issue: Using SQLOLEDB ADO provider with insecure TLS 1.0. You should install Microsoft OLE DB Driver from <url>

I installed this package, rebooted and same warning persists.

If I press OK on the warning then Heidi hangs for a moment then displays an error...

SQL Error (17): [DBNETLIB][ConnectionOpen(Connect().]SQL Server does not exist or access denied SQL Server does not exist or access denied.

Library: MSOLEDBSQL

Error: SQL Error(0): TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond Login timeout expired

I've attached anonymized logs. Hope this helps. Let me know if you need further details.

Are you sure your Windows Firewall lets HeidiSQL connect to your SQL Server?

Yes, the older version of HeidiSQL works to the same target with no issue.

Well the older HeidiSQL version may have got through the firewall while the newer does not. Please have a look at the Windows Firewall settings, if both can connect to the right port.

Hello,

Thanks for the advise. I tried to turn off the firewall and it still didn't let me connect.

Hi, I also have problems connecting to MS SQL Server databases with TCP/IP on version 10.3.0.5771, connected without problems with version 10.2.0.5599. My issue is that there are several instances of MS SQL Server on the same server, like DB-SERVER-2012, DB-SERVER-2012\MSSQLSERVER2014 and DB-SERVER-2012\MSSQLSERVER2016. With Heidi SQL 10.2 I could connect to all the different instances by specifying both server name and instance in Hostname/IP, but with 10.3 I can only connect to the 'basic' instance (like DB-SERVER-2012), even if I'm specifying a specific instance (like DB-SERVER-2012\MSSQLSERVER2016). It doesn't matter what driver library I'm using.

I just compared relevant sources of v10.2 with v10.3 and now see a difference I previously oversaw. A solution for issue #423 was to add this setting to the ADO connection string:

DataTypeCompatibility=80;

I just pushed a change which passes this line only for the newer MSOLEDBSQL provider. Maybe that helps you connecting through the old SQLOLEDB driver.

Hi. Just to inform you that the change in build 10.3.0.5805 doesn't seem to solve the issue with connecting to a specific instance on an SQL Server with several instances. Please be aware that the issue is related to both the old SQLOLEDB as well as the new MSOLEDBSQL library. Thanks.

Ok, then this must have something to do with the underlying ADO connection code in Delphi's VCL. Hope I can do anything to fix that.

Hi ansgar, I've managed to get this working.

Both SQL Servers I was trying do not run on the default port 1433, but the Port field on the connection settings has always been set to that (since it's a required field).

I believe the issue here is that on the older versions of HeidiSQL, the underlying MSSQL client would respect if the Hostname/IP field contained an instance name (i.e. server.com\inst120) and properly lookup the correct port (ignoring the Port field) using the servers SQL Server Browser Service.

If I specify the correct port for the target instance I can connect with no problems however I believe this behaviour to be incorrect. As far as I'm aware, MSSQL clients should first attempt to query the servers SQL Server Browser Service (UDP 1434) with the given Instance name to discover the correct TCP port. Only using the provided Port field if this lookup fails.

What do you think?

Could this be why most users are working fine since by default a single instance of SQL Server is running on 1433 and this is the HeidiSQL connection configuration out of the box? It also explains the issue KjellH has.

Ok, interesting. Only the port is used for MSSQL since I implemented them in 2011. There is one condition though, if the port is "0", then the port is left away in the ADO connection string, otherwise it's appended after the hostname:

whatever\mssql-2000,1433;Password=...

Found something else which may be contributing...

On the older versions, the 'Port' field is greyed out if an instance name is provided in the 'Hostname/IP' field. The new versions are not doing that.

The new versions are not doing that.

Should. Only for network type "MSSQL TCP/IP", the port input is enabled, or?

Latest version...

On the older versions (9.5 5916) 'Port' would be disabled (greyed out) and it would lookup port via SQL Server Browser Service.

Ah I thought you meant v10.2 - but for TCP/IP it's valid to provide a port. Should the default be then 0, which leaves it away from the connection string?

Hi, and thanks for all valid input. It's correct that the port field in 10.3 is enabled, and have the value 1433. If I clear the field I can connect to the specified instance. Thanks again.

Hi, and thanks for all valid input. It's correct that the port field in 10.3 is enabled, and have the value 1433. If I clear the field I can connect to the specified instance. Thanks again.

Can you tell me how you can do the same thing from the command line?

I tried to launch the connection in this way:

heidisql.exe -n=4 -hMYSERVER\MYINSTANCE -uMYUSER -pMYPASSWORD -P=???

but it doesn't work both if I write "-P=0" or if I omit it.

How should I do it?

Hello, what is the solution finally in order do not see this message every time we connect to SQL Server database?