Multiple instances on a single port and relationship to Named Pipes protocol

Question

Multiple instances on a single port and relationship to Named Pipes protocol

dan.forest

SSC Veteran

Points: 222
More actions
July 26, 2011 at 1:20 pm

#145801

For newly introduced corporate security reasons, I need to run 3 instances of SQL Server 2005 on the same host computer, all on port 1433. I got this to work by doing the following:
- Adding additional IP addresses to the computer, one for each instance
- Configuring each instance to listen on only one of those IP addresses, each on port 1433.
- Enabling Named Pipes on each instance
The last item is the one that has me wondering. If I only enable TCP/IP on the server instance, it does not work, and I'm unsure as to why. On the client side, I don't need to have the Named Pipes protocol enabled, which is adding further questions in my mind. Yes, it is working, but I'm trying to be as complete as I can in understanding how this solution works to overcome any other security restrictions I may come across in the future.
I am also concerned about performance, as I believe Named Pipes can be significantly slower over a WAN, and that is exactly the situation I have. The DBs will be separated from the apps over a medium speed wide-area network.
Thanks.
Dan

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

dan.forest (7/26/2011)
For newly introduced corporate security reasons, I need to run 3 instances of SQL Server 2005 on the same host computer, all on port 1433. I got this to work by doing the following:
- Adding additional IP addresses to the computer, one for each instance
- Configuring each instance to listen on only one of those IP addresses, each on port 1433.
- Enabling Named Pipes on each instance
The last item is the one that has me wondering. If I only enable TCP/IP on the server instance, it does not work, and I'm unsure as to why.

Please elaborate on "it does not work"...from where? Locally or from the client? Using what connection details?

You should be able to run your instance with Named Pipes Disabled, i.e. only Shared Memory and TCP/IP Enabled.

On the client side, I don't need to have the Named Pipes protocol enabled, which is adding further questions in my mind. Yes, it is working, but I'm trying to be as complete as I can in understanding how this solution works to overcome any other security restrictions I may come across in the future.

You can check the transport method each client is using to connect by running this query in their query window:

SELECT net_transport,

endpoint_id,

connect_time,

client_net_address

FROM sys.dm_exec_connections

WHERE session_id = @@spid ;

I am also concerned about performance, as I believe Named Pipes can be significantly slower over a WAN, and that is exactly the situation I have. The DBs will be separated from the apps over a medium speed wide-area network.

You are on the right trail. Try to get everything onto TCP.

Are all three instances named instances, or did you make one default instance?

How are the instances being addressed by clients? Is the requirement meant to make it so clients only have to supply a server_name, and not a server_name\instance_name, where you have different server_names setup in DNS all going to the different IPs? Have you considered handling this using Native Client Aliases on each client computer?

Is the SQL Server Browser running on the server?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

dan.forest SSC Veteran Points: 222 More actions · Answer 2

>> Please elaborate on "it does not work"...from where? Locally or from the client? Using what connection details?

Locally on the server I can connect to the instance with any of one of the 3 protocols enabled (Memory/TCP/Pipes). From a remote client, I can only connect if Named Pipes is enabled. I am using both oSQL and one of the application clients to test this. The error message from oSQL is:

[DBNETLIB]SQL Server does not exist or access denied.

[DBNETLIB]ConnectionOpen (Connect()).

>>You should be able to run your instance with Named Pipes Disabled, i.e. only Shared Memory and TCP/IP Enabled.

That's what I would have thought too.

>> You can check the transport method each client is using to connect by running this query in their query window:

>>SELECT net_transport, endpoint_id, connect_time, client_net_address

>>FROM sys.dm_exec_connections WHERE session_id = @@spid ;

With all 3 protocols enabled, when I connect locally and remotely, I get the following results from this query:

Named pipe32011-07-26 15:59:58.330<named pipe>

Shared memory22011-07-26 16:00:18.720<local machine>

endpoint_id = 3 is my remote session. You can see it's using named pipes even though tcp/ip is the preferred protocol specified on the client computer.

>> Are all three instances named instances, or did you make one default instance?

Yes, all three are named instances.

>> How are the instances being addressed by clients?

If you mean, how am I connecting, for the oSQL test here's my connection string:

C:\> osql -S dbserver\dbinstance -U sql_user -P xxxxxxxxx

>> Is the requirement meant to make it so clients only have to supply a server_name, and not a server_name\instance_name, where you have different server_names setup in DNS all going to the different IPs?

