Accessing ERP Software Database based on SQL Server (1 Viewer)

Kronix

Registered User.
Local time
Today, 01:44
Joined
Nov 2, 2017
Messages
102
So I see that several types of Enterprise Resource Management software use MS SQL Server as their database platform. I was wondering, is it possible to use MS Access to access the database that these programs use the same way that you would access any other MS SQL Server database?

I think what I need to know is, when a piece of software says it is based on the SQL Server database platform, does that mean it stores its data in an MDF file that can be opened the same way as any other SQL Server database? Or is it possible that the database can somehow be encrypted and unable to access from a program outside of the ERP software, even if the programmer owns the ERP Software that created the database?

I thought this wasn't possible until I heard someone say that competing ERP software can convert their competitor's database since they are both based on SQL Server. Is it really that easy and if so how difficult would it be to instead write an Access program to work with the database that my ERP software creates (for example, if I don't like the ERP's front end or I want custom-made modules/features)?
 
Last edited:

jleach

Registered User.
Local time
Yesterday, 20:44
Joined
Jan 4, 2012
Messages
308
It's entirely up to the vendor and/or IT department that's hosting the server.

Pretty much any database (SQL Server, PostgreSQL, MySQL/MariaDB, etc) can be configured to allow external connections. So, if you can talk whoever's in charge of the thing to allow you to connect to it, then yes, you should be able to (all of them have ODBC support, which is what Access would need to connect).

If it's something that gets installed on-site and the db is managed by an internal IT team, that'd be who to talk to. Although, the software vendor themselves could restrict it so noone can get to it except through certain channels.

If it's the software vendor itself or a 3rd party cloud host, you'd need to talk to those people accordingly. (Often times for cloud based services, they might expose a REST API or SOAP implementation for web service access to it, but not to the db directly).

Some vendors try to control the database itself to try and force the buyer/users to stay with them (EMR/EHR is notorious for this). They want to own the data so you can't take it and move elsewhere. ERPs tend to be a bit better about that though.

So really, it depends. Technically, it is possible to connect to any ODBC data source (which includes pretty much any version of SQL Server), but whether or not you can in practice is up to whoever is in charge of the database and their willingness to allow you to do so.

hth
 

Ranman256

Well-known member
Local time
Yesterday, 20:44
Joined
Apr 9, 2015
Messages
4,339
as long as you have an ODBC driver, it should connect.
 

jleach

Registered User.
Local time
Yesterday, 20:44
Joined
Jan 4, 2012
Messages
308
That's completely untrue. Please read my prior reply.

Just because he has an ODBC driver does NOT mean he can connect. He needs to have appropriate permissions set up in the server, which is not always doable.
 

Kronix

Registered User.
Local time
Today, 01:44
Joined
Nov 2, 2017
Messages
102
Thanks. Before I talk to the vendors I want to poke around on my own a bit. The vendor will only try to sell me something.

I'm trying to understand what methods are available to the software vendor to prevent access to the database (even though I own and use the license for the software). Hard database password protection that only the program knows to access the database file (theoretically hackable)? Changing the data format of the database file itself? On the fly converting of the database file into a proprietary database format that only the program can read?

In general I'm trying to understand if all of the measures they can take to lock the SQL Server database are features that are built into MS SQL Server and therefore predictable, or whether programming the frontend in Visual Studio allows them to create custom locks and/or communication protocols with the database?

Assuming the ERP software is on our local network and I have full access to the computers, will the SQL Server database always be stored in an MDF file, in which case I could just try to connect with that file to see if access is allowed? Will the SQL Server database portion of the ERP software always be stored in a separate SQL Server file?

Can I assume competing ERP vendors sometimes have to work around proprietary locks by "hacking" the competing database if they want to transfer the data to their own database?
 

jleach

