Using Access to 'access' an sql database on a webserver

[Flic]

Is this possible?

I know about a bit about ODBC and found how to import an ODBC database
stored on the computer, however I am after accessing an SQL database
stored on a webserver. I'd like to keep it up to date, but that could
probably be done with a macro.

At the moment I'm just considoring possible options, so just need to
know if it can be done, how easy and a rough idea of how.

Any help would be much appreciated!

Flic

[Tom van Stiphout]

On 6 Oct 2006 03:59:25 -0700, "Flic" <Fe*******@gmail.comwrote:

I typically create an ADP, then connect to such db specifying the IP
address and the u/pw I got from the ISP.

-Tom.

>Is this possible?

I know about a bit about ODBC and found how to import an ODBC database
stored on the computer, however I am after accessing an SQL database
stored on a webserver. I'd like to keep it up to date, but that could
probably be done with a macro.

At the moment I'm just considoring possible options, so just need to
know if it can be done, how easy and a rough idea of how.

Any help would be much appreciated!

Flic

[Albert D. Kallal]

Yes, you can connect to the sql server.

However, how that server is exposed to the wild wild internet is another
matter!!

Most (if not all) administrators of their web sites NEVER EVER allow their
database server to be exposed to a wide open port on the internet.

So, can you do this...sure. However, you need go to web server, and setup
the permissions to allow this....

(certanly not a ms-access question on how set those permissosn, but yes, you
can do this).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Lyle Fairfield]

Albert D. Kallal wrote:

Most (if not all) administrators of their web sites NEVER EVER allow their
database server to be exposed to a wide open port on the internet.

How do you know this?

[Albert D. Kallal]

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

Albert D. Kallal wrote:

>Most (if not all) administrators of their web sites NEVER EVER allow
their
database server to be exposed to a wide open port on the internet.

How do you know this?

Because 99% of any commercial site that obviously has a data driven web site
does NOT leave the sql server port open to the wild internet.

Since open sql servers is a very widely know security risk, then 99% of web
admins with a brain don't open the sql server to the wild internet.

In fact, most ISP's that provide a sql server package as part of the monthly
hosting don't allow a open connection at a all. (what they do allow, and
what my provider allows is using a ssl connection). So, most of the time,
one can use a VPN, or a ssl connection.

But a wide open sql server exposed to the internet? No, this is quite rare.
My ISP does not even allow this!!

So, how do I know this? My experience simply agrees with th above. Having
sql server ports exposed to the net is NOT the norm, and for the most part
is quite rare these days...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[David W. Fenton]

"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:jYvVg.100563$5R2.76375@pd7urf3no:

In fact, most ISP's that provide a sql server package as part of
the monthly hosting don't allow a open connection at a all. (what
they do allow, and what my provider allows is using a ssl
connection). So, most of the time, one can use a VPN, or a ssl
connection.

Lyle will dispute you on this, as he disputed me when I made the
same point. This is because Lyle has found a handful of ISPs that
provide open ports for SQL Server. This would be a good reason not
to use those providers, seems to me, but Lyle likes it that way.

Of course, I don't believe in anything but LAMP-based web hosting,
so it's never an issue for me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Flic" <Fe*******@gmail.comwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:

Is this possible?

I know about a bit about ODBC and found how to import an ODBC
database stored on the computer, however I am after accessing an
SQL database stored on a webserver. I'd like to keep it up to
date, but that could probably be done with a macro.

At the moment I'm just considoring possible options, so just need
to know if it can be done, how easy and a rough idea of how.

Whether it's a web server is irrelevant, but asking the question
implies that you want an HTTP connection. That is not possible in
any current version of Access.

However, Access 2007 will use HTTP for connections to SharePoint
servers.

What you're asking is can you connect to a database port on an ISP's
server across the Internet. That's possible if the host opens the
port for Internet access.

However, I'd never use a host that did that outside a VPN (or, at
the very least, limited to a finite IP address space).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

David W. Fenton wrote:

