SQL Server over the Web, what are my options?

SQL Server DB is at location A
IIS Server with the website is at location B

The website at location B needs to be able to run sql queries against the DB
at location A over the internet.

The options I am considering are:

1) VPN between A and B. What are the pros and cons?

2) On the firewall at B, forwarding traffic from A to SQL server. Would use
SSL encryption for the DB connection but still not sure this is secure enough.

Do those sound like valid secure options?
Are there other options I should be considering.

Thanks

--
Darren Jensen

Matt
Tue Feb 01 13:52:36 CST 2005

Darren,

I'd personally go with the firewall, and ONLY allow port 1433 over that
connection. SSL encryption is perfectly fine.

The problem occurs when/if your webserver is hacked (or vise-versa). The
attackers then have a secure link back to your SQL server. If your SQL
server is in a DMZ, then this isn't so bad. If it's in your network, then
it's bad.

Matt Gibson - GSEC

"DarrenJ" <darrenj@classinfo.com(work)> wrote in message
news:38D37232-3CF0-430C-A02A-3008B087EAE3@microsoft.com...
>
> SQL Server DB is at location A
> IIS Server with the website is at location B
>
> The website at location B needs to be able to run sql queries against the
> DB
> at location A over the internet.
>
> The options I am considering are:
>
> 1) VPN between A and B. What are the pros and cons?
>
> 2) On the firewall at B, forwarding traffic from A to SQL server. Would
> use
> SSL encryption for the DB connection but still not sure this is secure
> enough.
>
> Do those sound like valid secure options?
> Are there other options I should be considering.
>
> Thanks
>
> --
> Darren Jensen

Massimo
Tue Feb 01 14:40:19 CST 2005

"DarrenJ" <darrenj@classinfo.com(work)> ha scritto nel messaggio
news:38D37232-3CF0-430C-A02A-3008B087EAE3@microsoft.com...

> SQL Server DB is at location A
> IIS Server with the website is at location B
>
> The website at location B needs to be able to run sql queries
> against the DB at location A over the internet.
>
> The options I am considering are:
>
> 1) VPN between A and B. What are the pros and cons?

Good solution, very secure (as long as you use L2TP instead of PPTP, of
course). You may need a couple of additional routers, or you can do with
existing ones, if your existing routers support it.

> 2) On the firewall at B, forwarding traffic from A to SQL server.
> Would use SSL encryption for the DB connection but still not
> sure this is secure enough.

If you allow only traffic from A to B, that could be ok too.

> Do those sound like valid secure options?

Yes, but I think your main concernsshould be about network speed and
availability. How long will a query take against a remote server across the
Internet? What will happen if, for some reasons, the two servers can't talk?

> Are there other options I should be considering.

You could put another SQL Server at location B and have the two replicate,
using VPN or firewall-based port forwarding. This will be as secure as your
options, but a lot more fast and reliable.

Massimo

darrenj
Tue Feb 01 15:05:03 CST 2005

Thanks for the response.
Please see below for comments

"Massimo" wrote:

> "DarrenJ" <darrenj@classinfo.com(work)> ha scritto nel messaggio
> news:38D37232-3CF0-430C-A02A-3008B087EAE3@microsoft.com...
>
> > SQL Server DB is at location A
> > IIS Server with the website is at location B
> >
> > The website at location B needs to be able to run sql queries
> > against the DB at location A over the internet.
> >
> > The options I am considering are:
> >
> > 1) VPN between A and B. What are the pros and cons?
>
> Good solution, very secure (as long as you use L2TP instead of PPTP, of
> course). You may need a couple of additional routers, or you can do with
> existing ones, if your existing routers support it.
>
> > 2) On the firewall at B, forwarding traffic from A to SQL server.
> > Would use SSL encryption for the DB connection but still not
> > sure this is secure enough.
>
> If you allow only traffic from A to B, that could be ok too.
>
> > Do those sound like valid secure options?
>
> Yes, but I think your main concernsshould be about network speed and
> availability. How long will a query take against a remote server across the
> Internet? What will happen if, for some reasons, the two servers can't talk?

Speed is a concern of course. If the servers can't talk then the site is down.

>
> > Are there other options I should be considering.
>
> You could put another SQL Server at location B and have the two replicate,
> using VPN or firewall-based port forwarding. This will be as secure as your
> options, but a lot more fast and reliable.

The problem is that the DB is very large and there will be lots of updates
but the web site only cares about a few of the table, a small fraction of the
total number of tables. So it looks like replication would result in a lot
more network traffic over all. Also the tables need to be accessed real time,
we can't stale information so if the replication can't take place then the
site is down anyway ...

>
> Massimo
>
>

Massimo
Tue Feb 01 15:24:30 CST 2005

"DarrenJ" <darrenj@classinfo.com(work)> ha scritto nel messaggio
news:6D9094BC-9CEE-4796-8E81-F341202BA5E8@microsoft.com...


>> > Do those sound like valid secure options?
>>
>> Yes, but I think your main concernsshould be about network speed and
>> availability. How long will a query take against a remote server across
>> the
>> Internet? What will happen if, for some reasons, the two servers can't
>> talk?
>
> Speed is a concern of course. If the servers can't talk then the site is
> down.

Both problems are easily solved by two replicating DBs.

>> > Are there other options I should be considering.
>>
>> You could put another SQL Server at location B and have the two
>> replicate,
>> using VPN or firewall-based port forwarding. This will be as secure as
>> your
>> options, but a lot more fast and reliable.
>
> The problem is that the DB is very large

That's not a problem, you'll have to fully replicate it only the first time.
You can use ordinary means for this (i.e. sending a full backup to the
remote location).

> and there will be lots of updates

These will be automatically handled by the replication. Even if they really
generate a lot of traffic, that would be roughly equivalent to that
generated by queries (unless you always do "ALTER TABLE" to every row in
100K-columns tables, but I don't think you'll do that so often).

> but the web site only cares about a few of the table, a small fraction of
> the
> total number of tables. So it looks like replication would result in a lot
> more network traffic over all.

Wrong. There are ways to replicate only the portion of the DB you care
about, SQL Server replication handles this greatly.

> Also the tables need to be accessed real time, we can't stale information

Replication can be scheduled to run almost continuously using very short
time intervals, or even to be totally continuous.

> so if the replication can't take place then the site is down anyway ...

Yes, but when everything works it'll be *a lot* faster to query a local DB
than a remote one.

Massimo