SQL Server name using port number

Question

I am using SQL server express 2014. I used the below connection string as server name in SQL Express. but it failed.

[my servername],1433\SQLEXPRESS

In sql server 2012, I can connect the connection string in the format of [my servername],1433

Could you please anyone suggest how do I specify a port number in a connection string for SQL Express server?

Answer 1

First of all, if you did not change the port manually, SQLEXPRESS is NOT listening on 1433 that is reserved for default instance, it uses dynamic port that you can find in Configuration Manager or in SQL Server error log. Here you can see both methods with pictures: Identify SQL Server TCP IP port being used.

Second, there is no need to specify instance name + port, in fact if you do it, instance name is effectively ignored, so when you use

[my servername]\SQLEXPRESS,1433

you'll try to connect to

[my servername],1433

That is because to connect to server you need to know it's address and port, so or you use

myServer,port

or you use

myServer\instanceName

In the first case the underlying network library has all information it needs to connect(IP + port). In the second case SQL Server Browser need to be started. It's SQL Server Browser that will comunicate instance port given instance name.

When your connection string contains both instance name and port, only port is used and instance name is ignored, so if you use a wrong port, even with correct instance name you cannot connect.