ODBC - remote access

[Edwin Knoppert]

Often we get a problem where the database is on a different machine than the application which needs the data.

MS SQL Server can handle servernames and IP's and so on and works fine.
Problem is when i use the ODBC OleDB part i am depending on the driver choosen to allow me access to a remote computer.

Isn't there an overall way to do this, in fact fooling the driver?
Vpn with some ODBC bridge or similar comes to mind..?

?

[Michael Mattias]

ODBC datasources can point to a database anywhere accesible to the local machine. No trickery required. I do this all the time with SQL/Server via ODBC .

I'm also currently doing it with Sql/Server via OLE (ADO). Let me see if I have that OLE connection string here... here we go (UID and password changed)

Provider=sqloledb;Data Source=BPIAPPS;Initial Catalog=EntPortal_db;User ID=XXXXXX;Password=YYYYYYY;'

Works great.

I THINK (I'm pretty sure actually) you have to have installed the SQL/Server Client on the using machine.


MCM

[Edwin Knoppert]

I am talking about the other drivers.
It seems driver depending if it can do remote (through tcp for examle).
I was looking for an alternative and common 'bridge'.

Don't invest time looking for a solution for that specific driver, i am not looking for that.

[Michael Mattias]

Are you talking about something like RDO?

Or are you looking for some kind of "my driver can't handle remote databases, so I will use <X> instead?"

MCM

[Michael Mattias]

>Vpn with some ODBC bridge or similar comes to mind..?

I am no 'network guy' but I use VPNs to connect to several of my clients' systems.

When I do, it's as though I were sitting at the remote machine, so databases are no longer "remote." That is, I don't think 'remoteness' is a factor anymore.

What am I missing about your problem?

[Edwin Knoppert]

>I am no 'network guy' but I use VPNs to connect to several of my clients' systems.
Yes and then drives and printers are exchangable right?
How about ODBC connectivity?

Afaik it is not supported.

It's simple, if you take terminal server or remote desktop settings you can enable drive sharing but where is 'odbc sharing'?

[Michael Mattias]

All I can say re what I think you are talking about is... I provide services to firm in Appleton WI in which I ..

Start up a VPN client application
Connect to client's machine (or maybe to his network?)
Use Windows "Remote Desktop" to actually select and connect to "which machine" at client's site I want to work on.

Then it's like I am on his machine and everything I could do were I there I can do just fine from here. This includes both ODBC and OLE database work.

I also connect to another client via some Citrix thing, which I think is also a VPN.. from there I execute a local copy of PC Anywhere to connect to the machine of interest (not available directly from 'outside') and then I'm on that box.

I have other clients where I execute PC Anywhere on my computer and connect to a named machine at client site. With yet another I use Go To Meeting to operate the remote machine.

In all cases, it's just like I am there. All this Internet stuff has empowered me way beyond what I had ever dreamed about back when it took a priest and two altar boys plus the sacrifice of either three goats or one virgin just to get two computers to talk to each over over a dialup serial communications link.

If "being there without physically going there" is not what you want to do, then I don't understand what you want to do.

It almost sounds like you want to operate software on YOUR computer which can access a database on your customer/client's system, but you want to do it without a capable ODBC driver or OLE or other client provider installed on your system. I don't think that is possible, although many drivers/providers ARE capable of specifying the physical storage somewhere other than local.

MCM

[John Petty]

Michael
You are confused. VPN is a protocol which uses the internet to pass packets between reomte LAN's as if they were all on the same LAN and so anything that can be done on a LAN can be done via VPN ie (Virtual Private Network).
Terminal Services, Citrix, PC Anywhere etc etc that you list are NOT VPN's and as such have limited functions depending on which product you are useing they range from merely taking over the remote computers keyboard and mouse and displaying its screen to actually running another session on the remote computer (as is standard in Linux/Unix). It might have a simple file transfer protocol as well but thats about all
John

[John Petty]

Often we get a problem where the database is on a different machine than the application which needs the data.

MS SQL Server can handle servernames and IP's and so on and works fine.
Problem is when i use the ODBC OleDB part i am depending on the driver choosen to allow me access to a remote computer.

Isn't there an overall way to do this, in fact fooling the driver?
Vpn with some ODBC bridge or similar comes to mind..?

?

If I understand your question correctly the answer is yes. If the driver requires the data base to be on a drive connected to that computer then you just need to map the remote computers drive to an actual drive letter on that computer ie "G" or "Z" or your choice. If the data base is in a totally different location then you run a VPN and do the same mapping.
John

[Edwin Knoppert]

For most ODBC drivers you never access the db directly via the filesystem.
I need access and results from dsn on a remote computer.
The driver may be local to handle the execution and data but it should use the dsn declared on the remote computer.

I know it's odd but now i don't have a standard way i have to come up with a home brewed (web)server and gather the tabledata pass it on to the app on the local computer.
The client would then call a webserver which performs the task on the remote computer and passes the data in CSV or similar format.
This overhead is what i wanted to avoid writing myself and was looking for such a bridge.

[John Petty]

Edwin
VPN requires special hardware/software and knowledge about the remote computer which it seems you don't have. Sorry outside my level of knowledge so can't help you, so unlike certain other posters who feel a need to post even when they don't have a clue what they are posting about, I will drop out of the thread and hope someone with real knowledge in the area can help you.
John

[Edwin Knoppert]

Thanks for your help.
Imo this is a trivial question for a database user but may be there isn't a standard solution for this yet.

SQL server simply has built-in support to access the data via TCP, other drivers may have but requires knowledge of each specific driver.
A standard 'bridge' solution would be handy.
It may not exist.

[Erich Schulman]

Rather than a VPN, a ssh tunnel might help. You will then get a local port forwarded to a remote box, and the db driver is unaware that the db server is really remote. This, of course, assumes use of TCP/IP rather than named pipes.

[John Petty]

Edwin
There is no standard that I know of. MS SQL has a built in TCP protocol which requires great care when using (it was used by one of the most deverstating viruses ever). The client has to know (or have a way of finding) the IP address of the server. Then if the server is behind a firewall a specific port needs to be opened to allow the conection, MS SQL by default uses a particular port but a careful sysop would change that to a non standard for added protection anyway.
All client server data bases of course have some form of network protocol built in but most don't expose the details to users.
John

[Michael Mattias]

I need access and results from dsn on a remote computer.
The driver may be local to handle the execution and data but it should use the dsn declared on the remote computer.

I think you mean you need access and results from a DATABASE on a remote computer.

At some point the executing machine needs at least some kind of definition of the database.. something to relate the physical storage to the logical storage. This is done one of two ways: A proprietary interface featuring proprietary functions (eg, the Oracle Client Interface, the MySql API); or a standard interface such as ODBC or OLE.

A DSN provides a physical <===> logical definition is is most often used used for ODBC access, although the MS-Jet OLE provider can can get what it needs to know from an ODBC data source. (The OLE provider can also get what it needs to know from a UDL data link file).

Or, the physical storage needs to be accessible to a true server, which server is willing to talk to clients; the client-server conversation provides the logical ("programming") interface, at the client end.

You know what might work, but I have not tried is... using the 'URL=<value>' key/value attribute pair in an ADO connection string. My reference book says this attribute pair " Identifies the absolute URL of a file or directory."

This assumes of course you can resolve physical storage to some URL. (I have seen this but have no clue how it's done). Maybe it's worth looking into for your requirements.


MCM

[Edwin Knoppert]

Via google i can find some topics on 'odbc tunnel' and so on but seems not entirly what i want.
Since i make use of ADO/ADO.NET i could also create some interface for the ADO data parts but this may be very slow.

Maybe it is for the best to write some webserver kind of tool to gather the data.
New aspects arise, sysop's blocking tools like these.

[John Petty]

Its not that difficult to write your own if the data base owners will allow you to install a program on their server. Basically the program you install on their server does the actual ADO work. The remote client sends its data base request to that program which actually interacts with the data base via ADO or ODBC etc and then passes the result back to the remote computer. this is what MS SQL and Oracle etc have built in.
It is how most on line shopping carts work, ie for the ones I run for both security and bandwidth reasons the actual website is hosted by a profesional hosting service, when the website receives a request to view current details of a particular item or place and order it then passes that request back to the companies server which then sends the required info or order confirmation back to the web site hoster (non standard ports and good other security measures of course). Speed is usually not an issue as the data actually being passed is usually quite small.
Sysops are usually not a problem if it is all clearly and correctly documented as they actually have to do a lot of settings to actually make it work. Starting from the internet connection to the site with the data base here are some of the considerations.
1. The remote client need to know the internet IP or how to find it of the data base server, ie if not static DDNS (for some years I ran a simple private DDNS that I wrote so that information was very restricted).
2. The firewall needs to open up the private port being used by the remote client. Some Sysops will request that the client computers must themselves be able to supply a static internet IP so that port is only open to known by IP clients.
3. If the servers internet connection is also running NAT (common) then then the port needs to be forwarded to the correct computer.
4. If the server is a windows comp then the windows firewall also needs to be set up to allow it as well.
I have written some simple internet tunnels not using VPN. I still have one running which allows me to monitor the status and request logs sent by email to me of a program I wrote custom for the client (who is thousands of miles away in a different country). Fortunately I had no problems with the Sysop as that is also me

[Edwin Knoppert]

We'll see, my tool is < 2MB and could be rewritten to http server.
I could add webbased imput so it can be set remotely.
I currently have unique dialogs to setup a connection and some specific settings.
This requires writting forms for each import, it may be neater to use html.

Let's see what they have to say about that idea

Added:
Hmm, forgot the alternative dialogs like the datalink, that's a problem..