SQL Server connection issues

[tzachk]

Hello InstallAware support,

We have a problem using the sql server connection command.
By looking at wireshark log I see that in order to identify that the connection is succesful, InstallAware is connecting to SMB port 445 and tries to browse \\server name\IPC$.

This is a big problem as in large organizations will block access to this share. Is there a fix for this issue?
Why don't you just try to connect to the default port of the SQL server?

Regards,
T.

[FrancescoT]

Dear Tzachk,

Yes, I checked it and the answer is that our plugin doesn't require any port specifically.

For you convenience, following you can read the complete answer I received from our developent team, as answer to your question and to this other one (http://www.installaware.com/forum/viewtopic.php?f=2&t=9593) as well.

138 and 445 are file and print sharing ports and can be closed. They may not be related to SQL server. Usually SQL server listens on 1433 unless set up to the contrary. SQL server connections can and do happen even with SMB/NetBios disabled. In fact some suggest disabling SMB/NetBios to increase security on SQL server installations. I've heard in cluster structures, with NetBios off, nodes can have problems seeing each other; but that's not what we're looking at here.

Our plug-in uses the MS SQL Native Client mechanism; so we don’t need any baggage for the SQL connections.

I suspect the situation is related to the SQL server setup. If "named pipes over NetBios" are enabled then port 445 is needed. In that case NetBios also needs to be on, file/print sharing ports must be activated; the firewall closes these typically.

More information: http://support.microsoft.com/kb/839269

Regards

[tzachk]

Hi Francesco,

I'm sorry, but the problem is with your plugin.
I'm able to connect to the remote SQL server from:
- remote machine using aspnet_regsql.exe to register roles
- Our own management UI which resides on a remote machine and can successfuly connect to the DB
- SQL server management studio from any machine I'm using on that network.

The only connection error I get is with your SQL server plugin. Using wireshark, I can't even see that the installer is trying to connect to port 1433 at all.

Regards,
T.

[tzachk]

Hi Francesco,

Maybe your plugin does use native sql server libraries, but for some reason it first tries to use netbios which fails, and therfore do not continue.
And as the link you sent me shows, other users of IA discovered the same problem.

Regards,
T.

[FrancescoT]

Dear Tzachk,

I am sorry but I don't agree with you.

The tool you used and used by the other user with the link as well, only tells you which is the port and the protocol used, but not who really started it.
Because our Plugin uses the MS SQL Native Client mechanism, the connection settings are controlled by the MS SQL server only.

Regards.

[tzachk]

Hi Francesco,

Wieshark doesn't tell you which software, but I don't see any connection to the sql server, not by installaware or any other software.

Forget wireshark. Please help me figure out why three different application from three different vendors are able to connect to the DB with no issues, but InstallAware is not.

I have a client that can't install. They have a group policy not to open netbios shares.
What do I do?

Regards,
T.

[FrancescoT]

Dear Tzachk,

let me see if I am able to find out how you could test the behavior you seem to have.

By the way, as I said I'm not an MS SQL expert ... but it could be very important to know which are the exact MS SQL server settings, as ports enabled and so on.

Regards

[tzachk]

Hi Francesco,

We have another client with the SQL server connection issue.
This is the third client we had issues with.
Up to now to overcome this issue. I made a special installer which didn't connect to the DB at all, and we made manual asjustments. This is a risky process and prone to problems which I want to avoid.

As I asked in the past and didn't receive an answer till now, please explain in full details how the connect to DB command works. On which libraries it depends. What should be the minimum requirements in terms of software installed to make sure it is working.
As I mentioned in the past, any other tool that we use to connect from the same machine to the DB, connects without any issues.

Regards,
Tzach

[FrancescoT]

Dear Tzach,

Unfortunately, I am not allowed to share details on how the plug-in internally works.

For sure I can say, that our plugin doesn't require any dedicate components because it Relies on Windows' itself.
It works with just having the minimum MS SQL server configuration installed.

Anyway and if you believe that your error is caused by the plugin, you could use the RUN PROGRAM command from your IA script to execute the "sqlcmd" tool as you do from command line.

Hope this helps you.

Regards

[tzachk]

Hi Francesco,

I do not need to know the inner working of the plugin, nor does it interests me. I need to know the minimum requirements needed for the host machine in terms of libraries.
In order for me to run sclcmd tool, I need to have the sql server client components installed. Is this a pre-requisite of the plug-in?

You need to understand that when an installer of ours fail to install, it impact the confidence of our clients in our product. Not to mention that any other tool we use to try to connect to the DB from the same machine works. My client does not care that the installer software has issues. For the client it is my company that is to blame.

Let me know which debug operation I can make to see what is going on.

Regards,
T.

[tzachk]

Hi Francesco,

Found an answer that solved the issue. We are now going to test it at client
From what we discovered, you must include the SQL server port number of 1433 (or whatever the client is using) to the server address. Otheriwse it shall not connect to the SQL server if it is using a firewall.
The reason for that is that installaware db plug-in tries to connect with UDP port 1434 to the SQL server. Port 1434 is used by the SQL server broswer service that provides information on the SQL server instance. In many cases this 1434 port is closed by the firewall, in which case the DB connect plugin shall fail to connect to the SQL server browser service and thus fail the connection alltogether.
Specifying the SQL server port number forces the DB plug-in to use this port and thus do not connect to port 1434, but connect directly with the SQL server service itself.
For some obscure reason, executing the same installer again after succesfull connection does not require the 1434 connection anymore.

In summary, in order to solve the failing DB issue you either:
- Open UDP port 1434 on the SQL server machine
- Include the SQL server port number in the information provided to the DB plug-in

Francesco, this is the kind of information that needs to be available to Installaware clients, other wise they do not know why their installers do not work.

Regards,
T.

[FrancescoT]

Dear Tzach,

what you found, it is exactly what I told you to check with my reply dated "Thu Aug 01, 2013 6:53 pm".

Unfortunately, it is not possible to specify the port number with the plugin and it relies, on the the MS SQL server settings defined with the target.

Regards

[tzachk]

Hi Francesco,

It shall always be 1434. This is the port for the sql browser which provides information on the ports of the SQL server itself.
And on your answer on August 1st is not relevant, as even if port 1433 is open, your plugin still connects to 1434 and it is closed shall report connection problem. It is the SQL broswer that matters, not the SQL server.

Regards,
T.

[FrancescoT]

Dear Tzach,

I try to explain once again and I hope it'll be definitevely clear.

WE DO NOT SET OR ALTER THE SETTING OF ANY PORT DURING THE PLUGIN CONNECTION PROCESS.
THE PLUGIN JUST TRY TO ENABLE A CONNECTION. IF ANY PARTICULAR PORT IS USED, ENABLED OR WHATEVER ELSE, THIS DEPENDS ON THE TARGET SETTINGS ONLY.

Hope this clarifies the matter.

Regards

[tzachk]

Dear Francesco,

I'll try last time.
Your remark is quite clear.
Developers are dependant on clear log messages from external liubraries they use. Installaware either need to provide clear explanation on the inner working of it's code logic or relevant log messages. If your SQL server plugin depends on SQL browser port availability, failed to connhect to it should popup a different log message then SQL server connect fail.

That's it.
Do what you want with my advice, trying to help you out, but apparently you think differently.

Regards,
Tzach