Can I access my private db through my web server?

[bcanavan]

Web server: win2003 server with iis6
DB: sql server 2000

I have 50+ remote offices running an Access 2002 app which connects
directly to sql server at a 3rd party hosting company, in part using an
odbc connection on the workstation.

We recently moved our database (and our web site) in-house. The new
database is inside the firewall but outside the dmz, where it lives
along side the company's most sensitive databases. IT's policy won't
allow us to connect directly from the internet. I'm trying to avoid
buying another copy of sql server for the Access app, which I one day
hope to drop anyway in favor of

The web server uses port 1433 to talk to the database. I was wondering
if there is a simple way to route requests from the Access app through
the web server.

Thanks,
Bob

[Erland Sommarskog]

(bc******@bmghomes.com) writes:

Web server: win2003 server with iis6
DB: sql server 2000

I have 50+ remote offices running an Access 2002 app which connects
directly to sql server at a 3rd party hosting company, in part using an
odbc connection on the workstation.

We recently moved our database (and our web site) in-house. The new
database is inside the firewall but outside the dmz, where it lives
along side the company's most sensitive databases. IT's policy won't
allow us to connect directly from the internet. I'm trying to avoid
buying another copy of sql server for the Access app, which I one day
hope to drop anyway in favor of

The web server uses port 1433 to talk to the database. I was wondering
if there is a simple way to route requests from the Access app through
the web server.

Have you considered setting up a VPN?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[bcanavan]

Thanks, Erland.

VPN is a good suggestion, but my second paragraph above was cut off and
should have read:

"I'm trying to avoid buying another copy of sql server for the Access
app, which I one day hope to drop anyway in favor of a pure web app."

If I can do that by the end of the year I'll have saved us all many
hours of unnecessary work since just about everything is begin
converted for web access, and VPN won't be necessary. (In my case my
remote offices are sales offices who don't collaborate with each other
on documents, spread sheets, etc.)

So, if I could just address a connection from the Access app -->
through the web server --> to the database (and for read-only requests
at that!) I would be very happy.

Bob

[Erland Sommarskog]

(bc******@bmghomes.com) writes:

VPN is a good suggestion, but my second paragraph above was cut off and
should have read:

"I'm trying to avoid buying another copy of sql server for the Access
app, which I one day hope to drop anyway in favor of a pure web app."

If I can do that by the end of the year I'll have saved us all many
hours of unnecessary work since just about everything is begin
converted for web access, and VPN won't be necessary. (In my case my
remote offices are sales offices who don't collaborate with each other
on documents, spread sheets, etc.)

So, if I could just address a connection from the Access app -->
through the web server --> to the database (and for read-only requests
at that!) I would be very happy.

I will have to admit that networking is not my speciality, but what you
describes sounds very much like VPN to me.

That is, with VPN you would have to buy another SQL Server for the Access
people; they could connect directly to SQL Server once they have their
VPN up.

Route it through the web server? Of course, you could have a proxy that...
But I don't think you admin folks would like that.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[bcanavan]

Like I said...if we used vpn we wouldn't have to buy/install another
copy of sql server. Users could log in through Active Directory.

I'm trying to avoid the time and expense of setting up vpn in 50
offices when it's likely to be replaced by a web app by December.

Bob

[Erland Sommarskog]

(bc******@bmghomes.com) writes:

Like I said...if we used vpn we wouldn't have to buy/install another
copy of sql server. Users could log in through Active Directory.
I'm not sure that I get this. In your previous post you seemed to
be anxious that VPN would require another server. Now you are saying
that it does not.
I'm trying to avoid the time and expense of setting up vpn in 50
offices when it's likely to be replaced by a web app by December.

VPN is the only that I can think of that is secure. It's possible that
you could set up a proxy through a web page (this is definitely not
the group what you can do in a web server), but it certainly sounds
dangerous to me. After all, then you are exposing SQL Server on the
Internet - which you admin folks don't like.

Since setting up VPN on the connecting side is a simple task, I'm not
really sure that I see the problem.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[bcanavan]

Thanks, Erland.

The point of my post was that VPN is a good suggestion, but doesn't
look feasible at the moment. VPN would not require a new file server,
or a new copy of sql server, because users would have direct access to
the existing sql server database as though they were on the LAN. The
problem is that setting up VPN in our remote offices is not completely
compatible with our IT department's goals or policies. (I'm a
developer, not a member of the IT team.) The possibility of installing
a new server (located inside the dmz) was mentioned above only as the
painful alternative to having no other way to connect directly to our
sql server database (as a source of readOnly data) through the
internet, barring the rewriting of the Access app to use other
technologies such as xml.

Here's the thing...my application is in transition between Access and
my web app, and I need to buy some time. The only part of the Access
app that needs access to the db right now are the reports. (The data
itself is now entered and edited online through my web app.) Later
this fall the reports will be integrated into the web app, and I'll be
able to scrap Access altogether. At that point I'll be discarding any
work I do to supply data to these Access reports during the transition,
so I want to keep it to a minimum and spend most of my time building
permanent things.

As far as security goes, users already have to log in to the web app
through Integrated Windows Authentication(IIS6) + Active Directory +
SSL, and they have read/write permissions. From there, access to sql
server is based on trusted accounts, and is carefully monitored by
myself and IT. So, I reason that I would have at least that much
security going for me if I could pump readOnly data back to the
logged-in Access app in the remote offices using the web server.

I'm looking for a solution (that doesn't breach anyone's security
policies) such as:

Using the web server as a proxy through an odbc connection.

Or possibly opening a connection through .NET on the web server.

Here's another idea...from the remote Access app...can I link to sql
pass-through SELECT queries in an Access mdb on the web server?

I'm just trying to use my head(and yours) before I invest in something
that will be thrown away soon. It hurts when I see someone spend
thousand$ to solve problems like this with "obvious" solutions, and
then find something some creative geek did that would have saved a lot
of time and money. It's even better when users and IT don't even know
something changed, and wouldn't care anyway! Work is more fun when
you're really as clever as you like people to think you are.

Thanks for your interest.

Bob

[Erland Sommarskog]

(bc******@bmghomes.com) writes:

The problem is that setting up VPN in our remote offices is not
completely compatible with our IT department's goals or policies.
OK. If your IT department does not like VPN, they do ot like VPN.
Here's another idea...from the remote Access app...can I link to sql
pass-through SELECT queries in an Access mdb on the web server?

Don't ask me. I don't know Access, I don't know web servers.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[bcanavan]

Thanks, Erland.

I didn't mean to burden you with it personally. I thought I was in a
forum.

Thanks for your interest, though.

Bob

[Erland Sommarskog]

(bc******@bmghomes.com) writes:

I didn't mean to burden you with it personally. I thought I was in a
forum.

Yeah, you are. :-) But since I've been about the only person answering
you, I only wanted to make it clear that I have nothing to add.

And there was kind of a hint: try a forum where they know IIS and/or
Access.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp