Connecting to a MS SQL 2005 named instance

[Pipeops]

I am trying to establish a SQL 2005 named instance(MSSQL2005) ODBC connection to a server that has both MS SQL 2000 and MS SQL 2005 installed.

I only have an IP address and I connect using xx.xxx.xxx.xxx/MSSQL2005 on port that is dynamically determined.

I am able to connect to 2000 database since it is not a named instance and automatically connects through port 1433.

If I just use the IP address, i am not able to connect since it is looking at MS SQL 2000 sa login and not MS SQL 2005(where sa has a different password). Therefore this makes me understand that ODBC connection does not understand that I am trying to connect to MS SQL 2005 and not MS SQL 2000.

Local and remote connections are enabled on the server side via "SQL Server 2005 Surface area configuration". And SQL Server port 1433 is open on the netowrk to the server.

Can someone please let me know how to tell ODBC to connect to a named instance of SQL 2005 and not 2000?

Thanks,
-I

 

[Pipeops]

I forgot to mention that the ODBC connection I am trying to establish is not on the SQL Server network(different company). All local odbc connection work just fine since the DNS is picking up the named instance i think

 

[gmmastros]

When connecting to a named instance, the instance name should follow a back slash (not a slash).

xx.xxx.xxx.xxx[!]\[/!]MSSQL2005

If that does not work, then you should determine the port that the named instance is using. to do this...

On the server...
Click Start -> Run
type svrnetcn
click OK
In the "Instances on this server box", select your named instance.
In the "Enabled Protocols" list, click on TCP/IP
Click the "Properties" button.

You will see the port that the named instance is using.

Then, make sure your firewall allows traffic on that port.

Then, use the port in the connection string. Ex:

xx.xxx.xxx.xxx[!],1234[/!]

Where 1234 is the port number you saw in the tcp/ip properties.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Pipeops]

My TCP port was missing in SQL Server Configuration Manager for the TCP/IP Protocol properties under the IPAll drop down. Thanks a lot for the hint gmmastros.

I can now connect successfully.

Thanks