Often I see questions in forums about how to configure SQL Server Express Edition for remote access.
Below are the steps to allow remote access to a SQL Server 2008 R2 x64 Express Edition instance after the default install steps where performed.
Also included are the additional configuration steps for SQL Server 2008 R2 x64 Express Edition with Advanced Services.
The steps were performed on Windows Server 2008 R2 64bit.
Below you can see that by default, SQL Server Express allocates a Dynamic port when SQL Server starts.
You can either keep this setting or change SQL Server to listen on a fixed TCP port e.g. TCP 1433. This can be achieved by removing 0 from all the ‘TCP Dynamic Ports’ rows and placing the fixed TCP port you want to use on all the ‘TCP Port’ rows below it.
For this demo I will keep SQL Server allocating a Dynamic port on start-up and therefore I have not changed anything on this tab.
Connect to SQL Server Express on the server you installed it.
As below you can see the instance when SQL Server Express is installed is call SQLEXPRESS if you did not change it.
The below should already be enabled, but if it isn’t, then enable it and restart the SQL Server service.
The Port rule I am creating below is for if you installed SQL Server Express with Advanced Services and selected the SQL Server Reporting Services feature.
If you changed the HTTP TCP port from the default on 80, then you should enter the port you are using here.
These steps show you how to give a user access to Reporting Services.
As below, make sure you use localhost and not the name of the SQL Server.
Add the relevent windows user with the permissions they require.
Now test that you are able to connect from a remote computer.
Below shows a remote connection to Reporting Services. Above I used localhost when on the SQL Server, but remotely you should use the SQL Server name.