views:

49

answers:

2

Pardon my outrageous silliness, I don't know if this is even possible.

Here's the situation.

There is an MS Access "database" (yes, I know, believe me, I know) which I'll need to SELECT, UPDATE and INSERT to from a remote location. The catch is that this needs to happen securely.

I have complete control over the remote machine which hosts the MS Access file, so I can put in drivers and software as I please. The server is Microsoft Windows Server 2003.

The approach that I had intended to take was to host a PHP script on an HTTPS server (using either Apache or IIS, doesn't matter), send XML to the PHP script which would then do its thing on the MS Access database and send XML results back. However, due to time restraints, I'm trying to figure out if I can connect directly through ODBC in a secure manner, and have it speak to an MS Access database.

It's my understanding that ODBC is not exactly famous for being secure, but that there are ODBC drivers that support encrypted connections, or that I can somehow tunnel the ODBC connection through SSL. However, all the information I have found so far relies on the database being Microsoft SQL.

In particular I'm interested if there are ways to SSL-ify ODBC connections without regard to the underlying database. I could probably figure that out on a Unix-clone by myself, but the host is a Windows Server 2003 in which case, I don't know how to proceed.

Is this possible at all? Any information highly appreciated!

+1  A: 

Usually one puts an intermediary between clients and the database. The intermediary handles authentication, authorization, secure data transmission, etc. You assume that the database is inside your firewall, in a secure area. All the things you want to add to make things secure for clients that are outside your firewall are handled by the intermediary.

Being a Java person, I would automatically think web client talking to one or more servlets. Let the servlet handle authentication and authorization. HTTP means no firewall worries. You can use HTTPS, too.

I think that'd be easier to put in place. Besides, even an SSL-ified ODBC connection still exposes your database to the wider Internet. I wouldn't want my data in such a repository. Would you?

duffymo
Well, yeah, that was the original idea with the PHP method, to have an intermediary. That's what we can still do, but if we could just SSL the ODBC connection, that would suffice. We will have a firewall that only allows a particular IP to connect, and through SSL the identity of the remote server will be secured, and encrypted, and we figure that's enough for us. But the intermediary step is what we will do if this turns out to be impossible or wildly impractical. Thanks for the input.
Helgi Hrafn Gunnarsson
+1! Without an intermediary layer, the client must have file-system access to the (pardon the pun) Access file. An intermediary layer prevents direct user-tampering with the file.
kbrimington
+1  A: 

The problem here is you are not quite understanding how an ODBC connection works with access. We are not talking about a TC/IP or socked based connection here.

If you look at ANY connection string for an JET to access file, you see in the ODBC connection will always, I REPEAT ALWAYS include a fully qualified windows path name. When I say a fully qualified windows path name, I am talking to about a file that sitting on the hard disk.

At the end of the day we are thus talking about opening a plain Jane windows file. A horse is a horse is a horse and a windows file is a windows file, is a windows file.

In other words we are talking about opening a file sitting on the hard disk. So, this whole process is not any different than opening excel file, a text file, a PowerPoint file, or in this case an access file that just also happens to be sitting on the hard disk.

There's no server or particular database software that EVER has to be installed on the computer where this file sets. It is the CLIENT SIDE that must have the software and execute a standard windows file open command to pull the data off the disk drive. Remember when you place a word file on a server and open it, you never had to install word on the server, is the client side that's doing a windows standard file open, and the exact same scenario applies to JET when it opens a access file.

What this means then if you're going to open this file up over an Internet connection, you therefore must extend windows networking over the Internet. HTTP, or even FTP is nothing remotely close to the windows file networking protocol.

However, you can extend windows networking system over the Internet, and this is typically done by which called a VPN (virtual private network). That means you'll have to set up a VPN. This will thus allow you to see this other computer via network neighborhood and browse to the files on that folder on the server, and simply open it. Again your opening a standard windows file, there's not some type of service running on the server that you can connect to like with SQL server.

You can read the following article of mine and I explain why running a VPN over the Internet with windows networking and a JET (access) file simply will not work in an reliable fashion:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

So, just keep in mind that if you look at any JET ODBC connection string, you'll notice it's never a IP based, but must be a FULLY QUALIFIED STANDARD windows file name. I cannot stress and repeat again that we talking about a standard windows file name and location that we going to open.

Remember this is no different than opening word or excel or PowerPoint. The ODBC driver confuses this issue, since the driver is ONLY required to be installed and setup on the client side, there's nothing to connect two on the server side, except the required ability to open a standard plain Jane windows file.

What you thus ask as possible with a VPN, but not practical. You can read the above article and it explains in detail why this cannot reliably work and function.

With the advent of several free editions of SQL server, and so many other choices, the above limitation is likely not going to be an issue for you. These other server database systems are not file based, and your connection strings will NEVER resolve to some file name. And, thus these database servers also do not require the windows networking proto call to open that file, and therefore you can even connect to servers such as running linux etc. that don't even have windows networking installed. For a jet connection, you have to use windows networking to directly open the file .

Albert D. Kallal
In short, ODBC can't be used remotely securely with Jet/ACE, and you have to have an intermediate layer (e.g., PHP) to secure the data.
David-W-Fenton
Okay, I believe you, but I want to understand this and I'm not quite sure that I get it. First, I understand fully that ODBC only opens up the Access database as a file, and not through TCP/IP. I'm not concerned with the connection from ODBC to Access. They're on the same host.What I *am* concerned with, is the connection from my program to ODBC.I was under the impression that ODBC could be connected to remotely and that the underlying data source was irrelevant. When I connect to the ODBC, I don't have to specify the file name, I merely specify the ODBC data source name.
Helgi Hrafn Gunnarsson
I guess what's preventing me from understanding this clearly, is that if the client (which I would say "connects to ODBC" which is perhaps the wrong terminology) needs access to the Access file itself... why would anyone want to use ODBC for connecting to an MS Access file? Why are there ODBC drivers for MS Access in the first place? Thanks for the response, Albert!
Helgi Hrafn Gunnarsson
Jet/ACE is a file-based database engine, and ODBC is a database abstraction layer that can connect to any data source for which there is an ODBC driver. That it can't be done remotely from another machine is just the nature of connecting to file-based data sources, as opposed to server data sources. At least, that's the way I understand it. As an Access developer, I never use ODBC for Jet/ACE, since I'm prohibited from doing so by Access itself (and rightly so).
David-W-Fenton
@ wrote: Why are there ODBC drivers for MS Access?/ / / / /Because then any piece of software that supports ODBC drivers can then thus read and write access files. The problem is that JET is file based and not a server or even a service or process like MySql (JET runs in-process). The ODBC driver connects to JET that MUST THEN open the windows file direct, but this still allows any program that supports ODBC to use Access files. For MySql, the odbc driver NEVER opens the file direct, so you don't even care if MySql running on windows or a Linux box.
Albert D. Kallal
I get it. Thanks. One more reason to advise against MS Access in the future. ;)
Helgi Hrafn Gunnarsson
JET now supports disconnected recordsets to SharePoint. Pull network plug and JET will flip into off-line mode and when re-connected data starts to flow (sync) again. You are confusing access the developers tool with that of the database engine. Access like any developer tool requites you to choose the appropriate database engine and technology such as SharePoint, SQL server Oracle etc. Not only does new JET support disconnected records, but it also has support for the cloud (Azure) edition of SQL server. So don't let you general ignorance of computers to piss on what you know nothing about.
Albert D. Kallal