Using Microsoft Office Accounting 2009 Professional (UK edition) under Windows 7

I use Microsoft Office Accounting Professional and when I installed Windows 7, I had problems restoring and using my existing company data in Office Accounting.

These are the steps I took to resolve my problem, you may like to try them too.

Problem

I had the UK edition of Microsoft Office Accounting Professional 2009 running under Windows XP Professional with both the program and operating system patched to the newest releases at the time (SP1 for Office Accounting, SP2 for Windows XP). I had made a backup using Office Accounting (“company.sbb“) under Windows XP, but could not restore it under Windows 7.

When I tried to restore the company.sbb backup, I got the error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.   Verify that the instance name is connect and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)

Solution – part 1

The first thing you need to check is that you are using the latest version of Office Accounting: you should first download and install Office Accounting from this Microsoft site and then you should download and install Service Pack 1 from this page.

  » Update 28/10/09 – check the bottom of this page – SP2 has now been released.

I fixed the “No connection could be made because the target machine actively refused it” error by changing a setting in the SQL Express configuration. To make the same change on your PC:

  • Click the Start button and select SQL Server Surface Area Configuration from the Microsoft SQL Server 2005 -> Configuration Tools menu
  • Click on Surface Area Configuration for Services and Connections
  • In the tree at the top left, expand the MSSMLBIZ entry so that you can see the Remote Connections entry, and then click on Remote Connections. Change the radio buttons so that Local and remote connections is selected, and then below that, select Using both TCP/IP and named pipes.
  • Click Apply and then OK.

Solution – part 2

Once I had made the change above, I then retried restoring my Office Accounting backup. It seemed to get a little further, but it still failed – and I got the error below:

Login failed for user ”. The user is not associated with a trusted SQL Server connection.

Correcting this error was just as quick, but it means editing the registry so you need to be careful.

  • Open the registry editor by clicking Start and then typing regedit into the Search box at the bottom of the start menu. This will show regedit.exe at the top of the start menu: right click on it and select Run as Administrator
  • You need to expand the tree shown on the left so that you are viewing:
    • HKEY_LOCAL_MACHINE
      • Software
        • Microsoft
          • Microsoft SQL Server
            • MSSQL.1
              • MSSQLServer
  • To expand the tree, you need to press the triangle beside each entry, then scroll down to the next one – see this screenshot:
  • Once you’re in the MSSQLServer folder on the left, look on the right hand pane and you will see an entry named LoginMode.
  • We want to change the LoginMode from “1” to “2”:Double click on the LoginMode to open this window – delete the 1 in the “Value data” box, type 2, and press OK.
  • Close the Registry Editor
  • Finally, we need to restart the SQL service. It’s easier to just reboot your PC to do this, but if you want to do it manually you can launch the Service Manager (Start -> type services.msc) and look for a service named SQL Server (MSSMLBIZ) and stop it, then start it again.

Restore your backup

Hopefully after following through these steps you’ll be able to restore your backup as I was.

Microsoft don’t recommend the Mixed Mode Authentication setting that I made in the registry – though I had no choice but to do it if I wanted to restore my backup. After I restored my backup, I changed the 2 back to a 1 in the LoginMode entry and everything still worked fine… it was only necessary to restore my backup.

A helpful Microsoft technician from the Microsoft Office Accounting support team checked over my setup after I had made these changes and said that everything was OK. She recommended that the Windows 7 UAC be disabled but other than that, she didn’t change anything on my setup. I’ve created new customers, raised invoices, recieved payments etc in the time I’ve been using Windows 7 since and haven’t had any problems at all.

Good luck – I hope it works for you!

Useful resources

These are the pages I found and used to fix my problem:

  • Allowing local and remote connections – answer given on Experts Exchange
  • Understanding the second error to be that Mixed Mode authentication is required – KB article 555332 in the Microsoft Knowledgebase
  • Changing login type to mixed mode using SQL Express (which has no GUI to make the change, hence the registry edit) – article on the Edujini Labs website

I also posted about the problem on the Microsoft Partner forums, though only Microsoft Partners can access them. If you’re not a Microsoft Partner, you can use the public newsgroup although not so many questions seem to get resolved there.

Update – 28 October 2009

I was only ever able to restore a backup with the Mixed Mode Authentication enabled in the registry (the setting being on 2 rather than the default 1), but on 28 October 2009 Microsoft released Service Pack 2 for Office Accounting. I have installed SP2 and now I am able to restore backups without fiddling with the registry.

It may be worth installing SP2 before you try the steps detailed on the rest of this page.

  • Download Service Pack 2 for Microsoft Office Accounting here.