0

What is the best way to disable remote connections on a SQL Server Install on Linux (CentOS 7)? I.E. force SSH Tunneling to connect over localhost.

I typically block ports explicitly with IPTables/Firewalld rules, and have 1443 closed off to the outside.

However with MySQL I also tend to close off users to local access only, i.e. 'testUser'@'localhost' and not 'testUser'@'%'. Also, the default if you run mysql-secure-installation is to turn of remote connections as root (SA in SQL Server). This ensures that if my network is breached, they still have to SSH Tunnel into the server.

How do I achieve this?

I was reading this doc and was not able to turn off remote connections when running:

EXEC sp_configure 'remote access', 0 ;  
GO  
RECONFIGURE ;  
GO  
  • Have you tried configuring SQL Serverto only listen on a loopback address? Something like: ‘ipaddress = 127.0.0.1’ – David Browne - Microsoft Jun 11 at 22:37
  • @DavidBrowne-Microsoft No. Sounds like that would be the solution but I can't find any documentation on how to do that. Haven't see documentation that mentions where config files are on Linux or what the transact SQL would look like for that statement. – Aaron Chamberlain Jun 12 at 15:01
1

Thanks to David Browne for pointing me in the right direction. Sometimes knowing what terms to search for is the hardest part. I also got direction from this article

So to change the SQL Server to listen on localhost or 127.0.0.1 all you have to do is add the following configuration to the file /var/opt/mssql/mssql.conf:

[network]
ipaddress = 127.0.0.1

then restart the server with:

sudo systemctl restart mssql-server

If you want to get a complete list of all configuration parameters you can run

/var/opt/mssql/bin/mssql-conf list

Note a sample of the output is like

hadr.hadrenabled                           Allow SQL Server to use availability groups for high availability and disaster recovery
language.lcid                              Locale identifier for SQL Server to use (e.g. 1033 for US - English)
memory.memorylimitmb                       SQL Server memory limit (megabytes)
network.disablesssd                        Disable querying SSSD for AD account information and default to LDAP calls
network.enablekdcfromkrb5conf              Enable looking up KDC information from krb5.conf
network.forceencryption                    Force encryption of incoming client connections
network.forcesecureldap                    Force using LDAPS to contact domain controller
network.ipaddress                          IP address for incoming connections

So you can add any of those to the /var/opt/mssql/mssql.conf file. The syntax is that the prefix before the dot goes in square brackets with the suffix listed below (see above for example).

To test it was working I checked:

1) That the applications using it were up

2) Running sudo nmap -PN <servername> to determine if the port was open

3) Trying to open a connection from a remote computer where I previously could with sqlcmd -S <servername> -U <username> which threw the error

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.