Error connecting to SQL server

CarlosC's profile image CarlosC posted 4 years ago in General Permalink

Hi, I am using HeidiSQL 10.3.0.5771 Portable, and I can not connect to SQL SERVER; after filling the connection info and press OPEN I always got an exception. The exception number is 1,class is EOleException and message is similar to "Arguments are of incorrect type, are outside of acceptable range or in conflict which each other" (translate from PT). Same error with named pipes, TCP/IP, with Windows authentication or SQL authentication. Any suggestion?

TIA Carlos

ansgar's profile image ansgar posted 4 years ago Permalink

You could try the other driver library from the "Library" dropdown.

CarlosC's profile image CarlosC posted 4 years ago Permalink

I only have these providers on the combo: Description

If I select any of these I got an error "Supplier not found".

ansgar's profile image ansgar posted 4 years ago Permalink

These dlls are for MySQL/MariaDB connections, not for MS SQL. This is a bug I recently fixed in a nightly build. You need to save your settings once, before you can select one of the SQL Server providers.

CarlosC's profile image CarlosC posted 4 years ago Permalink

Ansgar,

Thank you for your support - saving the settings once, closing the program and open it again solve the issue - and use the correct SQLOLEDB library - previously I only had the 3 MySql/MariaDB DLLs (or a empty combo).

Wish you a Happy 2020!

jadepet567's profile image jadepet567 posted 4 years ago Permalink

I got the same problem still How Do I fix it

jadepet567's profile image jadepet567 posted 4 years ago Permalink

It saids I can't connect to MySQL Server

philtroy's profile image philtroy posted 4 years ago Permalink

Hi

I am trying to use HeidiSQL portable to access a database on Microsoft SQL Server. I do not have admin access to installing software on this machine . I open heidisql, the session manager starts, I click on new, I specify microsoft sql server (I tried several options), I put in the server name (provided to my by the dba), select sqloledb, and get a warning - "security issue, using sql oledb ado provider with insecure tls 1.0. You should install Microsoft OLE DB Driver from . . . "

Since I am not able to "install" software, I installed msoledbsql.msi on another windows machine, and then copied msoledbsql.dll into the HeidiSQL folder on the citrix machine. I then try again but HeidiSQL does not see this driver.

Any thoughts or ideas?

Thanks . . .

Phil Troy

ansgar's profile image ansgar posted 4 years ago Permalink

That new driver is not mandatory, you can still use it, and the message is just a warning. Also, you cannot install that driver into the HeidiSQL directory - that's wrong, it has to be installed using the original installer. If you don't have admin privileges, you can still use the SQLOLEDB library option. You can also look at the library dropdown, whether the MSOLEDBSQL driver is already there and installed.

philtroy's profile image philtroy posted 4 years ago Permalink

Hi

The sqloledb library option is not working for me.

I am attaching abunch of small screen shot files.

Thank you so much for responding quickly - I really appreciate it.

Phil

3 attachment(s):
  • Heidi04
  • Heidi05
  • Heidi06
ansgar's profile image ansgar posted 4 years ago Permalink

That doesn't mean the driver does not work. The message says you probably just have a user/password problem. Did you get credentials from your admin? If so, disable "Windows authentication" and put them in the user/password fields.

MillTurnBr's profile image MillTurnBr posted 4 years ago Permalink

I'm struggling with the same problem, followed all recommendations above to no avail.

Description

  • I'm using the latest Nightly Builds in portable version: 11.0.0.5985
  • MS-SQL Express 2019

It DOES NOT work with TCP/IP - Some additional info:

  • SQL Browser Service is Running
  • TCP/IP is enabled and properly configured in the SQL Server Configuration
  • It works with named pipe (Using both Windows or SQL Auth)
  • It works fine with Microsoft SSMS (Using both Windows or SQL Auth)

Description Description Description Description

Am I missing something?

ansgar's profile image ansgar posted 4 years ago Permalink

For TCP/IP connections, the port is important to set correctly, which is 1433 in your screen. Probably it runs on a different port. You should look that up in the configuration manager.

MillTurnBr's profile image MillTurnBr posted 4 years ago Permalink

Thanks for the prompt reply!

Well, that would be a rookie mistake... This is a freshly deployed local QA MS-SQL 2019 Express Instance... Nothing was tweaked... Just enabled...

Dynamic ports are disabled in the TCP/IP settings... bit that does not seem to be a problem to SSMS...

Description

Cheers!

MillTurnBr's profile image MillTurnBr posted 4 years ago Permalink

For additional information, these are my TCP/IP settings in the server configuration:

This is Microsoft documentation about them: Please Google "TCP/IP Properties (IP Addresses Tab)"

Description Description

Tks again!

MillTurnBr's profile image MillTurnBr posted 4 years ago Permalink

I just realized my mistake by following your advice @ansgar

To be honest, to this day I had a wrong understanding of the "TCP Dynamic Ports" in the "IPAll" group.

My understanding was that the client PCs were connecting to the Server Browser Service via 1433 and the browser service was routing them to 52221 in the example of my picture above. Every application I wrote to date I never had to specify the port, 1433 was assumed and this has been working for me for years, even using the value 52221 in my "TCP Dynamic Ports" in the "IPAll" group.

It seems that I was either wrong or HeidiSQL behaves differently. I have no problem in being wrong, and I'm glad you mentioned the port because when I entered port number 52221 in HeidiSQL it worked like magic. Because I never had to do it for any other application I wrote, I've been assuming it behaved the way I mentioned above, clients reaching out to server browser via TCP/1433, server browsing routing them to my fixed dynamic port 52221.

Let me know if you have any further thoughts...

MillTurnBr's profile image MillTurnBr posted 4 years ago Permalink

Sorry, my assumption was that client PCs were connecting to the Server Browser Service via 1434, not 1433.

ansgar's profile image ansgar posted 4 years ago Permalink

Everything you wrote is correct. Except for the assumption it was the PC doing that dynamic detection. It's the client software (or the underlying driver) connecting to the Server Browser. HeidiSQL is just not capable of doing that. I'm not that deep into this MS SQL stuff, but my guess is the MSOLEDBSQL driver which HeidiSQL uses is the root cause.

MillTurnBr's profile image MillTurnBr posted 4 years ago Permalink

Thank you. I'mm glad that my understanding of the mechanism was correct.

It is Ok if Heidi can't talk to the Server Browser service yet to be routed to the correct port. I guess that's what my applications and SSMS have been doing behind the scenes, because I never had to worry about that until today. Trying to adopt Heidi for all my SQL needs and abandon SSMS for MS T-SQL only.

It's OK the way it is now as long as people are not using dynamic ports, since Heidi would not be able to obtain the current ports from the Server Browser service in this scenario.

This is a good reference from Microsoft about how this mechanism works: Please Google TCP/IP Properties (IP Addresses Tab) (Can't post links yet, sorry)

I think this topic is to date the best walkthrough about TCP/IP issues between Heidi and MS-SQL. Good conversations!

Tks!

Horus Sirius's profile image Horus Sirius posted 3 years ago Permalink

thank you @MillTurnBr

you helped me.

i freshly installed SQL Server 2019 and can't connect to it. "No supported protocoll". i had to enable TCP/IP for that.

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