Error: "SQL server does not exist or access denied" when opening MYOB

This support note applies to all: 

• Accountants Enterprise (AU & NZ)
• Accountants Office (AU & NZ)

In MYOB you may experience the error "SQL server does not exist or access denied" when trying to log into Accountants Enterprise or Accountants Office.

This error may occur:

• randomly
• after a database restore
• after a server migration

Error occurs randomly

Where the issue occurs randomly, to fix this error you will need to restart the SQL Server.

If you find that you are able to log into MYOB on the server, but you cannot log into MYOB from a workstation, please refer to Checking the Data Source Connection via ODBCIf this does not resolve this issue and you cannot establish an ODBC connection from the workstation, you will need to contact your IT Professional. 

Also, check your AV or Firewall.  It is recommended to disable temporarily to test if the issue occurs and then consult your IT Professional to add an exclusion on AV/Firewall. For details refer to Experiencing slowness on workstations using MYOB AE or AO software and Resolving the slowness of MYOB applications or unexpected shutdown on a workstation

Error occurs after a database restore

Where you have just restored a database and the error still exists on the server and workstations after restarting sql, you will need to run the Create User script/create the VPMUSER in SQL. Please perform the following steps.

The following steps assume your database is called AODB1. In your case, the error may specify a different database.  In this situation, you would substitute AODB1 with the database name in your error message.

When you launch AE or AO, the system generally displays the database name you are trying to log into i.e. for AE, it would normally be VPMSER but could also be AEDB1. For AO, it would generally be AODB1 but could also be AODB2.

Error occurs after a server migration

Where you have completed a server migration and receive the error, you will need to:

• Run the Create user script
• Review and update Lookup.xml
• Confirm the database has been restored into the correct SQL instance.
• In addition to this, AE sites that use the Vizpost/CDS integration will need to run the grant.sql script once the integration component has been re-installed.
  To create the VPMUSER in SQL

  1. You will need to run the Create User script after restoring your database. See KB 37110: Running the Create user.sql script

     

     Ensure the correct server name is entered and that the instance name is MYOBACCT is correct; for example  SERVER or SERVER\MYOBACCT and click Connect. This information can be confirmed by checking the AE/AO login screen.

  To run the grant.sql script (AE Vizpost sites unsafe-only)

  Once the server migration steps have been completed on AE sites, you may need to run the grant.script to allow updates between Practice Manager and Tax if the practice is using Vizpost.

  1. To run the grant.sql script, see KB 34808: Reinstalling CDS Integration. Refer to Step 2. Reinstall VizCDS components.
  To update the lookup.xml

  1. On your server (where the AE or AO Practice Manager where the server program is installed, click Start and select Run. The Run screen appears.

  2. Type c:\Program Files\MYOB\Central\Deploy or C:\MYOBAE\AESQL\Central\Deploy or C:\MYOBAO\AOSQL\Central\Deployin the Open field and click OK, where: c:\Program Files\MYOB\VPM\Central\Deploy is the location of the file lookup.xml. Windows Explorer opens.

  3. Right-click on lookup.xml and select Copy. The file is copied.

  4. Right-click on a blank area of the screen and select Paste. The file Copy of lookup.xml is saved to the same directory.

  5. Right-click on the file lookup.xml and from the Open With menu, select Notepad. The Lookup.xml - Notepad screen appears.

  6. Edit the DataSource=<server name> to the server name identified above and change the Database to be the correct database, for example Database=AODB1, and ensure the User ID=VPMUSER, then from the File menu, select Save. The changes are saved

  To confirm if the requested database exists

  

  Document the name of the database that appears on the error; for example AODB1 and then perform the following on the Server.

  1. Follow the menu path: Start > All Programs > Microsoft SQL Server 2012 or later > SQL Server Management Studio. SQL Server Management Studio opens.
  2. Ensure the correct server name is entered and that the instance name is MYOBACCT for example SERVER\MYOBACCT or SERVER and click Connect. The SQL Server Management Studio Object Explorer opens.

  3. Expand Databases and ensure the database specified, for example AODB1 exists and you can expand it.

     

     If there are multiple databases, you will need to identify which is the correct one, prior to updating the lookup.xml.

  4. If you cannot expand the database, you will need to detach and re-attach it as per Attaching and detaching SQL databases, then run the Create User script as per below which will resolve the issue.

     

     If this does not resolve the error, you will need to restore your database from a backup and run a repair as per Restoring an SQL database using SQL Server Management Studio and then  Running a repair for MYOB Accountants Office or KB 37204:Running a repair for MYOB Accountants Enterprise