Monday, January 22, 2007

SQL Server 2005 Express Remote Connection Setup on Workgroup XP

Here are some of the steps needed for me to get connected from a remote workstation to an instanse of SQL Express on another XP computer. These stpes also applies when you are connecting to the databse using the ODBC SQL driver.

  1. Firewall - To eliminate the possibility of a firewall getting in the way, first test everything without it. Then enable. GO to the bottom of this post and there will be additional info on how to make an exception to the firewall for SQL Express.
  2. If using SQL Authentication, this is not enabled. Download the SQL Server Management Express then right click on the server and enable Mixed mode authentication. Otherwise you get a "security error"
  3. It is best if the server is registered under DNS or ActiveDirectory, but if that is not the case then runt the SQL Server Configuration Manager and activate the SQL Server Browser. You need to Enable this service.
  4. KEY TO THE BIG BIG MISTERY: The default SQL Express installation REQUIRES that the SQLBROWSER be enabled on the Windows Firewall (not just allowing Port 1433 if you came from out-of-the-box isntall with SQL 2000). The standard location for this is at C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe Make sure to set an Application Exception in the Windows Firewall even if you are accessing it locally!
  5. Also while you are at it, make sure that TCP/IP protocols are all enabled as below.

This was taken from Microsoft Web Site http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm

To enable TCP/IP:From the Start menu, choose All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager. Optionally, you can open Computer Manager by right-clicking My Computer and choosing Manage.

In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager.Expand SQL Server 2005 Network Configuration, and then click Protocols for InstanceName.In the list of protocols, right-click the protocol you want to enable, and then click Enable.

The icon for the protocol will change to show that the protocol is enabled.To enable the firewall:Click Start, click Control Panel, and then click Network Connections.From the navigation bar on the left, click Change Windows Firewall settings.On the Exceptions tab, in the Programs and Services box, you will probably see that SQL Server is listed, but not selected as an exception. If you select the check box, Windows will open the 1433 port to let in TCP requests.

Alternatively, if you do not see SQL Server listed, do the following:Click Add Program.Click Browse.Navigate to drive:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BINNAdd the file sqlservr.exe to the list of exceptions.

If using SQL Authentication, this is not enabled. Download the SQL Server Management Express then right click on the server and enable Mixed mode authentication. Otherwise you get a "security error"

It is best if the server is registered under DNS or ActiveDirectory, but if that is not the case then runt the SQL Server Configuration Manager and activate the SQL Server Browser. You need to Enable this service.

Also while you are at it, make sure that TCP/IP protocols are all enabled.

Post a Comment