Specifying Port With SqlConnectionStringBuilder?

Question

I've ran into a snag. I have a need to specify the port number for my local installation of SQL Server 2008 R2. So far I've tried using the SqlConnectionStringBuilder with the data source set as .\TESTSERVER, 1433, which by all the documentation should connect me to the server TESTSERVER on port 1433 (the default).

The connection string looks like:

{Data Source=".\TESTSERVER, 1433";Initial Catalog=AdventureWorks;User ID=testuser;Password=MYPASSWORDHERE}

However I get an error that looks like:

A network related or instance-specific error has occurred while establishing a connection SQL server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL server is configured to allow remote connections. (Provider: TCP provider, error:0 - no connection could be made because the target machine actively refused it.

Upon verifying the connection string using SqlConnectionStringBuilder.ToString() it comes out almost how MSDN suggests. For some reason it wraps double quotes ONLY around the data source and nothing else. However this may just be the debugger removing the outside quotes since it's stored in a string datatype. I also verified that SQL server is accessible without specifying the port and it is. Finally, I verified that the server is allowed to accept remote connections. Considering this instance of SQL Server is installed locally, using the default port, and on my development computer I couldn't imagine why I would be getting an error like this.

Is this because the SqlConnectionStringBuilder is overriding my port with it's own port? Do I need to for some reason open up ports on my firewall? Knowing it's a fully local installation it shouldn't be encountering firewall problems. I'd rather not have to manually build the connection string. Not that it's difficult, it just adds a layer of complexity to my code I'd rather not have unless needed.

Any help would be appreciated. Thanks!

EDIT:

After a long hard digging through the SqlConnectionStringBuilder syntax it appears that it handles invalid parameters by passing then to the connection string surrounded in quotes. I'd imagine this is because it breaks the connectionstring. My question still remains: is there a way to pass port via SqlConnectionStringBuilder or will I have to build it myself?

Answer 1

TL;DR

remove the space before the port number within your data source string:

{Data Source=".\TESTSERVER, 1433";Initial Catalog=AdventureWorks;User ID=testuser;Password=MYPASSWORDHERE}

and let it look like this

{Data Source=".\TESTSERVER,1433";Initial Catalog=AdventureWorks;User ID=testuser;Password=MYPASSWORDHERE}

---

Long Answer

After a little playing around you can omit the extra quotes by removing the whitespace between the comma and the port number:

var stringBuilder = new SqlConnectionStringBuilder();
var sqlCommand = "select TOP 100 * from MyTable;";

stringBuilder.IntegratedSecurity = true;
stringBuilder.InitialCatalog = "MyCatalog";
stringBuilder.DataSource = @"myServer\InstanceName,1433";

// This will give the connection string:
// "Data Source=myServer\\InstanceName,1433;Initial Catalog=MyCatalog;Integrated Security=True"
using (var connection = new SqlConnection(stringBuilder.ToString()))
using (var command = new SqlCommand(sqlCommand, connection))
using (var adapter = new SqlDataAdapter(command))
{
    var table = new DataTable();
    connection.Open();
    adapter.Fill(table);
}

Unfortunately this still leads to the same error message as the one you provided. So i took a deeper look into the network communication and found out, if you don't pass in a port number it first tries a tcp connection to port 1433 (as usual) but it will stay unconnected. Then it tries a udp connection to port 1434 and receives from their a dynamic port number that will be used for a second tcp connection where the data will flow.

By using Process Monitor from Sysinternals you can watch this process:

// The first try by using TCP port 1433
WindowsFormsApplication.vshost.exe  5480    TCP Reconnect   MyMachine:53202 -> SqlServerInstance:1433   SUCCESS
WindowsFormsApplication.vshost.exe  5480    TCP Reconnect   MyMachine:53202 -> SqlServerInstance:1433   SUCCESS
// The second try by using UDP port 1434
WindowsFormsApplication.vshost.exe  7664    UDP Send    MyMachine:50245 -> SqlServerInstance:1434   SUCCESS
WindowsFormsApplication.vshost.exe  7664    UDP Receive MyMachine:50245 -> SqlServerInstance:1434   SUCCESS
// Taking informations out of UDP connection to connect to dynamic assigned port
WindowsFormsApplication.vshost.exe  7664    TCP Connect MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Send    MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
WindowsFormsApplication.vshost.exe  7664    TCP Receive MyMachine:53209 -> SqlServerInstance:58904  SUCCESS
// Closing of dynamic assigned port
WindowsFormsApplication.vshost.exe  7664    TCP Disconnect  MyMachine:53209 -> SqlServerInstance:58904  SUCCESS

By using the explict port number I'll only see the first given lines and afterwards the exception is thrown. So defining the default port number leads to another behavior than not defining any port number!

Nevertheless if you need to define a explicit port number for your server, simply avoid the space after the comma and your connection string looks pretty.