ODBC DSN SQL Port settings

Hello
I need to create two DSN's on one machine, both pointing to different
SQL Databases (but housed on the same remote server). One runs on the
standard 1433 port and the other on port 1075.
I have both of these setup, but I cannot use a different port in both.
If I set it to 1433 in DSN 1, DSN 2 automatically uses that port. If I
set DSN 2 to 1075, DSN 1 reverts to 1075. Is there any way to use
different ports using the same SQL ODBC driver?
LeonOK, it looks like I've fixed this.
It appears to revert to the same port number if the Server Name in the
DSN is the same. A quick look through the registry confirmed this.
I'm now using a hostname for one of them and the server IP address for
the other. Not the ideal solution, but it works|||Leon...I read your post with interest. I've got a similar situation on the
server side that you do. I need to have two databases running on the same
server with different IP ports (a vendor has an app with a hard-coded IP
port). Up to now I thought I had to add a seperate instance on the server
for it to listen to two different ports.
I know your original email was talking about the DSN settings, but how did
you configure the server side to be listening to two ports?
Thanks for any information.
Bob
"leonvd@.gmail.com" wrote:

> OK, it looks like I've fixed this.
> It appears to revert to the same port number if the Server Name in the
> DSN is the same. A quick look through the registry confirmed this.
> I'm now using a hostname for one of them and the server IP address for
> the other. Not the ideal solution, but it works
>|||To listen on multiple ports, use the Server Network Utility
and enter the port numbers, separated by commas in the
TCP/IP properties. You need to restart the service for the
settings to take effect.
-Sue
On Wed, 23 Aug 2006 07:39:01 -0700, RL Coppedge
<RLCoppedge@.hotmail.com.(nospam)> wrote:
[vbcol=seagreen]
>Leon...I read your post with interest. I've got a similar situation on the
>server side that you do. I need to have two databases running on the same
>server with different IP ports (a vendor has an app with a hard-coded IP
>port). Up to now I thought I had to add a seperate instance on the server
>for it to listen to two different ports.
>I know your original email was talking about the DSN settings, but how did
>you configure the server side to be listening to two ports?
>Thanks for any information.
>Bob
>
>"leonvd@.gmail.com" wrote:
>|||Cool! Thanks for the fast reply!
Bob
"Sue Hoegemeier" wrote:

> To listen on multiple ports, use the Server Network Utility
> and enter the port numbers, separated by commas in the
> TCP/IP properties. You need to restart the service for the
> settings to take effect.
> -Sue
> On Wed, 23 Aug 2006 07:39:01 -0700, RL Coppedge
> <RLCoppedge@.hotmail.com.(nospam)> wrote:
>
>

ODBC Access to SQL Table

I need your help. I have a MS SQL Server in our DMZ that I need to setup an
MS Access ODBC connection to the SQL Server. I am using port 1433 opened on
the firewall but I can't bring up the SQL Server. Is there as way to
trouble shoot these issues or maybe a document on setting up ODBC
connections? I have no trouble within the LAN in our Active Directory.
Please help! Thanks
NC Beach BumDoes your SQL Server allow incoming connections for TCP/IP on port 1433.
Perhaps check your client configuration and make sure that it allows
incoming connections. If this is SQL 2005 you can use your surface area
configuration tool to make sure the protocol is allowed.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"NC Beach Bum" <NCBeachBum@.discussions.microsoft.com> wrote in message
news:EBE2C819-A798-48BD-A179-B511C9D79B31@.microsoft.com...
>I need your help. I have a MS SQL Server in our DMZ that I need to setup
>an
> MS Access ODBC connection to the SQL Server. I am using port 1433 opened
> on
> the firewall but I can't bring up the SQL Server. Is there as way to
> trouble shoot these issues or maybe a document on setting up ODBC
> connections? I have no trouble within the LAN in our Active Directory.
> Please help! Thanks
> --
> NC Beach Bum

ODBC Access to SQL Table

