Problem: Testing Linked Server hangs

Product:  BizInsight
Applies to:  MAS 90/200
Article Type:  Problem 

Summary:
When setting up a BizInsight environment for the first time or adding a new company, a key step is to verify that any newly created linked servers work and will retrieve data from MAS 90/200 by running a simple select statement in SQL Management Studio Express using the new linked server.  When testing your linked servers, you may experience a hang where the query never returns data.

Details:
When a query using a linked server hangs, this is typically an indication of a configuration problem of the ODBC data source.

Resolution/Workaround:
To troubleshoot this issue, do the following:
1.  Go back to the ODBC data source that the linked server is using and re-do the test connection.  If a pop-up dialog appears during the test connection requiring you to select your MAS 90/200 company and provide a user name and password, you have a problem with the configuration of your ODBC data source.
a. Go back and check that you have entered the correct company code on the Logon tab of the ODBC data source.  The Company code field should contain only the MAS 90/200 three character company code.
b. Confirm that the user id and password entered on the Logon tab are correct.  In particular, pay attention to the password.  The MAS 90/200 passwords are case sensitive so you must enter the password with the right case.

2. Check that the account being used to run the SQL Server service is the Network Service account. 
a. Click on Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager
b. In the left pane, make sure you have SQL Server Services selected.
c. In the right pane, locate the service for your instance.  If you used the default instance name, the service will be named SQL Server (SQLEXPRESS).
d. Look at the Log On As value for that service.  If it says “Local System”, you need to change it to Network Service.
e. To change the account used for the service, start the SQL Server Configuration Manager configuration tool from Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager.  It is critical that the account is changed via this tool because it ensures that all of the permission changes are made in the registry and to the file directory structure that are required for an account that runs the SQL Server service.
f. In the left pane, click on SQL Server Services.
g. In the right pane, double-click on the SQL Server (instance name) entry that is where your linked servers are created.
h. Change the Built-in account from Local System to Network Service.  You will get the message "This will cause the service to be restarted. Do you want to continue?". Click Yes.

3.  After making the change, test the query again to see if the issue is resolved.

If the provided Resolution/Workaround does not resolve your issues, please contact BizNet Software Support for further assistance.