This is because Lyle has found a handful of ISPs that
provide open ports for SQL Server.

Perhaps it is more than a handful.

http://www.google.ca/search?hl=en&q=ms-sql+hosting

[Larry Linson]

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.com...

>
David W. Fenton wrote:

>This is because Lyle has found a handful of ISPs that
provide open ports for SQL Server.

Perhaps it is more than a handful.

http://www.google.ca/search?hl=en&q=ms-sql+hosting

Big hands, Lyle, big hands. <GRIN>

[Arno R]

"Larry Linson" <bo*****@localhost.notschreef in bericht news:I4GVg.1025$e65.165@trnddc05...

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.com...

David W. Fenton wrote:

This is because Lyle has found a handful of ISPs that
provide open ports for SQL Server.

Perhaps it is more than a handful.

http://www.google.ca/search?hl=en&q=ms-sql+hosting

Big hands, Lyle, big hands. <GRIN

Looks like a bucket to me .... or perhaps even *lots* of buckets.
;-)

Arno R

[Albert D. Kallal]

>

>This is because Lyle has found a handful of ISPs that
provide open ports for SQL Server.

Perhaps it is more than a handful.

http://www.google.ca/search?hl=en&q=ms-sql+hosting

Hey, a zillion providers host sql server for you. However, the majority of
those STILL DO NOT allow a open SQL SERVER port to the internet (this means
you can't connect to the sql server over internet using a ip + port address.
Your web software running on that host can freely connect the hosted sql
server no problem at at. Often, you can even do this without even using a
password. This access to the sql server is LIMITED TO THE web server side,
but is NOT open to the public ip address. So, once again, to connect to the
sql server OVER the intent, you have to use a secure tunnel (ssl) in most
cases..

So, you STILL HAVE TO use a tunnel (ssl).
The hosted sql server ports ARE NOT OPEN TO THE INTERNET...

So, I stand by what I said.. Read it again...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Lyle Fairfield]

Hey, a zillion providers host sql server for you. However, the majority of

