Time for a small recipe of what I needed to do, to get a SQL-Server Express 2014 instance, running on Windows 2012R2, accessible from a remote machine.
On remote machine
In SQL-Server Management studio
- Enable remote connections (right click server instance:
Properties -> Connections)
- If you can not use a windows account for logging in, then create an SQL-server login and ensure to
enable mixed mode(right click server instance:
Properties -> Security)
In SQL-Server Configuration manager:
- Enable TCP-connections
- Edit properties for TCP-connections: - Ensure that only IP4 and IP6 was enabled.
- No specific ports, nor fixed or dynamic.
- IP Address for IP4:
- IP Address for IP6:
- Go to the botom of IP configurations, until you see
- TCP Dynamic Ports:
0(will re-seed it)
- TCP Port:
- Restart the service
- Add an inbound rule in the firewall for port
1433. Note, if you need support for dynamic ports, the inbound rule should be against the applicationprocess instead, that is:
C:\Program FilesMicrosoft SQL ServerMSSQL12.SQLEXPRESS2014MSSQLBinnsqlservr.exe
On local machine
You should now be able to connect using e.g. Management studio on your local machine, using:
[IP or name],1433.
A small tip, that lead me to see what the server was listening on to see that I at least had a chance to get connected, was to inspect the logs, which for my needs was satisfied with some T-SQL:
use master go exec xp_readerrorlog
That was all I did. Hope it can come in handy.