SQL Server connection strings and ADO.NET

When you are connecting to a SQL Server 2005 database with ADO.NET using for example C# or Delphi, the IDEs are able to build default connection strings easily. For example, to connect to a server named "SERV-DB" and the database "SALES", you would use a connection string like the following (on a single line):

Data Source=SERV-DB;Initial Catalog=SALES;
User ID=myname;Password=mypassword;

However, things get tricky when your database server hosts multiple SQL Server instances, each of which must run on different TCP/IP ports. In such a case, it is difficult (it was, for me at least) to find the proper documentation how to do this properly. For example, your database administrator might tell to you that the SQL Server instance you need to connect to is named "ERP" and the TCP/IP port number would be 1386 and not 1433, which is the default port for the default instance.

In such a case, you need to specify the instance name and the port number in the "Data Source" atrribute of the connection string as follows: "Data Source=servername\instance,port". Given the above example names, the full connection string would be the following. Note how you should also specify the network library as "dbmssocn" (the socket library) so that the ADO.NET provider knows that you want to connect using TCP/IP:

Data Source=SERV-DB\ERP,1386;Initial Catalog=SALES;
Network Library=dbmssocn;User ID=myname;Password=mypassword;

That should solve your connection string problems.

Keywords: howto, specify sql server instance and port number, connect to different instance, change sql server port, ado.net connection string.