When trying to connect to the SQL server from a remote workstation, the server may not be visible or it may appear that the workstation is unable to connect.
Before making any changes to the server, the connection from the work station should be tested. This can be done via either creating an ODBC connection to test or by the use of a UDL file.
To create a UDL file:
- Right click on the workstation desktop and select the option New and Text Document
- Rename the new file to test.udl
- Right click on the UDL file and select Properties
- Click the Provider tab and select a SQL provider (either Microsoft OLE DB Provider for SQL Server or SQL Native Client)
- Click the Next button
- If the Connection tab is not selected, click the Connection tab
- In the server name drop down list, type in the SQL Server name
- Select the option Use a specific user name and password and enter in the user name and password in the appropriate text areas
- From the Select the database on the server: drop down, click the drop down arrow
If no databases appear then the SQL server is not browseable from the client.
To make the SQL server browseable to the clients:
- On the SQL Server, select the option Microsoft SQL Server xxxx from the start menu and select Configuration Tools and SQL Server Configuration Manager (where xxxx is the SQL server version – i.e. 2005 or 2008)
- From the left object pane, select the option SQL Server 2005 Services
- In thr right pane, select the object SQL Server Browser
- Right click on SQL Server Browser and select Properties
- Select the Service tab and change the Start Type to Automatic
- Select the Log On tab and click the Start button. The SQL Server Browser service should now start
- Click the OK button
- In the object pane, expand the SQL Server xxxx Network Configuration node
- From the list of SQL servers, select the server that the PCSchool database resides on
- In the right oject pane, enable the network services TCP/IP and Named Pipes by right clicking on each option and selecting enable
- Once all the above changes are complete close the SQL Server Configuration Manager
Before the changes can take affect, the SQL Server service must be restarted. This can be done in a variety of ways – but the easiest method is to restart the service via the Management Studio. To restart the service using Management Studio:
- On the SQL Server, select the option Microsoft SQL Server xxxx from the start menu and select SQL Server Management Studio (where xxxx is the SQL server version – i.e. 2005 or 2008)
- Log in to SQL Server as per normal
- Once Management Studio has finished loading, right click on the database server that the above changes were made to from the Object Explorer
- From the context menu, select the option Restart. This will restart the SQL Server so that all pending changes can be completed.
Once the service has restarted, repeat the UDL connection process above. The database server and databases should now be visiable to all clients.
The above information is for users of PCSchool Student Management School Software.