Connection String has Semicolon (;) - how on earth can I get this working?

Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):

When I'm at the office, this is my connection string, pretty normal:

connectionString="Data Source=ServerName\InstanceName;Initial Catalog=blah..."

But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.

First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using"ServerName\InstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now"ServerName\InstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.

I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!

Have you tried with a Colon instead of a semicolon?

|||

I have - no dice. I've tried a colon after the servername but before the instance name (i.e.ServerName:Port\InstanceName) as well as at the end of the entire name (ServerName\InstanceName:Port).

|||

Hi jdandison,

The standard format to conifgure your port number in connection string is:string connectionString = "Server=Servername\InstanceName;port=your port number;"

So, try to change your connection string to :connectionString="Data Source=ServerName\InstanceName;port=NewPortNumber;Initial Catalog=blah..."and try again.

Tell us if you have any further questions. thanks