CODEWITHSUNDEEP
c#sql-servertcp
Allstar
Allstar

Reputation: 439

how to connect to remote SQL Server Express over internet?

I have a Windows 10 PC with SQL Server Express, I have setup remote connections, allocated a fixed port (express, 22172, removed TCP Dynamic Ports under IPALL), set an inbound rule for the port etc.

I can connect and manipulate the database from a Linux PC on the same network (on the same LAN router), using:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = @"192.168.1.22\SQLEXPRESS, 22172";
builder.InitialCatalog = "<database>";
builder.ItegratedSecurity=  "sa";
builder.Password = "<password>"

string connectionString = builder.ConnectionString;

So, the normal firewall inbound rules, remote connection settings etc work, correct? I now want to be able to make that same connection, from a Windows PC that is on a different internet (from a different location/town..)

I have:

I have switched off the firewall, and still get the error: "(provider: TCP Provider, error 40 - Could not open a connection to SQL Server)

I try these connection string options:

        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = @"tcp:xxx.xxx.x.xx\SQLEXPRESS, 22172";
        builder.InitialCatalog = "<database>";
        builder.UserID = "sa";
        builder.IntegratedSecurity = false;
        builder.Password = "<password>";

        sqlConnect = builder.ConnectionString;

I have also tried these variations:

        builder.DataSource = @"x.xxx.x.xx\SQLEXPRESS, 22172";
        builder.DataSource = @"tcp:xxx.xxx.x.xx, 22172";
        builder.DataSource = @"xxx.xxx.x.xx, 22172";

Also these connection strings:

        sqlConnect = @"Data Source = tcp:xxx.xxx.x.xx\SQLEXPRESS,22172; Initial Catalog = <database>; User ID = sa; Password = <password>";
        sqlConnect = @"Data Source = xxx.xxx.x.xx\SQLEXPRESS,22172; Initial Catalog = <database>; User ID = sa; Password = <password>";
        sqlConnect = @"Data Source = tcp:xxx.xxx.x.xx,22172; Initial Catalog = <database>; User ID = sa; Password = <password>";
        sqlConnect = @"Data Source = xxx.xxx.x.xx,22172; Initial Catalog = <database>; User ID = sa; Password = <password>";

And for good measure, this one:

sqlConnect = @"Data Source=xxx.xxx.x.xx\SQLEXPRESS,22172;Initial Catalog=<database>;Persist Security Info=True;User ID=sa;Password=<password>;";

I have scanned all relevant questions on SO etc - somewhere I am doing something small wrong? Please help

Output when running select * from sys.tcp_endpoints on host PC SQL: Thus, ip_address is NULL for both, port is 0 for both, and is_dynamic_port is 1 for both - is that the relevant information?

Dedicated Admin Connection  1   1   2   TCP 2   TSQL    0   STARTED 1   0   1   NULL
TSQL Default TCP            4   1   2   TCP 2   TSQL    0   STARTED 0   0   1   NULL

I have tried several times to change the dynamic port to static, I have removed all 0 or port numbers in configuration manager, but still get a 1 for _is_dynamic_port. Event Viewer shows:

Server is listening on [ 'any' <ipv4> 22172].

Same message if I run the following in SQL:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'

GO

Why does SQL settings remain on dynamic ports?

Upvotes: 1

Views: 1228

Answers (2)

Allstar
Allstar

Reputation: 439

Check that your ISP does not block ports / all ports. That is what stopped me.

Upvotes: 0

PIoneer_2
PIoneer_2

Reputation: 496

From your work, I suppose you set it all on the Win PC. So,...

  1. What about the Linux machine? Does it allow to do outbound connections to 192.168.1.22?
  2. Tak a look at the SqlConnectionStringBuilder.IntegratedSecurity. It should have a bool value. false if user and password should be used for the authentication (https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder.integratedsecurity?view=dotnet-plat-ext-6.0).
  3. Tak a look at the SqlConnectionStringBuilder.UserID. It is missing in both of your code snippets. But mentioned at created connection strings Persist Security Info=True;User ID=sa;Password=<password>; as «User ID», «user» и «uid». Please, see here (https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder.userid?view=dotnet-plat-ext-6.0).

Upvotes: 1

Related Questions