Configuring SQL Server Express 2005

Solution 1:

First, I'll use Windows Authentication only all your users are members of the same Active Directory domain, if not I'll use SQL Server Authentication (it's less secure but works better when not using a domain).

Then, make sure that SQL Server is configured to accept external connections. Open the Surface Area Configuration, go to configuration for services and connections, and in Database Engine > Remote connections check that Local and Remote connections is selected (usually you only need to enable TCP/IP connections).

The rest of requisites varies depending on how you installed it:

• If you changed the installations options to use the default instance (the same configuration that the Standard/Enterprise versions use by default) you need to have port 1433 TCP opened in the firewall (unless you changed the default port).

• If you used the default installation options SQL Server Express won't use the default instance, but instead it uses servername\SqlExpress. This requires having the SQL Browser service enabled and the port 1434 UDP opened in the firewall. Also you need to specify servername\SqlExpress or ip_number\SqlExpress when configuring the connection in the clients instead of just using the server name or IP.

  Note that AFAIK you can change only the instance at installation time, if you want to use the default instance you need to uninstall and install it again.

Finally, if you use queries that involve accessing data from different servers at the same time (i.e. you have different data in different servers), in the Surface Area Configuration go to the configuration for features and in Ad Hoc Remote Queries check Enable OPENROWSET and OPENDATASOURCE support.