No, the requirement is strictly to have all SQL instances listening on 1433. It is the only port available through the internal firewalls.

>> Have you considered handling this using Native Client Aliases on each client computer?

Yes, but using aliases doesn't resolve the issue with all instances needing to listen on the same port, albeit different IPs.

Having Named Pipes enabled to get this working was something I gleaned from another post. My current take on this is that it could be some kind of limitation either with SQL Server itself, or a lower-level networking constraint. Not sure at this point. Thanks.

Is the SQL Server Browser running on the server?

dan.forest SSC Veteran Points: 222 More actions · Answer 3

Forgot your last question. Yes the Browser Service is running. I believe it needs to be, even when using the default port when you have multiple instances. There are also other instances on this server, so I can't turn it off even if I wanted to.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Disclaimer: I am relying on my knowledge of how SQL Server Browser is meant to work. I have never actually worked in this exact scenario.

I think you're running into an issue because SQL Server Browser is not a system meant for this kind of work. It's a port number resolution system and I am not 100% if it handles managing the same port on different IP addresses. I say this because it is not a cluster-aware service and this use-case starts lending itself more towards multi-homing, fail-over IP addresses, etc.

Using SQL Server Browser

I would be interested in seeing how this turned out:

1. disable named pipes on the server (restart sql service required I think)

2. attempt to connect to the IP and port number of the instance, e.g. 192.168.0.10,1433

As an aside, trade osql in for sqlcmd. osql is deprecated and sqlcmd is a complete replacement with some additional features.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

dan.forest (7/26/2011)
>> Have you considered handling this using Native Client Aliases on each client computer?
Yes, but using aliases doesn't resolve the issue with all instances needing to listen on the same port, albeit different IPs.

I think it does. An alias setup allows you to specify a port.

Alias 1: SERVER_NAME\INSTANCE_NAME_1 = IP_ADDRESS_1 & port 1433

Alias 1: SERVER_NAME\INSTANCE_NAME_2 = IP_ADDRESS_2 & port 1433

Alias 1: SERVER_NAME\INSTANCE_NAME_3 = IP_ADDRESS_3 & port 1433

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

That said regarding aliases...it would be a heck of a lot of work to do all this resolution on the client. You may be able to push aliases to clients through a domain policy or login script but I have not looked into it.

I am back to: if the requirements say that users are willing to supply an instance name in their connection string then why not simply assign a distinct port to each instance, i.e. none would have 1433 since none are default instances, and just be done with the madness?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Hold up..

If you mean, how am I connecting, for the oSQL test here's my connection string:
osql -S dbserver\dbinstance -U sql_user -P xxxxxxxxx

Given your config I really can't see that working. You are using named instances but you have kind of bastardized the process by getting them all onto the default port (1433). You should be able to reference them by ip directly..

osql -S http://www.xxx.yyy.zzz -U sql_user -P xxxxxxxxx

You should also consider using DNS names for each instance so you don't need to know IP.

I like the idea but know its tricky.. Only locally on the machine could you really use dbserver\dbinstance since shared memory is used for connections.

CEWII

dan.forest SSC Veteran Points: 222 More actions · Answer 8

You were correct on all points opc, thanks. 🙂

I did set up an Alias that specified the individual IP address on port 1433, turned off named pipes on the instance, and named the client-side alias 'host\instance', and was able to connect successfully. I guess I was under the wrong impression that the browser service could sort out both the ip and the port.

The key things about this solution is that with the alias in place, the existing applications shouldn't require any changes to thier connection strings, and I'm meeting the security requirements of only 1433 being opened between app and db tiers. Thanks again.

p.s. oSQL is dead...

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

Elliott Whitlow (7/27/2011)
Hold up..
If you mean, how am I connecting, for the oSQL test here's my connection string:
osql -S dbserver\dbinstance -U sql_user -P xxxxxxxxx
Given your config I really can't see that working. You are using named instances but you have kind of bastardized the process by getting them all onto the default port (1433). You should be able to reference them by ip directly..

Elliott, this aligns with what I was thinking too...SQL Server Browser doles out port numbers only...not IP addresses and port numbers.

osql -S http://www.xxx.yyy.zzz -U sql_user -P xxxxxxxxx
You should also consider using DNS names for each instance so you don't need to know IP.

