1

Topic: Access to MS a SQL server express through the Internet.

It was required to me to get somehow access to house MS SQL Server Express 2016 through the Internet.
The circuit of a house network the such: there is ZyXEL Keenetic router (FW v.2) which lifts session PPPoE and distributes an Internet through  and . The computer on which it is twisted SQL the server (192.168.1.2) is connected To a router on .
That I made:
Included in the server TCP/IP protocol and adjusted ports. In broad gulls received following :
Server is listening on [' any ' <ipv6> 11433].
Server is listening on [' any ' <ipv4> 11433].
Server is listening on [' any ' <ipv6> 53250].
Server is listening on [' any ' <ipv4> 53250].
Server local connection provider is ready to accept connection on [\. \PIPE\SQLLOCAL\SQLEXPRESS].
Server local connection provider is ready to accept connection on [\\.\pipe\MSSQL$SQLEXPRESS\sql\query].
11433 is a static port.
53250 - dynamic.
On a router it is lifted dynamic  NAME.ddns.net. It is adjusted  ports:
Network The Source address Port Assignment Port of destination The Description
PPPoE0 tcp/1433 192.168.1.2 11433 MSSQL
PPPoE0 udp/1433 192.168.1.2 11433 MSSQL (UDP)
PPPoE0 tcp/53250 192.168.1.2 53250 MSSQL_53250
PPPoE0 udp/53250 192.168.1.2 53250 MSSQL_53250 (UDP)
Opened ports in a firewall.
The scanner of ports, shows that the port is opened,  on a domain name - transits, the web a muzzle through the Internet - rises.
I try clings to SQL from the remote computer, produces the code 0 . From the local computer, at that line of connection, incorporates. The firewall in OS - is ungeared.
Line of connection: NAME.ddns.net \SQLEXPRESS, 11433
What do I do not and how to correct?

2

Re: Access to MS a SQL server express through the Internet.

AlVM;
How checked that the port is accessible from an Internet?
telnet ipadress 11433 that shows?
What else dynamic port? Is not present such in a cheekbone, a broad gull completely show, instead of your free interpretation.

3

Re: Access to MS a SQL server express through the Internet.

Broad gull example:
[spoiler]