Registered User.
Local time
Yesterday, 20:44
Joined
Jan 4, 2012
Messages
308
It's not always that easy. There's lots of ways for them to prevent access to it: SQL Server is pretty robust as far as security goes and if they want to, they can lock if down pretty hard. Besides which, you might find yourself in a fairly nasty legal battle for trying to do so if it's not permitted.

They could use a proprietary format, but not sure anyone would really bother to do so as it'd be very difficult even for the simplest of cases.

Bottom line: don't count on being able to do it all the time. Have to go on a case by case basis to see if it's possible.
 

Kronix

Registered User.
Local time
Today, 01:44
Joined
Nov 2, 2017
Messages
102
Okay, but can I get an answer on the MDF file question? Do these front end programs normally still save the data in a standard SQL Server file, or is it realistically possible that the database can be stored in a way that can't even be detected by outsiders, for example by merging the data file into another proprietary file?

This is more a question of how SQL Server works -- whether it allows programs to dump the database data into a larger proprietary data file (and thus rely on the program to "feed" the data stream back to SQL Server), or the contrary if SQL Server always demands that the database itself is stored in a standard way determined by Microsoft or industry standards that makes it universally accessible (password protection notwithstanding).
 

jleach

Registered User.
Local time
Yesterday, 20:44
Joined
Jan 4, 2012
Messages
308
I don't know for sure, but if someone put a gun to my head and told me to take my best guess, I'd say no, it's not possible to introduce a proprietary data file format for SQL Server. Databases are an extremely fine-tuned engine humming along with all parts in harmonic motion (think about it: millions of pieces of data consulted and resolved in less time than it takes to snap your fingers...) I can't imagine that any RDBMS would support any sort of swappable persistence format. I could be wrong, but...

I would further note that just because you can get access to an .mdf may not necessarily mean you can do something with it. SQL Server is robust in security and it's layered thickly.

This all smells of someone trying to do something that shouldn't be done. Why not just check with the vendor in question an ask? (or, if you want a general idea, check with 10 vendors and get a reasonable idea).

I know no further and will bow out of this one now. Cheers,
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:44
Joined
Feb 19, 2013
Messages
16,553
you also have to consider what you want to do with the access once you have it. You may be able to achieve read access but I doubt you will get any other access without the required permissions/licences from the vendor - they won't want you messing with tables which if design or data is modified might well break the system. i.e. they will expect you to be a certified developer for that particular system.

I have done a fair number of data migrations over the years and in all cases, the data has to be downloaded from the old system by way of report into .csv/.xls and other formats, then modified to match (map to) the requirements of the new system. Often the upload to a new system will be denormalised tables which the new system will break back down into a normalised state suitable for its own purposes when imported. This is so it can ensure relationships are correctly created.

The fact that a number of applications all use the same backend is immaterial.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:44
Joined
Feb 19, 2013
Messages
16,553
I’ve never tried it but I would not be surprised if you can’t since it is unlikely to that 2005 will be forward compatible with 2008
 

WayneRyan

AWF VIP
Local time
Today, 00:44
Joined
Nov 19, 2002
Messages
7,122
Kronix,

If your application data resides in SQL Server it will be contained in SQL Server's
data files (.mdf, .ndf, .ldf).

Their front-end software is accessing the database using some ODBC driver.
Whether they use Windows Authentication or SQL Server Authentication you can emulate
that through MS-Access linked tables (providing you have appropriate access).

Regardless, with the appropriate SQL Server Admin account, you can be granted access
to their SQL Server data.

For full application functionality, the front-end might have some customized views and/or
functions that you will not have access to. But you can see the data.

Additionally, if the application uses Identity (sort of AutoNumber) or BIT columns,
your Access "view" into the data might have problems.

You CAN NOT move the Raw Data files (.mdf) or backup files (.bak) to a PRIOR version
of SQL Server. You CAN move the .BAK forward "roughly" a few versions.

You could move a .BAK file from 2005 --> 2008 --> 2014.

hth,
Wayne
 

Users who are viewing this thread

Top Bottom