SQL Server 2008 with SharePoint 2007 – Enable Remote SQL Connections

Installing SharePoint 2007 (WSS or MOSS) to use a SQL Server 2008 database is straight forward.  Other than differences in the initial SQL Server 2008 setup routine, there are very few differences to a SQL Server 2005 installation. 

One of the differences that I found involves enabling remote SQL connections.  For SharePoint 2007 to work properly, remote connections should be enabled over Named Pipes and TCP/IP.  Remote connections are disabled by default in both SQL Server 2005 and SQL Server 2008.

Enabling Remote Connections in SQL Server 2005

In SQL Server 2005, you enable remote connections by configuring SQL Server using the SQL Server Surface Configuration Tool, as shown below:

  1. Open the SQL Server Surface Area Configuration Tool (Start > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
  2. Click the link titled Surface Area Configuration for Services and Connections
  3. Select the Database Engine > Remote Connections node in the tree view
  4. Turn on the option for Local and remote connections > Using both TCP/IP and named pipes.
  5. Click the OK or Apply button (you’ll be warned that these settings will not take effect until the Database Engine is restarted)
  6. Restart the Database Engine (SQL Server Service)

 SQL Server 2005 Surface Area Configuration

Enabling Remote Connections in SQL Server 2008

In SQL Server 2008, the SQL Server Surface Configuration Tool is no longer part of the product.  It has been replace with the SQL Server Configuration Manager.  I personally find the old version to be more user-friendly, but either way, it gets the job done.  To enable remote connections in SQL Server 2008:

  1. Open the SQL Server Configuration Manager (Start > SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager
  2. Navigate to the SQL Server Network Configuration > Protocols for MSSQLSERVER node in the tree view
  3. Enable TCP/IP and Named Pipes (you’ll be warned that these changes will not apply until you the service is shut down)
  4. Restart the SQL Server Service

 SQL Server 2008 Configuration Manager

SharePoint 2007 Service Pack 1 Required

Please note that to run SharePoint 2007 on Windows Server 2008 or SQL Server 2008, you must have Windows SharePoint Services 3.0 Service Pack 1 and Microsoft Office Servers Service Pack 1 (if you’re running MOSS) installed on your SharePoint WFE server.

Related Links

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

Advertisements

4 responses to “SQL Server 2008 with SharePoint 2007 – Enable Remote SQL Connections

  1. Hey,,,really useful information on Sharepoint 2007.Thanks for sharing.I was confused before about enabling remote sql connections.But now i am clear. Thanks again

  2. I’ve seen this mentioned elsewhere but haven’t seen what the issues are with not doing it. Do you know what the side effects are if you forget to do this? Thanks for your post.

  3. Thank You, problem solved.

  4. @ Alex: it is documented in various Microsoft articles as a required step so I would certainly recommend you carry this out. I wish I could be of more use and tell you what happens if you forget to do so 🙂

    If you are interested, I have documented a process for migrating from SQL server 2005 to SQL Server 2008 within a MOSS farm over at http://mossblogger.blogspot.com/. Feel free to check it out if it is of interest to you.

    Cheers, Ben.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s