SQLServer - need to access an ACT! database for data migration

Question

I'm looking for a way to access data in an ACT! database for a data migration to a custom application.

The ACT! software uses SQLServer 2005.

The data conversion script and new application will be written on a Linux system using Python and Postgresql.

My problem is getting my hands on the data !

I don't know how ACT! has their SQLServer installation configured.

---

Initially I installed Microsoft SQL Server Management Studio Express to see if I could view and query the data.

I currently have ...

• Windows XP machine
• ACT! 2009 version 11.0.0.367
• Microsoft SQL Server Management Studio Express version 9.00.2047.00

There are two instances of the SQLServer running. One for the ACT! software and one that was loaded with the Studio Express installation.

In Studio Express I can connect to the latter instance but not to the SQLServer for the ACT! database.

Using the Windows Authentication login I get Login failed for user ... Error 18456.

I don't know what the SQL Server Authentication login would be.

---

• Is there a way around the connection/login issue ?

• Can the second instance of SQLServer be configured to see the ACT! database ?

• Copy the database files ... ?

Solution 1

We have a Customer who has the unfortunate need to use this program.

Sage purposefully makes it difficult to get to your data because they see that as a "revenue opportunity". (I guess they want to have all of the "lock-in" power of a proprietary file format combined with all of the convenience and performance of using SQL Server 2005...)

Read your license agreement. Sage demands you pay them additional fees to get access to the data. Legally, your only way of accessing your data directly is via their "licensed" means. (It's completely bogus, and makes me think of Sage software as a bunch of dirty, underhanded jerks, personally.)

They do some mean things to make sure that you can't get and keep "sa" privilege to the database easily, but since you physically control the box you can get around that.

I'm not sure how much I should say on here for fear of getting Server Fault into trouble and / or getting a cease-and-desist order myself. Suffice it to say there's a mode that you can start SQL Server in that allows "sa" access regardless of credentials. That's your back door to get in.

Sage: You're a bunch of bozos and your business model sucks re: ACT!. I hope that free and open-source and cloud-based alternatives drive you out of business.

Solution 2

or you can use the actreader.exe utillity that is included in the premium versions of act, to create a read only user for the act! database (I would advise against writing into act!s' databases anyway...)

simply start it, enter a password, and then connect using the username ACTREADER and the password you have chosen. done.

Solution 3

Jus tattach the database to a different SQL server and you have access to all the data.

Solution 4

This is a very old post but I've recently come across the same issue with ACT! 17 PRO and people may need it as it goes out of support in November 2017.

If you only need the data then using DBeaver connect to the remote server.

The complete JDBC connection settings as follows :

• Host : either the FQDN or the IP Address of your Act server
• Port : the TCP dynamic port as shown in the SQL Server Configuration Manager
• Database : the database name as shown in the ACT PAD connection file

Solution 5

Single User Mode works like a charm.

To start an instance of SQL Server with startup options

1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In SQL Server Configuration Manager, in the left pane, click SQL Server.

3. In the details pane, right-click the instance of SQL Server, and then click Properties.

4. In the SQL Server () Properties dialog box, click the Advanced tab, and then click Startup Parameters.

5. At the end of the original text, in the Value column, type the startup parameters you want, and then click OK. Separate parameters with a semi-colon, for example, –c;-m.

6. Stop and restart SQL Server for the parameters to take effect.

Then login with management studio and taddah!

View more solutions

Comments

[squillman]

Next time tell us how you really feel :)

[Spence]

Yeah, no kidding. I was so peeved when I found out that we had to use their silly "scheduler" to backup the database (legally) instead of just using our Backup Exec SQL agent like we normally would.

[squillman]

Heh, wonder if this is why my wife's uncle is switching over to Exchange.

[Nick Kavadias]

I don't think Sage can't hold this over you as being proprietary or business secret knowledge. Are you speaking of single user mode? Will this still work if the built-in administrator account has been removed from sa? i always find resetting sysxlgns password hash works best no matter what.

[Spence]

@Nick: Single-user mode it is, and it'll work regardless of what has been done to "sa" permissions. Sage has a nasty little stored procedure that removes "sa" privileges for the built-in Administrator (and any other accounts unless they match some string comparison criteria) again if you restore them.