Solved

Sage ACT error: The SQL database instance is unavailable

Posted on 2013-01-14
52
4,981 Views
Last Modified: 2013-01-28
Out of the blue, Sage Act 2012 Pro (on a secondary machine, using a shared database) suddenly stopped working.  Got the error (above).  Researched this on Sage, did what they suggested: (a) reinstalled the .PAD file, no luck; (b) uninstalled Microsoft SQL server, and reinstalled (2008 R2), and did the same for ACT.  Still no luck.  Only clue I have is in the SQL database configuration panel, it won't show the ACT database, so it's not finding it, but can't figure out how to change this ... and how it got messed up to begin with, as we've changed nothing here.  Any ideas?
0
Comment
Question by:Miloathens
  • 27
  • 25
52 Comments
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38776607
If only getting it on the 2nd machine, see this knowledge base article:
http://kb.sagesoftwareonline.com/app/answers/detail/a_id/28494
0
 

Author Comment

by:Miloathens
ID: 38776634
I really appreciate this -- I tried each of those 4 things, and it's still not working.  The main machine (server) is working fine, but the client machine is still giving this error.  I've updated the PAD file to the IP address of the main machine, added exceptions to Kaspersky Firewall (Windows Firewall is disabled), restarted the SQL services, and reset the SQL password -- still no luck.  Other ideas?  I'm feeling very defeated by this, and Sage wants to charge me $199 for ONE phone call to fix their software's problem.
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38776654
Try disabling all the firewalls on BOTH machines.

Or try telnet from the 2nd machine to the first on port 1433 ... just to confirm
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38776656
Sage charge for these, because they aren't usually anything to do with ACT! :-(
0
 

Author Comment

by:Miloathens
ID: 38776739
Yeh I know, it's usually not a Sage issue ... it's a PC issue.  (Don't get me started on PCs & their non-ease of use. :) ... I completely disabled ALL firewalls on both machines, rebooted, still getting the same issue.  However: When I tried to telnet in from client computer to main computer (using Putty), the connection was refused ... so clearly my main computer is blocking the connection somehow, but I have no idea what to do next?
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38776743
Could still be either system blocking it ... what operating systems and firewalls?

Can you at least ping the other 1st PC?
0
 

Author Comment

by:Miloathens
ID: 38776834
Main computer = Vista Business
Client computer = Windows 7 Home Premium

Kaspersky Anti-Virus on both, but I've completely disabled it on both too.

Ping from client to main computer successful.  However, both computers share the same IP as they are connected to a switch, which is connected to the internet via a router.
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38776842
Two machines cannot share the same IP on the same network.

If you're both behind the same router, you need to ping, telnet, share via the internal IP

Try doing "ipconfig /all" on both systems
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38776844
Also, did you disable the Windows Firewall on both?
0
 

Author Comment

by:Miloathens
ID: 38778140
Yup, Windows Firewall is turned off on both.  I did ipconfig, and found that each one does have a unique IP, if I'm reading it right -- they both start with the same numbers, but the final digit is different.  I pinged (from both computers) and succeeded each way.  What does that tell me?
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38778265
That means you can see each other over the network

So ... what did you put into Telnet?
0
 

Author Comment

by:Miloathens
ID: 38778330
OK so I used Putty (telnet) on second computer to try to telnet in to main computer, and it said "Connection closed by remote host" followed by "Network error: connection refused" ... this means something on the main computer is not allowing incoming connections, right?  If it's not my virus/firewall software, what else could it be?  I looked in Control panel, and shut off Windows Defender too.  I also use RegCure, and I set it to ignore all ACT & SQL items.  Can't think of anything else to shut off/try?
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38778454
And, obviously the SQL server (ACT7) Service is running on machine 1?
On the main computer, have you shared the database?

Did you manually install SQL and the ACT7 instance or did you let ACT! install it?

You might try some of these options:
http://www.sqlmusings.com/2009/03/11/resolving-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/
0
 

Author Comment

by:Miloathens
ID: 38782748
Tried all of that ... I had my local tech guy come in, he spent 2 hours (!) here, and is still stumped.  He ran ComboFix on both computers, found some stuff on the main one, but still hasn't solved anything.  He thinks there may be a root virus issue.  Any other ideas/theories for what could be causing this?
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38782764
Does ACT! run ok on the Vista box?

I'd have a look in the Windows Event Logs on BOTH systems.

Then from a command prompt on the Win7 box,
Telnet VistaIP 1433

Also, from the Vista box cmd prompt:
Telnet Win7IP 1433

Then have a look through the Event Logs and se3e if you can find any additional related error
0
 

Author Comment

by:Miloathens
ID: 38782777
OK -- I'll show this to my tech guy, it's starting to get over my head. :)  Yup, ACT runs fine on the Vista machine, and was running fine on the Windows 7 machine until Monday.
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38782792
Can you open a local database on Win7?
0
 

