* Note : Performing these steps needs an IT Technician Or individual with Network % SQL setup Savvy.

To Manage And Back Up Data, You need download SQL Server Management Studio Express.

Remote Access Settings for SQL Server

Configuring Microsoft SQL Server Express Edition for Remote Access

When you install Microsoft SQL Server Express Edition on a non-server based version of Windows, the default settings used by the database engine do not allow remote connections.

In order to enable remote connections you need to take the following steps:

  • Install SQL Server Management Studio Express. If you have not installed this program already, do so! It will make your life a lot easier.
  • Check that the SQL Browser service is installed and running.
  • Configure SQL Server to allow remote connections using the TCP protocol.
  • Configure the Windows firewall to allow traffic to flow to SQL Server and SQL Browser.
  • Change the authentication mode in SQL Server if necessary.

Installing SQL Server Management Studio Express

To make the configuration changes easier, I recommend that you install SQL Server Management Studio Express.

You can download Management Studio from the Med-Sites.com web site. Some versions of SQL Server already install Management Studio so you may not need to download it.

I recommend that you install SQL Server before installing Management Studio.

Checking the SQL Browser service and fixing ports

The SQL Browser service allows other computers to query the network for SQL Server database engines. Without this service, your computer will not answer the call and your database engine won’t appear present on the network.

You can still connect to the server as long as you know the name of the computer that holds the database engine. However, if you are using Visual Studio or another program that allows you to select the database server from a list, then you should check that this service is installed and working.

Because you may be modifying a service on the computer, you will need to login as an administrator to make these changes.
If you are using Windows Vista you will need to right click each program shortcut and then click on Run as administrator in order to give yourself administrative privileges.

By default, SQL Server Express Edition does not use a fixed TCP port for its communications. This can cause problems with firewall software.
One of the steps you will need to take is to force SQL Server to use a specific port

To check that the SQL Browser service is running and force SQL Server to use a fixed TCP port, do the following:

  1. Navigate to the following folder Start – All programs – Microsoft SQL Server 2005 – Configuration Tools. Once you open the Configuration Tools folder, you will see SQL Server Configuration Manager – run this program.
  2. Click on Services on the left hand pane. You should see at least two entries, SQL Server and SQL Browser. If you don’t see SQL Browser then you may need to reinstall SQL Server.
  3. While your in the Configuration Manager, navigate to SQL Server 2005 Network Configuration – Protocols for MSSQLSERVER. Check that TCP\IP is enabled. If it isn’t, right click on it, then click Enable. You may get a message saying that SQL Server will need to be restarted for the changes to take effect, do not restart at this stage, just cancel the message.

  1. ClinicGate sql2 SetUp Network SQL

  2. Right click again on TCP\IP as you did in the previous step. Select Properties
  3. Click on the IP Addresses tab. This section will contain an entry for each network adapter you have installed on your computer. For each entry, check that TCP Dynamic Ports is blank (delete any zeros if present) and that TCP Port equals 1433.
  4. Click OK or apply. You will get a message saying you need to restart SQL Server, you can do this manually if you know how, otherwise just restart the computer.
  5. Close the Configuration Manager.

  6. ClinicGate sql3 SetUp Network SQL

Configuring SQL Server for remote connections

Again, you will need to login as an administrator to make any changes to SQL Server. If you are using Windows Vista then you will need to run each shortcut as an administrator (see above).

  1. Navigate to the following folder Start – All programs – Microsoft SQL Server 2005 – Configuration Tools. Once you open the Configuration Tools folder, you will see SQL Server 2005 Surface Area Configuration – run this program.
  2. Click on Surface Area Configuration for Services and Connections.

  1. ClinicGate sql4 SetUp Network SQL

  2. Select Remote connections from the left hand pane. In the right hand pane, make sure that Local and remote connections and Using TCP\IP only is selected.
  3. Click OK and then close the configuration tool. You may need to restart SQL Server for the changes to take effect so do this if you know how or restart your computer.

  4. ClinicGate sql5 SetUp Network SQL

