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:

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.

Useful resources

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.

Remote Support

Telephone Support

Telephone number 020 3411 4445 (UK) +44 20 3411 4445 (Int’l)

Hours of business Open 9am – 5.30pm UK time, mobile number available for out of hours tech support.

About Digital Red

Digital Red provides small businesses, home workers and individuals with friendly and professional technical support on all aspects of IT and PC use. Digital Red is based north west of London in Ruislip.