Author Comment

by:Miloathens
ID: 38782902
Yes, ACT comes with a demo database that's installed on the hard drive with the program, and this is what it default opens when you open ACT now.  So yes, ACT is functional; it's just not finding/getting access to the main computer's DB via .PAD for sharing ...
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38783839
The PAD is just a Pointer to ACT! Database (you can open it in notepad)

Just had another 2 thoughts ...
1. What folder/path is the database in on the Vista machine?
2. Can Win7 access a shared folder on Vista?

Don't think it's these because of the telnet block ... but worth checking
0
 

Author Comment

by:Miloathens
ID: 38784528
Just checked:

1. On Vista, it's in C:\Users\(myusername)\Documents\ACT\ACT for Windows 12\Databases\(databasename.PAD)

2. Just checked, yes, the Win7 can access shared folders on the Vista machine.
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38785045
I usually find having a database in the Users folder causes issues with security permissions ... I prefer to create a folder c:\ACTDATA and put shared databases there.

Doubt it would be behind this issue as it's more an issue with SQL ... but might be worth a try to move it.
0
 

Author Comment

by:Miloathens
ID: 38785100
Is moving the database as simple as closing ACT, moving it, and then pointing ACT to the right location when it reopens?  I won't lose any data will I?  And what do I move -- the whole databases folder, or specific files?  Or maybe there's a way to do this from within ACT?
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38785118
Not quite as simple ... but not to hard. See:
http://kb.sagesoftwareonline.com/app/answers/detail/a_id/19818
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38785119
Of course ... backup first
0
 

Author Comment

by:Miloathens
ID: 38786960
So I did this ... backed up (twice, to 2 different locations), and then deleted, and restored -- it wouldn't work when I changed directories.  See what it did when I tried to have it restore to a new DB name in the directory c:\ACTDATA ... when I restore to the original location it restores fine.  Does this give us any clues as to why the client machine can't access things?
ACT-error.pdf
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38786988
You need to select "Restore as" ... not "Restore" when the dialogue box appears :-)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Miloathens
ID: 38786989
BTW, just tested the client machine with the new DB name and still getting same error.
0
 

Author Comment

by:Miloathens
ID: 38786999
I did Restore as ...
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38787002
What's the error when restoring to the new folder?
0
 

Author Comment

by:Miloathens
ID: 38787010
Just tried again ... deleted the DB, then did Restore As to the C:\ACTDATA, and got the same error screen.
0
 

Author Comment

by:Miloathens
ID: 38787011
The error is that screen shot I attached --
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38787018
You have compessed folders on your hard drive ... SQL cannot reside in a compressed folder
0
 

Author Comment

by:Miloathens
ID: 38787029
I don't understand ... I did the backup (as a ZIP file) to the desktop, then tried to Restore As from there.  Was that wrong?  Does the backup have to happen to the original ACT location?  If I do a backup to the default directory, it's the same one I'm about to delete.
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38787062
Nothing to do with that ... the folders on the hard drive are compressed

Have a look at the Compressed Files section on this like:
http://kb.sagesoftwareonline.com/app/answers/detail/a_id/19640
0
 

Author Comment

by:Miloathens
ID: 38787110
Wow, the things you learn about Windows ... I really appreciate your help;  I'm getting a serious education in the nuts & bolts of Windows software.  So I restored successfully to C:\ACTDATA, and then copied the .PAD folder over to the Win7 computer, which I also rebooted.  Started ACT, and .... same error.  Grrr.
0
 

