14

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?

Improve this question
2
  • Take the quotes out of the data source string. Also the `.` prefix. Commented Jul 2, 2012 at 7:58
  • It also fails with any combination of 127.0.0.1, 1433, localhost, 1433, etc. The thing is thought I'm using SqlConnectionStringBuilder which is adding the quotes ONLY when I have the dbname,port syntax set on the data source field. If I was building the connection string manually it wouldn't be a problem. Commented Jul 2, 2012 at 8:04

4 Answers 4

Reset to default
12

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+1 This is the correct answer, but I really wish you'd edit your answer to just a single sentence followed by a single line of code as an example.
@LeeGrissom: Hopes this makes it a little bit clearer.
Actually, that's better than my suggestion. :)
0

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

SQL Server 2008 connection string does not contain extra quotes around the server indtance name. Your connection string should be 

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

1 Comment

Once again, I'm using the SqlServerConnectionStringBuilder. I am not personally building this. I'm inquiring as to why it's happening when I'm using Microsoft's own solution to reliable connection string building.
0

MSDN SqlConnection.ConnectionString Property

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

Example : MyDataSource,1433

Improve this answer

Comments

0

For me its work like this:

//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";
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.