I have had difficulty creating a connection string in c# that will connect to a remote SQL server using a public IP, named instance and a port number (other than 1433). Anyone know how to do that?
In SQL Server Configuration Manager, expand SQL Server Network Configuration and then select Protocols for <instance name> on the left panel. To identify the TCP/IP Port used by the SQL Server Instance, right click on TCP/IP and select Properties from the drop down as shown below.
Try this, replacing 666 with the port number you want to use, 190.190.200.100 with the IP address you want, etc.:
Data Source=190.190.200.100\MyInstance,666;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
Using the servername tcp:<public IP>,<port>
, as documented in SqlConnection.ConnectionString:
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:
server=tcp:servername, portnumber
When specifying a local instance, always use (local). To force a protocol, add one of the following prefixes:
np:(local)
,tcp:(local)
,lpc:(local)
Data Source must use the TCP format or the Named Pipes format.
TCP format is as follows:
tcp:<host name>\<instance name>
tcp:<host name>,<TCP/IP port number>
If you use the tcp:<host name>\<isntance name>
the SQL Browser service connection is required (port 1433) therefore is better to use the later format, with explicit port name:
Data Source=tcp:1.2.3.4,1234;User Id=...; Password=...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With