Setting a port using SqlConnectionStringBuilder?

Question

I ran into a problem. I need to specify a port number for a local installation of SQL Server 2008 R2. So far, I have been trying to use SqlConnectionStringBuilder with a data source set to .\TESTSERVER, 1433 , which according to all documentation should connect me to the TESTSERVER server on port 1433 (by default).

The connection string looks like this:

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

However, I get an error message:

When establishing a SQL Server connection, a network-related or specific instance error occurred. The server was not found or was not available. Verify that the instance name is correct and configure SQL Server for a remote connection. (Supplier: TCP provider, error: 0 - the connection could not be completed because the target machine actively refused it.

After checking the connection string using SqlConnectionStringBuilder.ToString() it appears almost as MSDN suggests. For some reason, it wraps double quotes ONLY around the data source and nothing else. However, it may just be a debugger that removes external quotes, since it is stored in a string type. I also confirmed that the SQL server is accessible without specifying a port, and it is. Finally, I confirmed that the server is allowed to accept remote connections. Given that this instance of SQL Server is installed locally using the default port and on my development computer, I could not imagine why I was getting this error.

Is it because SqlConnectionStringBuilder overrides my port with its own port? Do I need to open ports on my firewall for some reason? Knowing his completely local installation, he should not run into firewall problems. I would prefer not to manually create a connection string. Not that it was complicated, it just adds a layer of complexity to my code, which I would rather not have if it is not needed.

Any help would be greatly appreciated. Thanks!

EDIT:

After a long recoding of the SqlConnectionStringBuilder syntax, it looks like it is processing invalid parameters, passing it then to the connection string surrounded by quotation marks. I assume this is due to the fact that it breaks the connection string. My question still remains: is there a way to pass the port through SqlConnectionStringBuilder or will I have to create it myself?

Answer 1

TL DR

remove the space before the port number in the data source line:

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

and let it look like

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

---

Long answer

After a little game, you can omit the extra quotation marks by removing the space 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 went deep into network communication and found out that if you do not transmit the port number, it first tries to connect to port 1433 (as usual), but it will remain unconnected. Then he tries to connect udp to port 1434 and receives from his dynamic port number, which will be used for the second tcp connection, in which data will be transmitted.

Using the 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 

Using the explicit port number, I will see only the first data, and after that an exception will be thrown. Thus, defining a default port number leads to a different behavior, rather than defining a port number!

However, if you need to determine the explicit port number for your server, just avoid the space after the comma, and your connection string looks pretty.

Answer 2

Just take a look at http://www.connectionstrings.com/sql-server-2008

The SQL Server 2008 connection string does not contain additional quotation marks in the name of the server. The connection string must be

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

Answer 3

MSDN Property SqlConnection.ConnectionString

The name or network address of the instance of SQL Server to connect to. The port number can be specified after the server name:

Example: MyDataSource, 1433

Answer 4

For me, this works as follows:

 //read instance builder.DataSource.Split(new Char[] { ',' })[0] //read port number builder.DataSource.Split(new Char[] { ',' })[1] //write builder.DataSource = builder.DataSource.Split(new Char[] { ',' })[0] + ",1234";