January 23, 2015 at 6:38 am
We are building a database server that we intend to use to give all developers their own SQL Server Instance.
The instances i have installed so far look like:
I have Dynamic port assignment off in TCP Properties and have the port specified for each instance.
Instance1 port 1435 (default instance)
Instance2 port 1436 (named instance)
The issue is that, if i try to connect to Instance2\sqldev using port 1435(which is the port assigned to the default), it will actually connect and I can see the databases under the default instance even though I specified 'Instance2\sqldev'
This would could great confusion and mixing of data if we assigning the instances our to developers and they specify the wrong port by accident.
Is there something I am missing on how to lock this down?
January 23, 2015 at 6:50 am
All a named instance is is a 'lookup' to a port number, kinda like the friendly name that resolves to an IP address. What happens when you connect to a named instance is that your client asks the SQL Browser service on that server what port the instance named 'sqldev' is listening on, and then connects to the server on that port. If you specify the port number, the lookup to SQL Browser isn't necessary and the client connects to the server and port specified.
Basically, what you have on that server are two instances of SQL Server, one on port 1435 and one on port 1436 and that's all the SQL Servers really care about.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2015 at 7:20 am
Thank you for the response. I do have them running on separate ports. This issue is that, regardless of the server or instance name I specify, I can still connect to other instances by changing the port number.
When log into SSMS and try to log into Instance2\SQLDev, 1435 I don't want it to connect because I have 1435 assigned to Instance1. It does connect however and i can see all of Instance1 databases
January 23, 2015 at 7:25 am
Yes, you can, because as I said above, "If you specify the port number, the lookup to SQL Browser isn't necessary and the client connects to the server and port specified."
You have two instances
Server:1435
Server:1436 (also known as sqldev)
Hence connect to the server on 1435 and you get the instance listening on port 1435. Connect to the server on port 1436 and you get the instance on port 1436
The name is only used when the port is not specified. When a name is specified and a port is not, the client connects to SQLBrowser to find what port that name corresponds to. When a port is specified then that lookup is not necessary, the name is ignored and the connection is made on the specified port
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2015 at 9:05 am
If I understand correctly, what yoy want is two distinct IP addresses and 2 names in DNS for this machine.
Then you just have to tell each SQL Server instance to listen on a single IP address and not on all addresses (the default). You can do that in the Configuration Manager, SQL Server Network Configuration, TCP/IP.
The final schema would be along these lines:
SERVER1 - 192.168.1.1 - INSTANCE1 - PORT 1435
SERVER2 - 192.168.1.2 - INSTANCE2 - PORT 1436
BTW, using this configuration you could also set port 1433 on both instances and drop the need to specify a port or an instance name upon connect
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply