How to get at the database schema of a hidden DB?

Question

My customer is a dental practice that has bought a piece of practice management software. This software was installed on their local server, including a patient database, a schedule and all manner of medical records. Now they want me to write some utilities for them that aren't provided with their package, and for this I need the ability to query this database.

I tried calling tech support of the software manufacturers (Patterson/EagleSoft), and it's difficult finding anyone who understands the technology enough to answer my questions. As far as I can tell, there's no API for their software, and understandably they're reluctant to tell me how to query the DB directly, programmatically. They do have an interactive query window, but obviously that's no good for writing automated queries. All that they would let on is that somewhere there's a SQL Server DB, but the ODBC drivers to connect to it are SQL Anywhere drivers (huh?).

So I searched around on the server and couldn't find any database files. Then I discovered that the installation creates some kind of proprietary virtual machine, which is only visible to the EagleSoft software. But while they've been very good at insulating their DB in layers of obfuscation, they have left open an ODBC driver, which is indeed an SQL Anywhere connection.

Now after that fascinating and lengthy preamble, here is my question: What queries can I run over this ODBC connection to interrogate the DB as to its structure? If it's a SQL Server DB underneath I could use the sysobjects table, but I don't fully grasp how you can use a SQL Anywhere ODBC connection to connect to a MSSQL DB. And If they were misinforming me and it really is a SQL Anywhere DB underneath, what are the queries to run to get at the DB structure?

And if there's anyone else out there who's ever succeeded in actually querying EagleSoft (or any similar proprietary package) - please tell me how you did it!

Berlin

@Marc Gravell: The ODBC API has generic capability for introspecting schema, and that's would be a not very localized answer to this question. – Unpolitic

Answer 1

Turns out the simplest way to do it was to write a little app using OdbcDbConnection, and connect using the DSN installed with the software. It took one probing 'select * from sysobjects' to reveal that it is, indeed a MS-SQL database underneath all that, and I'm good to go from there!

Berlin

How do we know which DSN is installed with software. Also do we need any credentials to connect to the database? – Courage

Shaul Behr can you post your sample code Im also working with eaglesoft – Kirkland

@BlaireAndaloc This post is approaching its 5th anniversary. Code buried somewhere in an archived project. Sorry... – Berlin

@ShaulBehr Do you remember what kind of driver you used? – Shirlshirlee

@KentaGoto SQL Server driver IIRC – Berlin

@ShaulBehr I am trying to connect to the eaglesoft database using PHP. Is this something you were successful in doing? – Shirlshirlee

@KentaGoto I did it in C# – Berlin

@ShaulBehr Anyway you can dig up the source file? I would be GREATLY appreciate it. I tried calling patterson eaglesoft tech support but they wanted us to fill out a vendor form. This could take a while and I am not sure they can help me. All I want to be able to do is query against the database at an application level. The interactive query window is not very helpful for that obviously. I have the same EXACT problem you had years ago. Would mean a lot. – Shirlshirlee

@KentaGoto It was 6 years ago, and I don't think I still have the code. Anyway I never completed it; the project got canceled. But the schema is not difficult to understand if you query the sysobjects etc. using the odbc connection. – Berlin

justinshafer.blogspot.com/2018/09/… – Pizor

Answer 2

I'd use a tool like squirel which is great at browsing any database to check if anyone was successful with "SQL Anywhere" this google result:

http://blog.gmane.org/gmane.comp.db.squirrel-sql.users/month=20091001

Shows that others have managed to get squirrel to do this. It's quite easy to use... assuming of course you manage to get the connection working!

Saving

Answer 3

A few tools that might help are SQLWorkbench and Django. I use SQLWorkbench to copy the data from the production system into a Postgres database so I can hack on it without damaging the production environment. Then I use Django's inspectdb to generate models of the database environment. From there it's easy to create 'views' into the database and templates to display exactly what I want.

UPDATE: As of Eaglesoft 19, it looks like Patterson has password protected the database and they have gone out of their way to prevent users from getting at the data without paying them for access.

UPDATE: Like I mentioned before, Eaglesoft 19 has a locked-down version of the database. For read-only access you can call Patterson and ask them for the password to the "Database Admin" section of their "Technical Reference" tool that is installed on your server. Once you are in there, there's an option to set a read-only password for access to the database. The username is 'dba' and whatever password you set. Some times it takes a bit of back-and-forth with them to give you access, but my solution was to say "We've been putting patient data into Eaglesoft for over a decade and we've always had access to the database. Now you're restricting it and telling us we have to pay for access. It sounds like you are trying to extort money by holding our data hostage. I should probably run this by our legal team."

EDIT: Nov 18 2022: You can still easily get read-only access to an Eaglesoft database in 21.20.08 (the latest version) by calling Patterson and getting the "day password" for Technical Reference. From there you can enable a read-only user. Based on some of the changes Patterson is making to their application architecture and the database, I suspect they will stop using direct connections to the database in the next year or two. When they make that change, you will only be able to access the database through their API Server. After playing "phone tag" with one of their salesbros for several weeks and doing some light social engineering, I managed to get their price list for going through the API server. It's atrocious. Most offices pay ~$500/mo to Patterson for free tech support and free upgrades. They want developers to pay nearly as much per office for access to the API. They've realized they can lock practices out of their own data and monetize it. We are working with a company that is actively developing a replacement for Eaglesoft to get away from this horrible vendor lock-in.

Undertone

I am trying to connect using RazorSQL and it is asking for database name and auth details. Looking at the ODBC.ini in the registry, I don't see any of those details being saved, any idea what the default is for Eaglesoft? – Paraesthesia

All the Eaglesoft clients (at least back in the version 14-ish days) would create a database connection to the server and query directly rather than sending some data to the server process and getting a result. This means the client signs in with a username and password. And it used to be clear-text--no SSL/TLS. So fire up wireshark on your Windows Server that hosts Eaglesoft and capture packets. You should be able to get the username and password. :) – Undertone

Thanks Aaron, I am using the latest demo (version 18.00) on a local computer. I installed nmap since winpcap can't do localhost traffic, but don't see anything using Wireshark. I don't have any experience decrypting SSL/TLS, but if it is clear text, I couldn't find any matches for pass, or user in the loopback logs. – Paraesthesia

I'd be happy to work with you on the issue as I had to deal with Eaglesoft for 10 years. Shoot me an e-mail. It's my first name 'aaron' at 'heyaaron.com'. Once we find a solution, we can post it here. – Undertone

Answer 4

I have written my own PHP driven website to access and manipulate data in my eaglesoft database. You simply create odbc connection to local DNS entry and done. To see database structure you can use the technical reference included in eaglesoft or advanced query tool.

Siltstone

Can you explain how you used PHP to establish a connection with eaglesoft database please. – Shirlshirlee