Author Comment

by:Miloathens
ID: 38787112
BTW my tech guy verified there are no sharing/internal network name issues.
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38787137
The problem is with accessing SQL ... which the Telnet seemed to reveal.

As you have SQL on both ... have you tried telnet to port 1433 from Vista to Win7?
0
 

Author Comment

by:Miloathens
ID: 38807722
Sorry for the delay -- been traveling for business.

Tried to telnet from Vista to Win7, using port 1433 -- said "connection closed by remote host" and then "Network error: Connection refused"
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38808716
I'm suspecting that (assuming it's not a virus) either the router is blocking it or there is still a firewall there.

Does your IT guy know SQL?
0
 

Author Comment

by:Miloathens
ID: 38809954
My dad is a computer whiz and knows SQL and remote logged in last night ... spent 2 hours, still no luck, although we did discover that the folder ACTDATA on the Vista computer was shared, but not openable by Win7.  We fixed that, but it's still giving us the same error.  We've just downloaded SQL management studio on both computers, and will look some more tonight ...
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38812295
It's less a SQL issue than it's a Firewall issue talking to SQL ... because both systems can open SQL locally.
0
 

Author Comment

by:Miloathens
ID: 38814094
I agree, but we can't find any firewall issues anywhere that can be fixed ...
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38814200
You might try running (from cmd):
netstat -p TCP

Then try to connect and see if it shows up
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38814218
Do you see anything in the Event Log when trying to Telnet (either machine)?
0
 

Author Comment

by:Miloathens
ID: 38814222
Not sure how to do that ... will be working with my dad again tonight, will ask him to check.
0
 

Author Comment

by:Miloathens
ID: 38824280
So my dad gave up also ... unless I can fix this today, will be calling Sage tomorrow and spending (gulp) $199 ...

I did the netstat on both computers, each shows the other, with IP address followed by a :139 and :1196 and :52541 (on Vista, referencing Win7 machine) ... and :52541 and :1196 (reverse).

Not sure how to check event log?  Pulled it up, but there are a hundred options, not sure what I'm looking for.
0
 

Author Comment

by:Miloathens
ID: 38824289
And BTW, both the Win7 machine can see, and access, the Vista machine through Control Panel -- so there's nothing obvious blocking the connection.
0
 

Author Comment

by:Miloathens
ID: 38824292
Sorry, not Control Panel, Windows Explorer I meant.
0
 
LVL 30

Expert Comment

by:Mike Lazarus
ID: 38825002
When running the netstat, did you try to connect with Telnet?
0
 

Author Comment

by:Miloathens
ID: 38827148
Solution!!!!

So you were very very close, all your suggestions were on the right track ... the issue was with ACT-specific ports being blocked by Windows Firewall.  ACT uses 1433, 1434, and another one I forget that's specific to my Vista computer.  Once these were added as exceptions under the Advanced tab of Firewall (on both machines) it magically started working!  This was a lesson for me too in TCP/IP ... I'm no tech guru, but I learned that when firewalls are disabled, it's only for application level, and not for port level.  I also learned that each application (like ACT) has its own specific ports it uses, and this info was available on the Sage Knowledgedatabase --  but of course, I had to know that's what we were looking for.  The Sage guy apologized for it being such a problem for so long, but I told him it was fascinating to learn all this stuff -- as long as I don't have to go though it again anytime soon! :)  Again, many thanks for all your help --
0
 
LVL 30

Accepted Solution

by:
Mike Lazarus earned 500 total points
ID: 38827169
Right from the beginning I knew it had to be that :-)

If you can open it on the local machine (which mean the services are running) and you can't Telnet ... there's really no other option
0
 

Author Closing Comment

by:Miloathens
ID: 38827184
Thanks for the help!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

A lot of users new to ACT!, especially those who don't get the benefit of a consultant, seem confused between when to share on a network and when to sync ... or, in fact, how to do either. So I thought I would attempt to explain the various options …
ACT LINK FOR PEACHTREE WON’T LINK PROBLEM       It always seems that when you upgrade Act or Peachtree Quantum in versions 2009, 2010, 2011, or 2012 then try to relink them it fails. Following the linking instructions-  Go to the peachtree tab in …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now