Open up SQL Server for central server application...

[Ray Lavelle]

I have an application which runs in many different stores. Stores
that are within a chain want to be able to share their client lists.
I want to implement a sync process where each application would ping a
central server every 5-10 mins to check for changes made by any other
store to the client list, if any changes are made it would download
the new or modified clients then upload any clients that were modified
localy. I want the stores to ping for changes, I don't want to try to
implement some kind of real time system where there changes would
always be stored directly on the server as they make them.
My application uses MSDE so I was thinking about just throwing MSDE on
a central server that all the other computers could access over an
internet connection and keeping a master client list in this database.
The applications in each store would connect directy to MSDE to send
and get the data needed. My question is, how secure would it be to
open up my sql server port to the internet so each store could connect
to it? Is it enough to set a user account for the database and set a
SA password? If not, what else do I need to do? Also, I know it is
possible to set the SA password to blank with a simple query, so
doesn't this mean that SQL Server would not be secure at all even if I
set a password for user SA?
How can I do this and keep it secure? Or is there a better overall
approach to doing this?
Thanks.

[Erland Sommarskog]

Ray Lavelle (bo************@yahoo.com) writes:

The applications in each store would connect directy to MSDE to send
and get the data needed. My question is, how secure would it be to
open up my sql server port to the internet so each store could connect
to it? Is it enough to set a user account for the database and set a
SA password? If not, what else do I need to do? Also, I know it is
possible to set the SA password to blank with a simple query, so
doesn't this mean that SQL Server would not be secure at all even if I
set a password for user SA?
How can I do this and keep it secure? Or is there a better overall
approach to doing this?

Putting a database on the Internet is not a walk in the park. Not the least
if you have mixed-mode authentication. Someday someone will find that
server, and try sa passwords until he gets in. There is no way to tell
SQL Server to close the doors if there have been too many failed login
attempts.

Security issues are not my best subject, but I would guess that the best
is that you set up VPN connections between the stores. Then all that
is exposed to the Internet is the VPN login, and this is least safe against
brute force attacks. This also permits the stores to be in the same
domain, so that you can use Windows authentication, and turn of SQL
authentication.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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