those STILL DO NOT allow a open SQL SERVER port to the internet (this means
you can't connect to the sql server over internet using a ip + port address.
Your web software running on that host can freely connect the hosted sql
server no problem at at. Often, you can even do this without even using a
password. This access to the sql server is LIMITED TO THE web server side,
but is NOT open to the public ip address. So, once again, to connect to the
sql server OVER the intent, you have to use a secure tunnel (ssl) in most
cases..

TTBOMK the paragraph above is quite wrong.

All the hosts/providers with which I am familiar allow a connection
over the internet using

ServerName: Something Like MS-SQL-Server12.DiscountAsp.Net
User Name: ___
Password: ___
DefaultDatabase: ___

That is, one can connect to them with an ADP or other ADO connection if
one has the requisite information.

In addition, when we ping the string used as the ServerName, in the
case above, MS-SQL-Server12.DiscountAsp.Net as in
ping MS-SQL-Server12.DiscountAsp.Net
an ip address in the form
255.255.255.255 is returned.

We can use this ip address in place of MS-SQL-Server12.DiscountAsp.Net.

If the server does not use the default port, 1433, we may have to add
the port, (say 4507) as in
255.255.255.255,4507.

Of course, these are vulnerable to brute force attacks. One could
programmatically bombard the server (assuming one knows its name) with
millions of different user names and passwords. But the servers log
every attempt to log in and a programmed response to not very many
multiple failures result in the ip address from which those failures
come being cut off.

I disagree with Albert's suggestion that it is safer to access
SQL-Server from a web-server. This is as safe as the web-server
security. Why would we think that web-server security would be safer
than SQL-Server security? For my own part, I have found that it is not,
and that by executing a disk sector read of my web-server (hosted by a
huge Atlanta company), I could find sql user-names and passwords of
sites sharing the web-server with me and connecting to the sql-server
from the web-server through ASP. When I communicated this problem to
support/security they were indifferent, not seeming to know what I was
talking about. I changed web-server hosts at that time.

I have not read of any breach of security at any of the sql-server
sites with which one connects as I have described although these
certainly may have happened. (To exploit the vulnerability I described
one had to know how to do a disk sector read of the web server hosting
one's web-site; this does not seem to be such common knowledge and I
don't know if it's possible any more; that was years and years ago.)

I have used these Internet Servers for more than six years without any
problem on my part.

I think I will check out of this thread. It seems that only a few
Access developers use Internet available MS-SQL servers. That's fine
with me. Internet available MS-SQL servers work for me; they're
cheap; they're secure for me; they're available anywhere in the world;
they're maintained well.

I don't get any points for others who sign up.

The End

[Rick Brandt]

Albert D. Kallal wrote:

Hey, a zillion providers host sql server for you. However, the
majority of those STILL DO NOT allow a open SQL SERVER port to the
internet (this means you can't connect to the sql server over
internet using a ip + port address. Your web software running on that
host can freely connect the hosted sql server no problem at at.
Often, you can even do this without even using a password. This
access to the sql server is LIMITED TO THE web server side, but is
NOT open to the public ip address. So, once again, to connect to the
sql server OVER the intent, you have to use a secure tunnel (ssl) in
most cases..
So, you STILL HAVE TO use a tunnel (ssl).
The hosted sql server ports ARE NOT OPEN TO THE INTERNET...

So, I stand by what I said.. Read it again...

Actually that is what I was expecting to see when I clicked on a few of those
links, but the first few I tried DID say that all you needed was the IP and user
credentials and one even allowed Enterprise Manager access.

Of course when these places host SQL Server for you it is still YOUR data. What
do they care how secure it is? I'm sure the fine print absolves them from any
liability in those areas. I wonder how many corporate servers hosting *their
own data* have the ports open to the net.

I used to have an Access app in use by around 25 remote users that communicated
with SQL Server over ODBC using an open port on our server. Our sys admins
insisted that this was NOT secure and said I needed to change to a model that
used HTTP requests. When I inquired on the SQL Server newsgroups about whether
their concerns were legitimate the consensus of the responders was that they
would not advocate an open port to the internet if it were their call and their
responsibility.

So, does Microsoft have an opinion published anywhere? As it turns out the
switch to HTTP requests was a great opportunity to learn a bunch of new stuff
and the result actually works very well. It certainly is a lot more work than a
straight connection though.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Albert D. Kallal]

All the hosts/providers with which I am familiar allow a connection

over the internet using

Well, a the vast majority of the ones I am aware of don't allow the above...
For the most part, a open sql port is not available.

>
I disagree with Albert's suggestion that it is safer to access
SQL-Server from a web-server.

The above was not a suggestion.

This is as safe as the web-server
security. Why would we think that web-server security would be safer
than SQL-Server security?

Because, just like you don't have to give users access to the files via ftp,
that very same web server has complete and free rein to access those files,
but Joe public DOES NOT!!!

The web server DISHES out your web files to users without problems, but
those users NEVER CAN SEE OR ACCESS the files directly. This is exactly the
same concept for that web server accessing the sql database. So, the
permissions set for the web server to access the sql server are VASTLY MORE
OPEN THEN THE INTERNET.

I not making a suggestion, I am simply pointing out the obvious. When you
use ebay, the ebay web server has no trouble accessing their sql servers,
but you think Joe pubic can do that? Come on, I not making a suggestion
here, I am simply telling you how most web servers are setup, and they can
be given full access and full rights to the sql server, but THIS HAS NOTHING
TO DO WITH the public users of that site. Public users don't have rights to
view, grab individual files like the web sever does...

>I could find sql user-names and passwords of
sites sharing the web-server with me and connecting to the sql-server
from the web-server through ASP. When I communicated this problem to
support/security they were indifferent, not seeming to know what I was
talking about. I changed web-server hosts at that time.

Well, yes, if you are taking about ASP pages hosted on that site...right? If
you are taking about any ASP page on the web, then you have a COMPLETE
different story...and one that I don't believe. So, taking about ASP pages
uploaded to your provider?...well, no kidding Sherlock!!!, then of course
that is the case.

However, the fact that ASP pages loaded to a provider server that can
open/access their instance of sql server is not a big deal, and is expected
(if your users can do that, then they are free to modify (deface) your web
site!!!.

However, that is grand canyon of difference from telling me that the sql
server is open to the internet by a simple ip + password (they are not).
Even when those ASP pages have free reign to sql server, that DOES NOT give
end users public access, nor can they open/use the sql server via a ip +
port number.

To confuse your hosted web site access to sql server with that of the public
access is a really rotten way to have a debate here. Please work on this
clarity issue.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[David W. Fenton]

"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:QfRVg.103640$5R2.49970@pd7urf3no:

>All the hosts/providers with which I am familiar allow a
connection over the internet using

Well, a the vast majority of the ones I am aware of don't allow
the above... For the most part, a open sql port is not available.

I would never host with an ISP that allowed it. Period.

It's just bloody stupidly insecure.

The only method by which it should be allowed is via VPN or SSL
tunnel. The latter should be fairly easily availalbe from any
reputable hosting organiztion.

>I disagree with Albert's suggestion that it is safer to access
SQL-Server from a web-server.

The above was not a suggestion.

Right -- it's a fact.

>This is as safe as the web-server
security. Why would we think that web-server security would be
safer than SQL-Server security?

Because, just like you don't have to give users access to the
files via ftp, that very same web server has complete and free
rein to access those files, but Joe public DOES NOT!!!

The key difference is that HTTP users have only as much access to
your database as you've programmed for them to have. They have no
way of directly getting to the database to execute arbitrary SQL
(unless you've written a bad app that allows SQL injection).

The web server DISHES out your web files to users without
problems, but those users NEVER CAN SEE OR ACCESS the files
directly. This is exactly the same concept for that web server
accessing the sql database. So, the permissions set for the web
server to access the sql server are VASTLY MORE OPEN THEN THE
INTERNET.

Because the HTTP server can only do what you've programmed in your
application that sits between the HTTP server and the database.

I not making a suggestion, I am simply pointing out the obvious.

It is obvious, Albert, to anyone with half a brain.

When you
use ebay, the ebay web server has no trouble accessing their sql
servers, but you think Joe pubic can do that?

Type alert! :)

Come on, I not making a suggestion
here, I am simply telling you how most web servers are setup, and
they can be given full access and full rights to the sql server,
but THIS HAS NOTHING TO DO WITH the public users of that site.
Public users don't have rights to view, grab individual files like
the web sever does...

I don't know why so many Access developers have absolutely no
concept of security at all, but it seems that many of those who post
here, some of them quite respected, simply don't have any training
or experience in the subject.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

Albert D. Kallal wrote:

All the hosts/providers with which I am familiar allow a connection
over the internet using

Well, a the vast majority of the ones I am aware of don't allow the above....
For the most part, a open sql port is not available.

I have just added a new MS-SQL 2005 database named SQL2005_51315_ffdba
to those hosted for me by DiscountAsp.Net. It is new. I have added no
tables, or other objects.

It is available to me through an (SQL) port on the Internet. That is, I
can connect directly to it with ADO using its IP address, the name of
the database (given above), User Name and Password. An example of such
a connection is one used by an MS-Access ADP file.

I invite you to login in to this database and create a table called
"Albert D Kallal". As the database is mine, I am stating that I will
hold you free of any civil or legal problems that such hacking,
tampering or breaching of security might bring up. W can explain that
this is all a test (intended for the extension of knowledge) among
friends should any authority intercede or question us.

When you have created this table, please, let me know with a post here
in CDMA, or by e-mail and we can jointly point out the vulnerability of
such database hosting.

I will point out that I have made it a point not store any of the data
required for login on my own or any ffdba computer, nor on my website,
so that a secondary approach through those routes is unlikely to be
successful.

And, to start you on your way I'll reveal the major security software
running at the site as below:

"TippingPoint Intrusion Prevention Systems
The TippingPoint Intrusion Prevention System (IPS) delivers the most
powerful network protection in the world. The TippingPoint IPS is an
in-line device that is inserted seamlessly and transparently into the
network. As packets pass through the IPS, they are fully inspected to
determine whether they are legitimate or malicious. This instantaneous
form of protection is the most effective means of preventing attacks
from ever reaching their targets.

TippingPoint's Intrusion Prevention Systems provide Application
Protection, Performance Protection and Infrastructure Protection at
gigabit speeds through total packet inspection. Application Protection
capabilities provide fast, accurate, reliable protection from internal
and external cyber attacks. Through its Infrastructure Protection
capabilities, the TippingPoint IPS protects VoIP infrastructure,
routers, switches, DNS and other critical infrastructure from targeted
attacks and traffic anomalies. TippingPoint's Performance Protection
capabilities enable customers to throttle non-mission critical
applications that hijack valuable bandwidth and IT resources, thereby
aligning network resources and business-critical application
performance.

The system is built upon TippingPoint's Threat Suppression Engine (TSE)
- a highly specialized hardware-based intrusion prevention platform
consisting of state-of-the-art network processor technology and
TippingPoint's own set of custom ASICs. The TippingPoint ASIC-based
Threat Suppression Engine is the underlying technology that has
revolutionized network protection. Through a combination of pipelined
and massively parallel processing hardware, the TSE is able to perform
thousands of checks on each packet flow simultaneously. The TSE
architecture utilizes custom ASICs, a 20 Gbps backplane and
high-performance network processors to perform total packet flow
inspection at Layers 2-7. Parallel processing ensures that packet flows
continue to move through the IPS with a latency of less than 215
microseconds, independent of the number of filters that are applied.

The TippingPoint TSE architecture also enables traffic classification
and rate shaping. Sophisticated algorithms baseline "normal" traffic
allowing for automatic thresholds and throttling so that mission
critical applications are given a higher priority on the network.

The TippingPoint IPS family offers a range of products that differ in
capacity and the number of simultaneous segments they protect.

TippingPoint X505
TippingPoint 50
TippingPoint 200
TippingPoint 200E
TippingPoint 400
TippingPoint 1200E
TippingPoint 2400E
TippingPoint 5000E
TippingPoint SMS (Enterprise-Level Management System)
TippingPoint ZPHA (Zero Power High Availability)
An integral part of the TippingPoint solution is the Digital Vaccine®
Service that delivers new filters on a weekly or even daily basis to
maintain evergreen protection for the latest vulnerabilities, exploits,
viruses and rogue applications."
I trust this will help everyone to understand the vulnerability that
you have described. As I am going away on holidays in a few weeks I
hope we can wrap this up quickly. Let me know as soon as the table is
created.

I apologise for posting to this thread after I indicated that I would
not do so.

Happy (Canadian) Thanksgiving!

[Lyle Fairfield]

David W. Fenton wrote:

"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:QfRVg.103640$5R2.49970@pd7urf3no:

All the hosts/providers with which I am familiar allow a
connection over the internet using

Well, a the vast majority of the ones I am aware of don't allow
the above... For the most part, a open sql port is not available.

I would never host with an ISP that allowed it. Period.

It's just bloody stupidly insecure.

I have just added a new MS-SQL 2005 database named SQL2005_51315_ffdba
to those hosted for me by DiscountAsp.Net. It is new. I have added no
tables, or other objects.

It is available to me through an (SQL) port on the Internet. That is, I
can connect directly to it with ADO using its IP address, the name of
the database (given above), User Name and Password. An example of such
a connection is one used by an MS-Access ADP file.

I invite you to login in to this database and create a table called
"David W Fenton". As the database is mine, I am stating that I will
hold you free of any civil or legal problems that such hacking,
tampering or breaching of security might bring up. W can explain that
this is all a test (intended for the extension of knowledge) among
friends should any authority intercede or question us.

When you have created this table, please, let me know with a post here
in CDMA, or by e-mail and we can jointly point out the vulnerability of
such database hosting.

I will point out that I have made it a point not store any of the data
required for login on my own or any ffdba computer, nor on my website,
so that a secondary approach through those routes is unlikely to be
successful.

And, to start you on your way I'll reveal the major security software
running at the site as below:

"TippingPoint Intrusion Prevention Systems
The TippingPoint Intrusion Prevention System (IPS) delivers the most
powerful network protection in the world. The TippingPoint IPS is an
in-line device that is inserted seamlessly and transparently into the
network. As packets pass through the IPS, they are fully inspected to
determine whether they are legitimate or malicious. This instantaneous
form of protection is the most effective means of preventing attacks
from ever reaching their targets.

TippingPoint's Intrusion Prevention Systems provide Application
Protection, Performance Protection and Infrastructure Protection at
gigabit speeds through total packet inspection. Application Protection
capabilities provide fast, accurate, reliable protection from internal
and external cyber attacks. Through its Infrastructure Protection
capabilities, the TippingPoint IPS protects VoIP infrastructure,
routers, switches, DNS and other critical infrastructure from targeted
attacks and traffic anomalies. TippingPoint's Performance Protection
capabilities enable customers to throttle non-mission critical
applications that hijack valuable bandwidth and IT resources, thereby
aligning network resources and business-critical application
performance.

The system is built upon TippingPoint's Threat Suppression Engine (TSE)
- a highly specialized hardware-based intrusion prevention platform
consisting of state-of-the-art network processor technology and
TippingPoint's own set of custom ASICs. The TippingPoint ASIC-based
Threat Suppression Engine is the underlying technology that has
revolutionized network protection. Through a combination of pipelined
and massively parallel processing hardware, the TSE is able to perform
thousands of checks on each packet flow simultaneously. The TSE
architecture utilizes custom ASICs, a 20 Gbps backplane and
high-performance network processors to perform total packet flow
inspection at Layers 2-7. Parallel processing ensures that packet flows
continue to move through the IPS with a latency of less than 215
microseconds, independent of the number of filters that are applied.

The TippingPoint TSE architecture also enables traffic classification
and rate shaping. Sophisticated algorithms baseline "normal" traffic
allowing for automatic thresholds and throttling so that mission
critical applications are given a higher priority on the network.

The TippingPoint IPS family offers a range of products that differ in
capacity and the number of simultaneous segments they protect.

TippingPoint X505
TippingPoint 50
TippingPoint 200
TippingPoint 200E
TippingPoint 400
TippingPoint 1200E
TippingPoint 2400E
TippingPoint 5000E
TippingPoint SMS (Enterprise-Level Management System)
TippingPoint ZPHA (Zero Power High Availability)
An integral part of the TippingPoint solution is the Digital Vaccine®
Service that delivers new filters on a weekly or even daily basis to
maintain evergreen protection for the latest vulnerabilities, exploits,
viruses and rogue applications."
I trust this will help everyone to understand the vulnerability that
you have described. As I am going away on holidays in a few weeks I
hope we can wrap this up quickly. Let me know as soon as the table is
created.

I apologise for posting to this thread after I indicated that I would
not do so.

Happy (Canadian) Thanksgiving!

[Lyle Fairfield]

Lyle Fairfield wrote:

Albert D. Kallal wrote:

All the hosts/providers with which I am familiar allow a connection
over the internet using

Well, a the vast majority of the ones I am aware of don't allow the above...
For the most part, a open sql port is not available.

I have just added a new MS-SQL 2005 database named SQL2005_51315_ffdba
to those hosted for me by DiscountAsp.Net. It is new. I have added no
tables, or other objects.

It is available to me through an (SQL) port on the Internet. That is, I
can connect directly to it with ADO using its IP address, the name of
the database (given above), User Name and Password. An example of such
a connection is one used by an MS-Access ADP file.

I invite you to login in to this database and create a table called
"Albert D Kallal".

Hasn't happened yet. Guess I'll just use the database feeling fairly
safe in doing so.