MSSQL | Configure SQL Server for Remote TCP/IP Connections
These instructions work for Windows Server 2016 and SQL Server 2016 and SQL Server Express. You can connect remotely through port 1433 from Windows and/or Linux instances.
Configure SQL Server for Remote TCP/IP Connections
Connecting remotely to SQL Server requires that you switch on SQL Server authentication, enable the TCP/IP protocol, and restart SQL Server so that the changes take effect.
Following that – you employ SQLCMD (version 13) running on a remote instance to connect and thus validate your handy-work.
It is good practice to create a user specifically for remote connection.
Configuring SQL Server for Remote Connection
After you have created the Windows Server (2016) with SQL Server (use Amazon’s Web Service Instance with SQL Server (or SQL Server Express) expressly installed) and you can configure it for remote connection by following these steps.
- In SQL Management Studio / Security / Logins create a user/password for the remote connection (these will be entered in the sqlcmd connection below under the -U and -P parameters.
- In SQL Management Studio on the top line in left bar – you right click and go to properties – then click on security and finally change server authentication to Sql Server and Windows Authentication Mode
- In SQL Configuration Manager go to SQL Server Network Configuration and Protocols for MSSQLServer and ensure that the TCP/IP protocol is enabled.
- Still in the Configuration Manager go to Sql Server Services and ensure that SQL Server Browser start mode is automatic (starts on reboot) and then right click and restart it to make the changes take effect.
- To wrap it all up right click on the SQL Server service (a couple of places above the SQL Server Browser) and restart that.
- Ensure all the changes above are applied and saved
With that done let’s ensure that connections can flow in through port 1433.
Can TCP/IP Traffic Flow through Port 1433
Before connecting – your network must allow connections through port 1433 to the Windows Server running SQL Server.
If you are using Amazon Web Services – you ensure that your security group has an inbound rule allowing traffic for port 1433.
In production environments – best practice would limit these connections to within a private subnet or DMZ in your VPC. If connecting for the first time, keep things as open as possible (by allowing connections from anywhere 0.0.0.0/0) and then constrict it when SQL Server TCP/IP connectivity is assured (fight one battle at a time, my friend).
Install SQLCMD Using Chocolatey or Apt-Get
SQLCMD is a great command line utility useful for testing for SQL Server remote connectivity. Let’s install it in case you haven’t already.
Installing SQLCMD on Windows
It pays to use Chocolatey when installing SQLCMD on a Windows Platform. The example here works for installing Chocolatey and then using Chocolatey to install SQLCMD on Windows 10.
To install chocolatey you need your command (or powershell) shell running as administrator. For winemacs users this is as simple as right-clicking on the run emacs desktop icon and selecting “Run as Administrator”.
Then simply copy and paste the below command.
@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin" @powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"
Now that’s done you can install SQLCMD itself by using the chocolatey you’ve installed as a game-keeper.
choco install sqlserver-cmdlineutils
Installing SQLCMD on Ubuntu 16.04 and 17.04
apt-get will install sqlcmd on Ubuntu and other Linux Debian clones.
sudo apt-get install sqlcmd
Now that you have SQLCMD it is time to connect up to your remote prepared Windows SQL Server 2016 (or Windows SQL Server Express) instance.
Use SQLCMD to remotely connect to SQL Server
Change the IP address to yours. You can use a public ip address, a private ip address, public or private dns names, hostnames – anything that is resolvable over your network to the Windows Server instance running the SQL Server database.
Also change the username and password to suit.
sqlcmd -S 220.127.116.11 -U db-usr-name -P p455w0rd