Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm trying to connect to a named SQL instance on the server. There is already default instance of SQL Server 2005 and a newly named one - SQL Server 2008. I'm using the "Network library" property to use TCP connection but for some reason, he try's to connect the default instance and omit an error about login error.

If I remove the network library propery the connection established as well without any error.

I'm using windows authntication and have all the permissions needed.

The problematic connection string:

Data Source=ServerName\InstanceName,1433;network library=dbmssocn;Initial Catalog=MyDataBaseName;Integrated security=SSPI

The working connection string:

Data Source=ServerName\InstanceName;Initial Catalog=MyDataBaseName;Integrated security=SSPI

I would like to understand why, please. Thanks in advance,

Tamir

share|improve this question

1 Answer 1

up vote 1 down vote accepted

The connection strings have 2 differences so I'll try to answer for this...

  • Named instances do not listen on port 1433 (only the default instance by, er, default)
  • The instance name is ignored when the port is specified

So:

  • ServerName\InstanceName,1433 means connect to server "ServerName" on port 1433
  • ServerName\InstanceName means connect to server "ServerName" and resolve instance name to correct port (resolve uses port 1434 UDP)

However, network library=dbmssocn means use tcp. It could be the SQL Server instance (see port stuff) does not listen on tcp

share|improve this answer
    
that was very helpfull, thanks a lot. But if I'm using ServerName\InstanceName with network library=dbmssocn, which port will be used? it will use TCP, but which port number? a dynamic one? –  Tamir Jan 21 '10 at 12:41
    
Port is dynamic, but will stay the same usually. There is no logic to it. I'd also forget network library=dbmssocn –  gbn Jan 21 '10 at 13:07
    
I got link to a registry key that show to which TCP port the the named instance is listening. using it instead of the 1433 still cause an error. do you have idea why? TCP is enabled, as i can connect using DBMSSOCN when not specifing a port, so this should not be the problem. found the port at the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER2008\MSSQLServer\SuperSocketNetLib\Tcp\IPAll Thank you for answering :-) –  Tamir Jan 21 '10 at 15:25
    
MORE PRECISELY: can i specify a port number some how when connecting a named instance or no way? if i'm able, than how? The question is more about if i can specify it on the connection string, more than if i can cause the named instance to use permanent port. –  Tamir Jan 21 '10 at 15:28
    
nevermind, I've found: databasejournal.com/features/mssql/article.php/3689846/… –  Tamir Jan 25 '10 at 11:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.