SQL Server multiple instance problem

Question

Post reply

SQL Server multiple instance problem

johnmelvin

Valued Member

Points: 70
More actions
May 20, 2005 at 4:58 am

#87557

Hi

I have created 2 instances in SQL server on a windows 2000 server over a Public IP. The 1^st instance is assigned the PUBLIC IP and can be accessed over a remote location.
Whereas the 2^nd instance cannot be accessed over the public IP.

Now I want to change and assign the Public IP to the second instance, so that the 2^nd instance can be connected from a remote location over the Internet.

Regards
John Mendonca

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think this can be handled two ways;

your instance is listing for SQL Server connections on a different port than the default instance of 1433;

you can either change the listing port for the servers, or make the firewall do it by port forwarding Public requests for port 1433 to go to your alternate port for your other instance.

for example, my machine, which has a default and named instance listens on these ports:

LOWELLI 0:1433

LOWELLI\SQL2000 1207

here is a script to run on your instances to find out what port it is using.

CREATE TABLE #GetPort

(

token varchar(100),

value varchar(20))

go

DECLARE @inst varchar(200)

DECLARE @inst1 varchar(100)

--Determine registry path and key

IF(charindex('\',@@servername) > 0)

BEGIN

SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)

SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'

--SELECT @inst1 = 'TcpDynamicPorts'

SELECT @inst1 = 'TcpPort'

END

ELSE

BEGIN

if SUBSTRING(@@VERSION,23,1) = '7'

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'

SELECT @inst1 = 'DefaultServerPort'

end

else

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'

SELECT @inst1 = 'DefaultPort'

end

END

print @inst + '\\\\////'+ @inst1

INSERT #GetPort

EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

DROP TABLE #GetPort

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

johnmelvin Valued Member Points: 70 More actions · Answer 2

Hi Lowell

I understood what you wanted to convey but let me also tell you what else have I done..

I have made the 2nd instance to use the default port 1433 but still it

doesnt seem to connect through remote location over internet.

Also I am trying to connect to the second instance with the following server

name in query analyser;

server_ip_address\server_name

I hope the above parameter to connect to the server is correct.

I get the error message "SQL Server does not exist or access denied"

Thanks

Regards,

John Mendonca

Lowell SSC Guru Points: 323518 More actions · Answer 3

I don't think you can connect to an ip address, by instance name, if you are not on the same subnet. if your instance is on a public IP, you don't use the instance name anyway.

for example, if your instance is going to be on a public IP, say 65.34.234.179 for example, you DO NOT NEED the instance name; the ip address is the server name, and that should be sufficient if it is on port 1433;

if it is not, you might need something like 65.34.234.179:1207 for the server ip address, if it is answering a different port. the instance name should not be referenced as part of a public IP.

the situation is a little different on a private network; if everyone is on the same subnet you can reference default and named instances separately. if you have a default instance on 192.168.1.100 AND an instance at 192.168.1.100\SQL2000, ;

anyone with a 192.168.1.xxx ip address can use the ip\instancename to find my server....BUT if i am on a different subnet, say 10.1.100.xxx, i cannot get to the named instance the same way...you gotta do ip+:port;

I believe the WINS service is what allows you to find the instancename on a private network.

if your are using a firewall to redirect public traffic to a private IP, it is still the same, it's just you'd use port forwarding on the firewall to point your public 65.34.234.179:1433 traffic to go to your 192.168.1.100:1433 or whatever port is being used.

does that help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

johnmelvin Valued Member Points: 70 More actions · Answer 4

Hello Lowell

I checked on the same and tried to connect through the IP Address and when i do so i get the following error..

unable to connecto to server

server:Msg 14, Level 16, State 1

[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid Connection

Can you suggest me something on this..

Thanks

Regards,

John Mendonca

Lowell SSC Guru Points: 323518 More actions · Answer 5

i think it is comman instead of semi colon:

xxx.xxx.xxx.xxx,1433

so a connection string might look like any of these:

cnObj.ConnectionString= "Provider=SQLOLEDB;Server=65.34.234.179;database=GHBA;uid=tester;pwd=password"

cnObj.ConnectionString= "Provider=SQLOLEDB;Server=65.34.234.179,1433;database=GHBA;uid=tester;pwd=password"

cnObj.ConnectionString= "Provider=SQLOLEDB;Server=65.34.234.179,1207;database=GHBA;uid=tester;pwd=password"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

johnmelvin Valued Member Points: 70 More actions · Answer 6

Hello Lowell

You were absolutly right it has to be IP-ADDRESS,1433

Its now working perfect. I am able to connect to the internet server using the above server config.

One last doubt,

Since 1433 is a default port of SQL Server and in general terms we need not attach the port number to connect to the internet server but in this case I have to connect to the server using ipaddress,1433 which as it fails to connect only though the ipaddress.

can you enlighten me on this point.

Thanks a lot for all the help.

Regards,

John Mendonca

Lowell SSC Guru Points: 323518 More actions · Answer 7

connect via instance name and run the script below. this will identify what port your instance is listening to.

if it is anything other than 1433, you will need to include the port in your connection string, ie "192.168.0.40,1207":

CREATE TABLE #GetPort

(

token varchar(100),

value varchar(20))

go

DECLARE @inst varchar(200)

DECLARE @inst1 varchar(100)

--Determine registry path and key

IF(charindex('\',@@servername) > 0)

BEGIN

SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)

SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'

--SELECT @inst1 = 'TcpDynamicPorts'

SELECT @inst1 = 'TcpPort'

END

ELSE

BEGIN

if SUBSTRING(@@VERSION,23,1) = '7'

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'

SELECT @inst1 = 'DefaultServerPort'

end

else

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'

SELECT @inst1 = 'DefaultPort'

end

END

print @inst + '\\\\////'+ @inst1

INSERT #GetPort

EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

DROP TABLE #GetPort

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!