Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5220
  • Last Modified:

Sage ACT error: The SQL database instance is unavailable

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
Miloathens
Asked:
Miloathens
  • 27
  • 25
1 Solution
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
If only getting it on the 2nd machine, see this knowledge base article:
http://kb.sagesoftwareonline.com/app/answers/detail/a_id/28494
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
Sage charge for these, because they aren't usually anything to do with ACT! :-(
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
Could still be either system blocking it ... what operating systems and firewalls?

Can you at least ping the other 1st PC?
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
Also, did you disable the Windows Firewall on both?
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
That means you can see each other over the network

So ... what did you put into Telnet?
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
Can you open a local database on Win7?
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
Not quite as simple ... but not to hard. See:
http://kb.sagesoftwareonline.com/app/answers/detail/a_id/19818
0
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
Of course ... backup first
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
You need to select "Restore as" ... not "Restore" when the dialogue box appears :-)
0
 
MiloathensAuthor Commented:
BTW, just tested the client machine with the new DB name and still getting same error.
0
 
MiloathensAuthor Commented:
I did Restore as ...
0
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
What's the error when restoring to the new folder?
0
 
MiloathensAuthor Commented:
Just tried again ... deleted the DB, then did Restore As to the C:\ACTDATA, and got the same error screen.
0
 
MiloathensAuthor Commented:
The error is that screen shot I attached --
0
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
You have compessed folders on your hard drive ... SQL cannot reside in a compressed folder
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
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
 
MiloathensAuthor Commented:
BTW my tech guy verified there are no sharing/internal network name issues.
0
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
It's less a SQL issue than it's a Firewall issue talking to SQL ... because both systems can open SQL locally.
0
 
MiloathensAuthor Commented:
I agree, but we can't find any firewall issues anywhere that can be fixed ...
0
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
You might try running (from cmd):
netstat -p TCP

Then try to connect and see if it shows up
0
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
Do you see anything in the Event Log when trying to Telnet (either machine)?
0
 
MiloathensAuthor Commented:
Not sure how to do that ... will be working with my dad again tonight, will ask him to check.
0
 
MiloathensAuthor Commented:
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
 
MiloathensAuthor Commented:
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
 
MiloathensAuthor Commented:
Sorry, not Control Panel, Windows Explorer I meant.
0
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
When running the netstat, did you try to connect with Telnet?
0
 
MiloathensAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantCommented:
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
 
MiloathensAuthor Commented:
Thanks for the help!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 27
  • 25
Tackle projects and never again get stuck behind a technical roadblock.
Join Now