How to: Configure Express to accept remote connections – SQL Server Express WebLog – Site Home – MSDN Blogs
Posted by Jeroen Pluimers on 2011/04/28
when developing embedded Windows software, SQL Express can be a good option.
However, embedded usually means you can’t install a big tool-set (like SQL Server Management Studio or SQL Server Profiler) on the target system.
So if you want to monitor or profile it, you need remote access to your SQL Express instance (usually SQLEXPRESS).
The How to: Configure Express to accept remote connections – SQL Server Express WebLog article on the SQL Server Express WebLog and the KB article How to configure SQL Server 2005 to allow remote connections explain the details.
This is a short summary:
- Enable TCP/IP using the SQL Server Configuration Manager
(you can do this using the registry too)
- Enable the SQL Browser using the SQL Server Configuration Manager
- Open your firewall for the TCP ports (more difficult) or SQL Server process (easier)
- Enable mixed mode login authentication from the registry:
HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/LoginMode = 1 integrated security, 2 mixed.
- Make sure that SA can login, and give it a strong password using this in SQLCMD:
ALTER LOGIN sa ENABLE
ALTER LOGIN sa WITH PASSWORD=’Som3StrongP@assword’
- Restart the SQL Server Service
- Start the SQL Browser Service
Finally connect to your SQL Express server by using SQLCMD:
SQLCMD –S machinename\SQLEXPRESS –Usa –PSom3StrongP@assword