danielwertheim

danielwertheim


notes from a passionate developer

Developer that lives by the mantra "code is meant to be shared".

Share


Tags


Disclaimer

This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

Recipe - Allow remote connections to SQL-Server Express 2014 on Windows 2012R2

Daniel WertheimDaniel Wertheim

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

  1. Enable remote connections (right click server instance: Properties -> Connections)
  2. 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)

sql-managementstudio-allow-remote

In SQL-Server Configuration manager:

  1. Enable TCP-connections
  2. Edit properties for TCP-connections: - Ensure that only IP4 and IP6 was enabled.
  3. No specific ports, nor fixed or dynamic.
  4. IP Address for IP4: 0.0.0.0
  5. IP Address for IP6: ::
  6. Go to the botom of IP configurations, until you see IPAll.
  7. TCP Dynamic Ports: 0(will re-seed it)
  8. TCP Port: 1433
  9. Restart the service
  10. 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

sql-connectionmanager-tcp

sql-connectionmanager-tcp-properties

On local machine

You should now be able to connect using e.g. Management studio on your local machine, using: [IP or name],1433.

sql-managementstudio-connect

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

Developer that lives by the mantra "code is meant to be shared".

Comments