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:
0.0.0.0 - IP Address for IP6:
:: - Go to the botom of IP configurations, until you see
IPAll. - TCP Dynamic Ports:
0(will re-seed it) - TCP Port:
1433
- 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.
//Daniel