Even if clients were using different server names that resolved to different IPs there could still be network issues with this approach. SQL Server Browser is not always able to reply on the IP on which it was reached. While it may be able to field requests from multiple IPs, it latches onto one IP for all return communications...I think this goes back to the cluster-aware limitation.

http://connect.microsoft.com/SQLServer/feedback/details/296165/sql-server-browser

I like the idea but know its tricky.. Only locally on the machine could you really use dbserver\dbinstance since shared memory is used for connections.

It sounds good on paper so to speak but I think the technology is a little behind the theory here...it needs a little prodding to get it to work.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

dan.forest (7/27/2011)
You were correct on all points opc, thanks. 🙂
I did set up an Alias that specified the individual IP address on port 1433, turned off named pipes on the instance, and named the client-side alias 'host\instance', and was able to connect successfully. I guess I was under the wrong impression that the browser service could sort out both the ip and the port.
The key things about this solution is that with the alias in place, the existing applications shouldn't require any changes to thier connection strings, and I'm meeting the security requirements of only 1433 being opened between app and db tiers. Thanks again.
p.s. oSQL is dead...

That's very cool 😎

Thanks for posting back Dan!

How many clients will need the aliases? Are you planning to centralize management of those through policy or login script? Just curious really.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

opc.three (7/27/2011)
Elliott, this aligns with what I was thinking too...SQL Server Browser doles out port numbers only...not IP addresses and port numbers.
Even if clients were using different server names that resolved to different IPs there could still be network issues with this approach.

Why?

SQL Server Browser is not always able to reply on the IP on which it was reached. While it may be able to field requests from multiple IPs, it latches onto one IP for all return communications...I think this goes back to the cluster-aware limitation.
http://connect.microsoft.com/SQLServer/feedback/details/296165/sql-server-browser

By using the DNS method the browser service is effectively taken out of the equation and is replaced by DNS, port 1433 is assumed when only the name or ip is used. If you setup DNS to say SomeName is ip xxx.yyy.zzz.151 and SomeOtherName is xxx.yyy.zzz.160 the fact that they are actually on the same machine is not important to the client, it will try to connect to port 1433. In this case the browser service really only functions for local communications ON the box.

CEWII

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

Elliott Whitlow (7/27/2011)
opc.three (7/27/2011)
Elliott, this aligns with what I was thinking too...SQL Server Browser doles out port numbers only...not IP addresses and port numbers.
Even if clients were using different server names that resolved to different IPs there could still be network issues with this approach.
Why?
SQL Server Browser is not always able to reply on the IP on which it was reached. While it may be able to field requests from multiple IPs, it latches onto one IP for all return communications...I think this goes back to the cluster-aware limitation.
http://connect.microsoft.com/SQLServer/feedback/details/296165/sql-server-browser
By using the DNS method the browser service is effectively taken out of the equation and is replaced by DNS, port 1433 is assumed when only the name or ip is used. If you setup DNS to say SomeName is ip xxx.yyy.zzz.151 and SomeOtherName is xxx.yyy.zzz.160 the fact that they are actually on the same machine is not important to the client, it will try to connect to port 1433. In this case the browser service really only functions for local communications ON the box.
CEWII

You are correct, assuming the clients will not supply an instance name, however my comments are saying they will, per what the OP said about the connection strings.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

dan.forest SSC Veteran Points: 222 More actions · Answer 13

>> Even if clients were using different server names that resolved to different IPs there could still be network issues with this approach. SQL Server Browser is not always able to reply on the IP on which it was reached. While it may be able to field requests from multiple IPs, it latches onto one IP for all return communications...I think this goes back to the cluster-aware limitation.

well I hope that bug doesn't bite us. If I'm specifying a static port in my connection string, which I am, do I even invoke the browser service?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

dan.forest (7/27/2011)
>> Even if clients were using different server names that resolved to different IPs there could still be network issues with this approach. SQL Server Browser is not always able to reply on the IP on which it was reached. While it may be able to field requests from multiple IPs, it latches onto one IP for all return communications...I think this goes back to the cluster-aware limitation.
well I hope that bug doesn't bite us. If I'm specifying a static port in my connection string, which I am, do I even invoke the browser service?

Not TMK. As long as you're specifying a port (tell client to go straight for TCP) then you won't need instance name resolution (asks Browser on UDP 1434 to resolve name to TCP port) so you won't bother hitting the Browser service.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato