ODBC Connection on a non-default port

[guidogast]

Hello,

My colleague is working on an installer and he gets stuck at the following.

He created an ODBC Test connection in the installer and it all seemed to work pretty well. But he found some sort of bug(?). When he configured the MSSQL Server to run at the default port (1433) it all goes fine, but if he configured it to another port (Other than 1433) it can't connect to the server. We took a look at the Connection string and it seems that nothing is wrong with that. Also, if we fill in a random number as port on the SQL Connection Dialog it can connect (if SQL Server is set to default port), which it shouldn't because SQL is not running on that port.

It just looks like the ODBC Connection does not work if the SQL Server is not running on the default port.

Can you verify this or can you help us make this work?

Thanks in advance!

Sincerely,
Guido

[guidogast]

We really need to know an answer to this question, it's been here since friday morning. Can somebody help us? This is really important for our installer.

Sincerely,
Guido

[Dan]

Hello Guido,

I apologize for the delayed response, we are quite busy at the moment.

Can you please create a System Data Source manually (Control Panel -> Administrative Tools -> Data Sources (ODBC) -> “System DNS" tab. ) and configure it to use the port where your MSSQL Server is running. Then, can you please check if the connection can be established?

Best regards,
Dan

[guidogast]

Hello Dan,

Thanks for replying! My colleague has tried your solution, but as it turns out, it doesn't work. Do you have another solution or trick to get this done? Or is it a bug of Advanced Installer?

Sincerely,
Guido

[Dan]

Hi Guido,

I'm afraid this was not a solution or a workaround.

This was a test to see if your SQL Server was configured correctly, and as you can see, it is not. First make sure to configure SQL Server so it can run on a different port than the default one.

Best regards,
Dan

[guidogast]

Hello Dan,

As we did some digging to why we can't connect to the SQL Server. My colleague generated some logs from the ODBC Datasource Adminstrator (included in this post) and as you can see, there are error messages in these logs. We looked on the MSDN website as to what these errors where and as it turns out, it seems that the functions to the ODBC driver are out of order...

(DM) The HandleType argument was SQL_HANDLE_ENV, and at least one connection was in an allocated or connected state. SQLDisconnect and SQLFreeHandle with a HandleType of SQL_HANDLE_DBC must be called for each connection before calling SQLFreeHandle with a HandleType of SQL_HANDLE_ENV.

(See more info here: http://msdn.microsoft.com/en-us/library ... 85%29.aspx)

Note: Attachment SQL.log is the one that worked and SQL3.log is the one that failed.

Could this be a ODBC driver error or is it possible that AI sends functions out of order?

Sincerely,
Guido

[Dan]

Hi Guido,

It seems that the ODBC driver is causing this. So, you should configure the MSSQL Server to run on the default port.

If you have other questions, please let us know.

Best regards,
Dan

[Dan]

Hi Guido,

If we fill in a random number as port on the SQL Connection Dialog it can connect (if SQL Server is set to default port), which it shouldn't because SQL is not running on that port.

It seems that in order to connect to a Microsoft SQL server which is not on default port 1433 you need to append the port number into the Server field (with a comma between e.g. USER\SQLINSTANCE,2000 ) from Predefined SQL Connection properties.

Please take a look on the How do you specify a different port number in SQL Management Studio? thread which debates a similar issue.

Please let us know if that helped.

Best regards,
Dan

[guidogast]

Hello Dan,

The solution you gave us works! Thank you for the quick reply after our call.

Sincerely,
Guido

[Olaf S]

Hello,
this problem revisits me from time to time... sorry...
The reason it comes back is, that the tag "use default port" in the ODBC client configuration dialog is NOT UNSET when defining a custom SQL connection which I do as follows in advanced installer:

Code: Select all

Driver={[DBDriver_PROP]};Server=[DBSERVER_PROP];Port=[DBPORT_PROP];Uid=[DBUSER_PROP];Pwd=[DBPASS_PROP];Database=[DBNAME_PROP];

the properties are stored as hidden properties in the MSI.

best regards
Olaf

[Daniel]

Hello Olaf,

I am sorry but I am not sure I fully get what you are trying to expose. Could you please give us more details on this? Maybe attach some screenshots.

Thank you,
Daniel

[Olaf S]

Thanks Daniel,

I'll explain...
1) I have set up the ODBC connection in the package (which does not allow me to set the PORT so that the default config applies (checkmark for Default Port is set):
https://ibb.co/s3cQ0kd

The setup is as follows:
https://ibb.co/vdbNk8T


2) I have setup up a predefined SQL connection as stated in http://stackoverflow.com/questions/8957 ... ent-studio:

https://ibb.co/6yrRSp7

3) I am wondering if the predefined SQL connection will override the ODBC setting which normall forces the default port

4) I am wondering which is the correct way to solve that.

[Daniel]

Hello Olaf,

Our SQL Connection will not overwrite the ODBC data source connection string at install time.

If you want to install an ODBC data source whose connection string be set with port specified (when the SQL instance is running on a non-default port) or without a port specified (when the SQL instance is running on the default port), then you could proceed like this:

1. create your own custom action which check the port the SQL Service is running on and save the search result into property (e.g. SQL_PORT)

2. in "ODBC" page create two data sources: one with the port specified in the Server parameter and another without a a port specified

3. right click on first ODBC entry and choose "Go to Component" context menu option

4. on the selected component use the following condition: SQL_PORT = "default"

5. repeat the above steps (4,5) for the second ODBC entry

Hope this helped.

All the best,
Daniel

[Olaf S]

Hi Daniel,

thank you so far. What is the correct syntax for defining a PORT in the ODBC config (Data Sources dialog)?

I now have used

Code: Select all

Server=Servername\Instance,Port

and LASTUSER=username with no password (but there's one)

[Daniel]

Hell Olaf,

I apologize for the delayed reply.

The "Servername\Instance,Port" format should work as expected for the "Server" parameter when you want to be used a custom (non-default) port.

All the best,
Daniel