I need your help. I have a MS SQL Server in our DMZ that I need to setup an
MS Access ODBC connection to the SQL Server. I am using port 1433 opened on
the firewall but I can't bring up the SQL Server. Is there as way to
trouble shoot these issues or maybe a document on setting up ODBC
connections? I have no trouble within the LAN in our Active Directory.
Please help! Thanks
NC Beach Bum
Does your SQL Server allow incoming connections for TCP/IP on port 1433.
Perhaps check your client configuration and make sure that it allows
incoming connections. If this is SQL 2005 you can use your surface area
configuration tool to make sure the protocol is allowed.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"NC Beach Bum" <NCBeachBum@.discussions.microsoft.com> wrote in message
news:EBE2C819-A798-48BD-A179-B511C9D79B31@.microsoft.com...
>I need your help. I have a MS SQL Server in our DMZ that I need to setup
>an
> MS Access ODBC connection to the SQL Server. I am using port 1433 opened
> on
> the firewall but I can't bring up the SQL Server. Is there as way to
> trouble shoot these issues or maybe a document on setting up ODBC
> connections? I have no trouble within the LAN in our Active Directory.
> Please help! Thanks
> --
> NC Beach Bum

Obtaining TCP/IP Port and Pipe name

Hello All,

Does anyone know how to obtain the TCP/IP port and the pipe name for an instance?

I would have expected to see these properties in smo.server.configuration or smo.server.settings. What am I missing here?

Thanks

Rob

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ServerInstance. ServerProtocols property is a collection with information about each protocol settings.

WBR, Evergray
--
Words mean nothing...

|||

Thanks Evergray,

Unfortunatly, this will not pick-up any 2000 instances, only 2005 instances installed on the same machine.

Does anyone else know of any other method to obtain the pipe/port via standard smo, or? I know that i can issue a UDP broadcast via a socket and the response (with limited success) will include the information I require, so it can't be rocket science!

Thanks

Rob

|||You will need DMO for this (see Registry object), as the SMO only supports SQL Server 2005 instances.|||

Thanks for the reply Michiel. I have, however respectfully unmarked your post as an answer as the registry object does not expose the required information. I can, via WMI access a remote registry and reqad the NP and port info, but there is a massive overhead in doing this.

I'll rephrase my question in the hope someone knows how to achieve what I'm trying to do:

Via a udp broadcast on 1434, the reply from SQL Servers includes the pipe name and TCP/IP port number they are listening on. This is the information I am trying to obtain. I can, very easily obtain this once connected to an instance, but I need this information prior to connecting, as of course the pipe/port may be different depending upon the instance.

Does anyone know of anyway to do this (without using a socket/IP broadcast)?

Cheers

Rob

|||

Thanks for claryfing your answer. I am somewhat puzzled about what you are trying to accomplish. The normal way to approach this configure you server for the required network protocols, and then configure your clients so that it will try the protocols in a preconfigured order. On a SQL Server 2000 and above versions, the connection betwee your client and instance will be 'routed' for you (on SQL Server 2000 it was a function of one of the instance; in SQL Server 2005, the SQL Browser service is introduced).

Are you trying to connect without this facility? If so, then it makes more sense to preconfigure your servers to some known IP port or named pipe names instead of reinventing the SQL Browser service.

At the risk I get unmarked again, please mark if this helps or clarify further if this is not what you're looking for :-)

HTH

|||

Hi Michiel,

Thanks for your help.

Basically, I'm attempting to audit all unauthorised MSSQL instances on our corporate network. This involves firstly listing all instances, then attempting to connect to the discovered instances to obtain further information. In order to connect, I need to know the pipe name and port number the instances are listening on (quite often the port is changed to be something other then 1433). So, I'm in effect trying to discover a way to to obtain the pipe/port of a particular instance without connecting to it first (chicken or the egg?). This could be done via a udp broadcast as the response from the instances would include the pipe/port they are listening on (as per isql -L). I just can't find a way to get this info without using a socket udp boradcast (or wmi for a remote registry interrogation , which is far too much overhead and is prone to security exceptions)...is there a way?

Thanks

Rob

|||

Makes sense. The UDP broadcast is however unreliable, and an instance could be configured to hide itself. There is really no silver bullet here. This is quite a common question however and it has the SQL Server's team attention (which will not help immediately).

If you are domain admin, you could enforce local scans and aggregate that data up to a central point, if you don't want to depend on WMI.

|||

Hi Michiel,

That confirms my suspicions.

Thanks for all your help regardless; I appreciate it.

Cheers

Rob