SQL connection with public IP address, named instance and port number

Question

It was difficult for me to create a connection string in C # that would connect to a remote SQL server using a public IP address, named instance, and port number (except 1433). Does anyone know how to do this?

Answer 1

Try this by replacing the number 666 with the port number you want to use, 190.190.200.100 with the desired IP address, etc.:

Data Source=190.190.200.100\MyInstance,666;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword; 

Answer 2

Using the server name tcp:<public IP>,<port> , as described in SqlConnection.ConnectionString :

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

server=tcp:servername, portnumber

When specifying a local instance, always use (locally). To force the protocol, add one of the following prefixes:

np:(local) , tcp:(local) , lpc:(local)

The data source must use the TCP format or the Named Pipes format.

The TCP format is as follows:

• tcp:<host name>\<instance name>
• tcp:<host name>,<TCP/IP port number>

If you use tcp:<host name>\<isntance name> , you need to connect to the SQL Browser service (port 1433), so it is better to use a later format with an explicit port name:

 Data Source=tcp:1.2.3.4,1234;User Id=...; Password=... 

Answer 3

 connectionString="Database=pub;Server=192.168.1.1\INSTANCE,1746;Trusted_Connection=yes;" 

Or you can use username/password instead of a reliable connection.

Answer 4

This site has never failed me.

And I'm going to formulate the obvious here, but as a rule, it is a bad idea to expose your sql server on the Internet .. (if you are not using a VPN)