Congratulations! You have completed the configuration changes necessary for SQL Server to accept remote connections. You now need to modify your firewall to allow other computers to connect.

Configuring your firewall for SQL Server

There are many firewall products out there, so for simplicity I will only cover how to configure the Windows firewall. If you are using another firewall product, simply allow incoming connections to TCP port 1433 for SQL Server and UDP port 1434 for SQL Browser.

  1. Navigate to the Control Panel. Once there, double click on Windows Firewall. You may have to switch to Classic View in order to see the icon.
  2. If you are using Windows Vista, click on Allow a program through Windows firewall – XP users do not need to
    do this step.
  3. Click on the Exceptions tab and then click Add port. Enter ‘SQL Server Port’ in the Name text box and ‘1433’ in the Port text box. Make sure that TCP is selected as the protocol – do not click OK just yet.

  1. ClinicGate sql6 SetUp Network SQL

  2. Click on the Change scope button. Make sure that My network (subnet) only is selected and then click OK twice. By doing this last step, you ensure that only computers connected to your local network can access SQL Server – we don’t want computers on the Internet gaining access!

  3. ClinicGate sql7 SetUp Network SQL

  4. Now we need to do the same for SQL Browser. Click Add port. Enter ‘SQL Browser Port’ in the Name text box and ‘1434’ in the Port text box. Make sure that UDP is selected as the protocol – do not click OK just yet.
  5. Click on the Change scope button. Make sure that My network (subnet) only is selected and then click OK twice
  6. We now need to add SQL Server and SQL Browser as programs that are allowed access through the firewall. To do this, click Add program.
  7. Click on the Browse button and navigate to the following path ‘C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\Binn\Sqlservr.exe’, then click OK twice.
  8. We now need to do the same for SQL Browser. Click on the Browse button and navigate to the following path ‘C:\Program Files\Microsoft SQL Server\ 90\Shared\sqlbrowser.exe’, then click OK twice.

  9. ClinicGate sql8 SetUp Network SQL

Changing SQL Server’s authentication mode

By default SQL Server only allows you to authenticate using Windows authentication (i.e. your Windows login which is sent automatically when you make a connection to SQL Server) – this works out fine on a domain, but within a workgroup environment this can prove problematic and usually results in connection failures. Also, certain software will only connect to SQL Server using SQL.
If you are on a domain and your software can connect to SQL Server using Windows authentication, then skip this step. Using Windows authentication is more secure and more easily managed.

However, if you do need to change the authentication mode, do the following:

  1. Navigate to the following folder Start – All programs – Microsoft SQL Server 2005. Once there, you will see SQL Server Management Studio Express – run this program. If you are using Windows Vista, right click on the program and then select Run as administrator, otherwise you won’t be able to log into Management Studio.
  2. Log in using Windows authentication as your authentication mode.
  3. In the left hand pane, right click on the very top icon – the text next to this icon should be the same name as your server. Select Properties. Another window should appear telling you the details of your SQL Server.
  4. In the left hand pane you will see a number of items in a list, select Security from the list. In the right hand pane, make sure that SQL Server and Windows Authentication mode is selected and then click OK.
  5. Close Management Studio.

  6. ClinicGate sql9 SetUp Network SQL

     

Now we must enable the admin(sa) password for security resaons , please follow the next steps :

  1. In the left hand pane you will see a number of items in a list, select Security from the list. In the right hand pane, it will be expand as indicated in the picture below , select Logins then select right click on sa then choose properties as shown in the picture.

    ClinicGate sql10 SetUp Network SQL

  2. write the password then confirm password in the boxes and select Enforce password policy ( as you see in the picture)

    note : Remember the password you will use it later.

    ClinicGate sql11 SetUp Network SQL

  3. move to the Status on the left side and do as you see in the picture

    ClinicGate sql12 SetUp Network SQL

  4. Now press ok.

Congratulations!

You’re ready to go! Just remember to restart the computer to make sure that all changes have taken.

Then goto the next section ( Attach Database ).

 

SetUp Network SQL