Date Message
9/18/2017 20:45:04 Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64)
Jul 6 2017 7:55:03 AM
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 15063:)
9/18/2017 20:45:04 UTC adjustment: 5:00
9/18/2017 20:45:04 (c) Microsoft Corporation.
9/18/2017 20:45:04 All rights reserved.
9/18/2017 20:45:04 Server process ID is 20044.
9/18/2017 20:45:04 System Manufacturer: ' ASUS', System Model: ' All Series'.
9/18/2017 20:45:04 Authentication mode is MIXED.
9/18/2017 20:45:04 Logging A SQL Server messages in file ' C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Log\ERRORLOG '.
9/18/2017 20:45:04 The service account is ' NT Service\MSSQL$SQLEXPRESS'. This is an informational message; no user action is required.
9/18/2017 20:45:04 Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
9/18/2017 20:45:04 Command Line Startup Parameters:
-s "SQLEXPRESS"
9/18/2017 20:45:04 A SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on A SQL Server licensing. This is an informational message; no user action is required.
9/18/2017 20:45:04 A SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
9/18/2017 20:45:04 Detected 8129 MB of RAM. This is an informational message; no user action is required.
9/18/2017 20:45:04 Using conventional memory in the memory manager.
9/18/2017 20:45:04 Default collation: Cyrillic_General_CI_AS (Russian 1049)
9/18/2017 20:45:04 Buffer pool extension is already disabled. No action is necessary.
9/18/2017 20:45:04 InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
9/18/2017 20:45:04 Implied authentication manager initialization failed. Implied authentication will be disabled.
9/18/2017 20:45:04 The maximum number of dedicated administrator connections for this instance is ' 1'
9/18/2017 20:45:04 This instance of A SQL Server last reported using a process ID of 12452 at 9/18/2017 OF 8:44:56 PM (local) 9/18/2017 OF 3:44:56 PM (UTC). This is an informational message only; no user action is required.
9/18/2017 20:45:04 Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
9/18/2017 20:45:04 Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
9/18/2017 20:45:04 Database Instant File Initialization: it is disconnected. For security and performance considerations see the topic ' Database Instant File Initialization ' in SQL Server Books Online. This is an informational message only. No user action is required.
9/18/2017 20:45:04 Query Store settings initialized with enabled = 1,
9/18/2017 20:45:04 Starting up database ' master '.
9/18/2017 20:45:04 SQL Server Audit is starting the audits. This is an informational message. No user action is required.
9/18/2017 20:45:04 SQL Server Audit has started the audits. This is an informational message. No user action is required.
9/18/2017 20:45:04 SQL Trace ID 1 was started by login "sa".
9/18/2017 20:45:04 Server name is ' AVM\SQLEXPRESS'. This is an informational message only. No user action is required.
9/18/2017 20:45:04 A self-generated certificate was successfully loaded for encryption.
9/18/2017 20:45:04 Server is listening on [' any ' <ipv6> 11433].
9/18/2017 20:45:04 Server is listening on [' any ' <ipv4> 11433].
9/18/2017 20:45:04 Server is listening on [' any ' <ipv6> 53250].
9/18/2017 20:45:04 Server is listening on [' any ' <ipv4> 53250].
9/18/2017 20:45:04 Server local connection provider is ready to accept connection on [\. \PIPE\SQLLOCAL\SQLEXPRESS].
9/18/2017 20:45:04 Server local connection provider is ready to accept connection on [\\.\pipe\MSSQL$SQLEXPRESS\sql\query].
9/18/2017 20:45:04 Dedicated administrator connection support was not started because it is disabled on this edition of A SQL Server. If you want to use a dedicated administrator connection, restart A SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
9/18/2017 20:45:04 A SQL Server is attempting to register a Service Principal Name (SPN) for the A SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the A SQL Server service. This is an informational message. No user action is required.
9/18/2017 20:45:04 A SQL Server is now ready for client connections. This is an informational message; no user action is required.
9/18/2017 20:45:04 The SQL Server Network Interface library could not register the Service Principal Name (SPN) [MSSQLSvc/AVM:SQLEXPRESS] for the A SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
9/18/2017 20:45:04 The SQL Server Network Interface library could not register the Service Principal Name (SPN) [MSSQLSvc/AVM:11433] for the A SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
9/18/2017 20:45:04 CLR version v4.0.30319 loaded.
9/18/2017 20:45:04 A new instance of the full-text filter daemon host process has been successfully started.
9/18/2017 20:45:04 Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
9/18/2017 20:45:04 Starting up database ' msdb '.

[/spoiler]
From the local machine the telnet drops session, with remote -  produces.

4

Re: Access to MS a SQL server express through the Internet.

1:

5

Re: Access to MS a SQL server express through the Internet.

2:

6

Re: Access to MS a SQL server express through the Internet.

wrote:

From the local machine the telnet drops session, with remote -  produces.

Well and you it does not suggest that it is necessary to look a configuration of a network and all network equipment that you with a cheekbone that try to find the magic button.
Achieve that that the command telnet ip 11433 opened connection.
Check as port , , routers, dmz and other network functions.
From the point of view whining port it is opened, further its power how to be told all.

7

Re: Access to MS a SQL server express through the Internet.

AlVM;
Here so it will be connected:
NAME.ddns.net, 11433
Laziness and long  if shortly that for connection on dynamics, and named  is dynamics, the port 1434 UDP and a triggered browser is necessary still.
Easier to be connected as I showed above, it connects to static port though it is possible also dynamic too .

8

Re: Access to MS a SQL server express through the Internet.

AlVM wrote:

PPPoE0 tcp/1433 192.168.1.2 11433 MSSQL

AlVM wrote:

the Line of connection: NAME.ddns.net \SQLEXPRESS, 11433

You  1433 exterior on 11433 internal, then outside try to incorporate on 11433 and for some reason think that it should work.

9

Re: Access to MS a SQL server express through the Internet.

Col wrote:

... And named  it is dynamics...

For the sake of justice, it not always so
In the abstract case what hinders to assign  the port which is distinct from 1433, and to be connected to a sequel by name , at launched SQL Browser?

10

Re: Access to MS a SQL server express through the Internet.

komrad;
I also speak, long, is lazy and in this case most likely it is not necessary smile

11

Re: Access to MS a SQL server express through the Internet.

Registered at SQL servers standard port 1433, restarted, service "SQL Server Observer" - is launched.
In a router  ports 1433 (TCP), 1434 (UDP).
Tried and so: NAME.ddns.net, 1433 and with copy instructions, and to the dynamic address - does not start up, and all.

12

