170

I use the following connection string in SQL Server Management Studio. It failed to connect:

mycomputer.test.xxx.com:1234\myInstance1

But the following one is ok:

mycomputer.test.xxx.com\myInstance1

So how do I specify a port number in a connection string?

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
smwikipedia
  • 61,609
  • 92
  • 309
  • 482

3 Answers3

319

Use a comma to specify a port number with SQL Server:

mycomputer.test.xxx.com,1234

It's not necessary to specify an instance name when specifying the port.

Lots more examples at http://www.connectionstrings.com/. It's saved me a few times.

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
  • 10
    Each instance must be on a separate port, so port=instance in Sql server. I never knew this ",port" until now, though - thank you. – Daniel Williams Mar 14 '11 at 04:35
  • 2
    @Daniel: yep, you need to specify either port or instance. – Michael Petrotta Mar 14 '11 at 04:37
  • 11
    Note: instance name is *ignored* when port is specified. So "mycomputer.test.xxx.com\AnyOldRubbish,1234" still works. – gbn Mar 14 '11 at 05:32
  • 1
    How does this work if the default (MSSQLSERVER) instance isn't running on 1433? – Mark Richman Mar 16 '17 at 15:58
  • "not necessary to specify an instance name " That was key for me. FreeTDS on Raspi will crash if you put in the instance name and a port. Never knew you could skip the instance name, and that's the only way to get it working in FreeTDS. – Gabe Halsmer Oct 20 '17 at 17:31
  • Having connectionString="Server=address\SQlExpress,1433" won't work. – Istorn Feb 18 '19 at 08:42
  • Having MSSQLLocalDB, SQL Express and SQL Server 2019 Developer, all with default port set: specifying port in any case won't work, at least using "Data Source=". Always need to specify instance, without port. If port is specified, following exception is shown: Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to 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. – Sebastian Jun 09 '21 at 12:46
  • Anyone have a link to the specs for this:"It's not necessary to specify an instance name when specifying the port."? Would be nice to know. – dtc Jun 13 '22 at 20:19
  • Ran into this issue today and was immediately disgruntled, I'm glad I'm not alone @PrgTrdr – crock Aug 30 '22 at 19:24
  • For example, you would use this format for an OLEDB data connection in SSIS. – JPTremblay Nov 15 '22 at 15:13
12

For JDBC the proper format is slightly different and as follows:

jdbc:microsoft:sqlserver://mycomputer.test.xxx.com:49843

Note the colon instead of the comma.

Shanerk
  • 5,175
  • 2
  • 40
  • 36
  • 1
    I have turned from C# to Java, and this answer saves me. Again. – smwikipedia Apr 03 '15 at 01:10
  • 1
    Awesome, glad it helped. Not exactly intuitive documentation in Java for the JDBC connection string. Not sure why it's different either, which is annoying. – Shanerk Apr 06 '15 at 17:29
3

The correct SQL connection string for SQL with specify port is use comma between ip address and port number like following pattern: xxx.xxx.xxx.xxx,yyyy

David Thang
  • 31
  • 2