Go Back

SQL Instance Connection String Issues

Recently, we have started moving our MSSQL production databases to another server which has multiple instances of SQL server.

I've been trying to get things to connect from a .NET web app with little success.

I found a great blog post which has been updated over time to include more details regarding the exact issue I've encountered.

http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

One potential issue is with the port used to communicate between the web and SQL server. Instances tend to use dynamic port allocations, so setting up a static port is sometimes advisable:

http://technet.microsoft.com/en-us/library/ms345327.aspx

I also downloaded PortQryV2, referenced in the first link and that confirmed "UDP port 1434 is LISTENING" and also sent back a listing of all of the SQL instances with the TCP ports they are using.

Lastly, there is the actual connection string. I am trying to connect to a Sitefinity database, so my connection string sits in the DataConfig.config file. However, as a starting point for troubleshooting, I have been working with a static page sitting in the root of the site which just tries to establish a connection to the database through the codefile. If the page loads with no errors, I'm happy.

I am on SQL Server 2005, so have been referencing this for connection string format.

http://www.connectionstrings.com/sql-server-2005

The idea of creating a page which just connects to the db was from forums on Sitefinity.com.

http://www.sitefinity.com/devnet/forums/sitefinity-4-x/general-discussions/openaccessexception.aspx

Finally, I've got something working...

It turns out that if you use the specific port number for the SQL instance, you do not include the name of that instance in the connection string, so I've been able to connect by just including the port number of my instance... now to confirm that the port number will remain the same!

Another helpful article with many examples of connection strings here:

http://weblogs.sqlteam.com/dang/archive/2011/07/10/sql-server-connection-strings.aspx


  • Facebook
  • Twitter
  • DZone It!
  • Digg It!
  • StumbleUpon
  • Technorati
  • Del.icio.us
  • NewsVine
  • Reddit
  • Blinklist
  • Add diigo bookmark