SQL 2008 Express remote connections

If you have the full version of SQL server 2005 or higher you do not need to follow these steps.  This is for the Express version only.

You must have the following requirements installed.  You may already have them but if not, click on the links below to dowload the installation files.
  They should be installed in the order shown below.

    1. Dot NET framework Version 3.5 with SP1 (Click here to download)

    2. Windows PowerShell (Click here to download) For Windows XP

    3. Sql Server Management Studio or SSMS Express (Click here to download)

To enable remote connection on SQL Server 2008 Express, see the step below:

  1. Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
  2. Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.
  3. (Optional) Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Note: In SQL Server 2008 Express, there isn’t SQL Server Surface Area Configuration so you have to configure from SQL Server Configuration Manager instead.

Note: Unless you are using a Domain username you must reset the ‘SA’ user's password to something you know.   Then on the client's computers you need to connect to the database using SQL Server Authentication NOT Windows Authentication.   Use the user sa and the new password that you created to connect from the workstations (client), or you can create an new user for this purpose (Recommended).  The bottom line is that you CANNOT use Windows Authentication unless you are on a domain and your users have domain access. Domain level networks are not common in small shops.

 

Step-by-step

  1. Open SQL Server Configuration Manager. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
    SQL Server Configuration Manager
  2. On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service. Otherwise, you can skip to step 6.
    SQL Server Browser Service
  3. Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.
    Set Startup Account
  4. On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.
    Set Start Mode to Automatic
  5. Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.
    Start SQL Server Browser Service
  6. On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.
    Protocols for SQL EXPRESS
  7. Right-click on TCP/IP and select Enable to enable the protocol.
    Enable TCP/IP protocol
  8. There is a pop-up shown up that you have to restart the SQL Service to apply changes.
    Need to Restart SQL Server Service
  9. On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.
    Restart SQL Server Service
  10. Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.
    Open Microsoft SQL Server Management Studio
  11. Right-click on the SQL Server Instance and select Properties.
    Open Server Properties
  12. On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.
    Change Authentication to SQL Server and Windows Authentication
  13. Again, there is a pop-up shown up that you have to restart the SQL Service to apply changes.
    Need to Restart SQL Server Service
  14. Right-click on the SQL Server Instance and select Restart.
    Restart SQL Server Service
  15. That’s it. Now you should be able to connect to the SQL Server 2008 Express remotely.

Add Feedback