Re: Access to MS a SQL server express through the Internet.

By the local machine all four lines of connection work.

13

Re: Access to MS a SQL server express through the Internet.

To check up communication, lifted a web muzzle on uTorrent and  port (adjusted on 1010) - result positive from the remote machine comes and works. Why does not work MS SQL?

14

Re: Access to MS a SQL server express through the Internet.

MS SQL Express edition it is traditional (from the first version - 2005) does not provide remote connection.
Or in 2016 express something changed?

15

Re: Access to MS a SQL server express through the Internet.

KohrAhr wrote:

MS SQL Express edition it is traditional (from the first version - 2005) does not provide remote connection.

All provides, it is necessary to include TCP/IP protocol only

16

Re: Access to MS a SQL server express through the Internet.

AlVM wrote:

to check up communication, lifted a web muzzle on uTorrent and  port (adjusted on 1010) - result positive from the remote machine comes and works. Why does not work MS SQL?

By the local machine to check up pluggability on TCP/IP using sqlcmd here so:

 sqlcmd-S tcp:server\instans-E 

, If at you authentification  instead of - to use-U and-P
Outside to try to be connected only on host name or IP And to port number
I do not eliminate probability of that standard ports of type TCP1433 are cut/filtered by your provider

17

Re: Access to MS a SQL server express through the Internet.

komrad wrote:

By the local machine to check up pluggability on TCP/IP using sqlcmd here so:

 sqlcmd-S tcp:server\instans-E 

, If at you authentification  instead of - to use-U and-P
Outside to try to be connected only on host name or IP And to port number
I do not eliminate probability of that standard ports of type TCP1433 are cut/filtered by your provider

If without port instructions - that produces Sqlcmd: an error - Microsoft ODBC Driver 13 for a SQL Server: Network interfaces of a SQL Server: it is not possible to find out the specified server/copy [xFFFFFFFF].
If with port: Sqlcmd: an error - Microsoft ODBC Driver 13 for a SQL Server: Supplier TCP: operation Wait time effused.
.

18

Re: Access to MS a SQL server express through the Internet.

Tried  80 port - too most.

19

Re: Access to MS a SQL server express through the Internet.

AlVM wrote:

Registered at SQL servers standard port 1433, restarted, service "SQL Server Observer" - is launched.
In a router  ports 1433 (TCP), 1434 (UDP).

Also what, in sequel broad gulls it is written, what he listens 1433?
From the local machine now you are connected on TCP 1433?

AlVM wrote:

Tried  80 port - too most.

Not  anything,  connection at first on the server.

20

Re: Access to MS a SQL server express through the Internet.

AlVM wrote:

to check up communication, lifted a web muzzle on uTorrent and  port (adjusted on 1010) - result positive from the remote machine comes and works. Why does not work MS SQL?

Where lifted web for uTorrent?
On a router or on a host with a sequel?

21

Re: Access to MS a SQL server express through the Internet.

komrad wrote:

it is passed...
Where lifted web for uTorrent?
On a router or on a host with a sequel?

On a computer with a sequel - .

22

Re: Access to MS a SQL server express through the Internet.

alexeyvg wrote:

it is passed...
Also what, in sequel broad gulls it is written, what he listens 1433?
From the local machine now you are connected on TCP 1433?
it is passed...
Not  anything,  connection at first on the server.

On a local computer - the line of connection NAME.ddns.net, 80 - fulfills normally.

23

Re: Access to MS a SQL server express through the Internet.

Not clearly, what for IPv6 it is included,  and other. I included static IPv4 the address.  through NAT only TCP on 1433 port of the server. Any UDP and browsers it is not necessary. All worked. What hinders to deliver  and to look at packets?

24

Re: Access to MS a SQL server express through the Internet.

I will try to look from other computer.

25

Re: Access to MS a SQL server express through the Internet.

AlVM wrote:

it is passed...
On a local computer - the line of connection NAME.ddns.net, 80 - fulfills normally.

It that such, "NAME.ddns.net, 80" how you use this line, and generally, what for a format?
sqlcmd-S tcp:server\instans-E on the server works?
Here when earns, means, your sequel is adjusted on connection on tcp protocol, using a browser for port obtaining.
Or so, if want to specify port number, without contacting a browser:
sqlcmd-S tcp:server\instans, port number-E
Port number look in  broad gulls, at the same time checking, whether listens to a sequel tcp
And further it is already possible to start to potter with firewalls, routers etc.