Remotely connecting to SQL Server 2005/2008 Express

From WebWatchBotWiki

Jump to: navigation, search

SUMMARY
Configure a server running SQL Server 2005/2008 Express to accept connections from a remote client.



RESOLUTION
On the WebWatchBot Server:

  1. Ensure that the SQL Browser service is set to start "Automatically" and is running - this will broadcast and make SQL Server available to the network.
  2. Ensure "Mixed Mode" authentication is enabled. Open SQL Server Management Studio (SSMS):
    1. Start menu
    2. All Programs
    3. Microsoft SQL Server 2005/2008
    4. SQL Server Management Studio
    5. Right-click on the machine name in the Object explorer: MACHINNAME (SQL Server 9.x...)
    6. Select "Server Properties"
    7. Under the security section, ensure that SQL Server and Windows Authentication mode is selected.
  3. Open the SQL Server Configuration Manager:
    1. Start menu
    2. All Programs
    3. Microsoft SQL Server 2005/2008
    4. Configuration Tools
    5. SQL Server Configuration Manager
    6. A new window will open
    7. On the left-hand pane, expand the node for SQL Server 2005/2008 Network Configuration
    8. Select “Protocols for SQL Express”
    9. On the right-hand pane, double-click “TCP/IP”, change the value for “Enabled” to “Yes”.
    10. Click OK
    11. You will receive a notice that the service needs to be restarted. Click OK
    12. On the left-hand pane, select “SQL Server 2005 Services”
    13. On the right-hand pane, select “SQL Server (Express)”
    14. On the tool bar click the “Restart Service” button (looks like a curved arrow) and the service will restart
  4. Ping the WebWatchBot server from the remote client to ensure you can reach that server.


On the remote client, test the connection with the following:

  1. Start menu
  2. Control Panel
  3. Administrative Tools
  4. Data Sources (ODBC)
  5. Select the "SYSTEM DSN" tab
  6. Click the "Add" button
  7. Select "SQL Server"
  8. Enter a name (anything really), and for server enter "machinename\sqlexpress"
  9. Click Next
  10. Select "With SQL Server authentication..."
  11. Check the box for "Connect to SQL Server to obtain..."
  12. Enter the username and password for the SQL SErver instance.
  13. Click Next. At this point, if it cannot connect, you will receive an error message.
Retrieved from "http://www.poorperformance.com/wiki/index.php?title=Remotely_connecting_to_SQL_Server_2005/2008_Express"
Personal tools