Support‎ > ‎

Configure MS SQL Server 2005 Express Edition Remote Access ( mixed mode )

MS SQL Server 2005 remote access configuration is different from MS SQL Server 2000. Remote access to MS SQL Server 2005 with user and password is required and necessary for any database applications. If MS SQL Server 2005 is originally configured for windows authentication, configuration for remote access ( mixed mode or SQL Server authentication ) needs a little more extra work to get it done.

Here is how to get this done.

 

1. Configuration in SQL Server Management Studio

 

l   Click All Programs -> Click Microsoft SQL Server 2005 -> click Microsoft SQL Server Management Studio

l   Log in to SQL Server 2005 with windows authentication or sa user;

l   Highlight the server and right click Properties;

l   Highlight Security  -> Under Server authentication,  choose SQL Server and Windows authentication mode;

l   Highlight Connections -> Under Remote server connections,  choose Allow remote connections to this server;

l   Click OK to save the settings.

 

2. Configuration in SQL Server Surface Area Configuration

l   Click All Programs -> Click Microsoft SQL Server 2005 -> click Configuration Tools -> click SQL Server 2005 Surface Area Configuration

l   Click Surface Area Configuration for Services and Connections -> click MSSQLSERVER -> click Database engine -> click Remote connections -> Under Local and remote connections, choose Using TCP/IP only or other protocols.

l   Click Apply to save the settings.

 

(Optional start the SQL Browser Service and set automatically start)

 

3. Stop SQL server and restart SQL server

l   Use SQL Server Management Studio or Services in Control Panel to stop SQL Server and then restart the SQL Server. This process is used to initialize the change settings.

 

4. Test the changes

l   Create the user through Security -> Logins -> New Logins…”

l   log in with the newly created user  when Authentication  is SQL Server authenticationunder local SQL server or under remote SQL server.

 

 

Firewall..


At this point you should be able to remotely connect.  If you still can't chances are you have a firewall configured on the computer where SQL Express is running.  The instructions below are for Windows XP SP2's firewall settings.

 

To enable the firewall to allow SQL Server Express traffic:

1.        Launch the Windows Firewall configuration tool from the control panel.

2.        Click the Exceptions Tab

3.        Click the "Add Programs." button and select "sqlservr.exe" from the
location where you install SQL Server Express


You should be able to remotely connect.  Note, you can get more restrictive by just specifying the port number that will be allowed (used best when configured with Option A).

Note: If you chose to use the SQL Browser service, you must also add sqlbrowser service executable to the exception list as it listens on udp port 1434.

 

 

 

Comments