Access to SQL over web

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

If I have a access frontend, can i link to a SQL Serer that resides at an
ISP? If a regular LAN-based sql server only requires an IP and password,
would there be any difference?
 
Hi Scott,

You need some ports opened at least 1433 tcp and udp you may require more,
this is not a good idea having a SQL server exposed on the internet without
it being properly secured, do you need continuous access or do you need to
connect get some data disconnect, because if you don't need continuous
access the best way would create a vpn tunnel between you and the SQL server
across the internet do the work, disconnect the vpn. Security is your
biggest problem here.
 
Alex White MCDBA MCSE said:
Hi Scott,

You need some ports opened at least 1433 tcp and udp you may require more,
this is not a good idea having a SQL server exposed on the internet
without it being properly secured, do you need continuous access or do you
need to connect get some data disconnect, because if you don't need
continuous access the best way would create a vpn tunnel between you and
the SQL server across the internet do the work, disconnect the vpn.
Security is your biggest problem here.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk
Click to expand...



I had an account with fasthosts.co.uk which included a SQL Server database.
I could create Access applications (mdb or adp) on my desktop which allowed
me to update the database easily. All I needed was the ip address and
username and password - and I had no possibility to allow/disallow this form
of connection.
I looked at this vpn business and still couldn't understand the basics -
that is:
Does vpn necsarily involve extra hardware? As I understood it, not - you
can establish a vpn using Windows XP. If that is the case, how is it more
secure since at the end of the day, you need a username and password to
connect.
Whenever I looked at companies which were offering vpn setup - it always
seemed to involve a big black box, client software, client licencing and
sometimes additional little number-generating-thingies. Do you know much
about vpn?
 
Hi Justin,

VPN comes in several flavours, e.g. software,Hardware then pptp (non
certificates), L2TP (with certificates), if your hosting company was running
either Windows 2000 or 2003 server, then setting up RRAS (the technology
that allows for VPN connections). At a very basic level you do not need any
hardware other than the Windows 2000/3 server an internet connection and
Windows 2000 pro/server Windows XP at the client end. Software level vpn
implemented on MS software uses either Windows authentication or RADUIS
(what all the DSL providers use to authenticate their DSL users).

It is true to say that L2TP that uses certificates as part of it's security
is much better than PPTP in terms of security, but is much harder to setup.
I understand VPN well enough to do my job, which includes connecting to 100+
servers every single day via VPN to support and review them and to then
connect to PC's local on those networks.

VPN does come with problems, like you have unhindered access to the remote
network, and if you had a virus it is very easy for that virus to travel
through the tunnel unhindered. Given my last comment if all your remote
computers are running Windows XP SP2, with a good virus product then this is
a good solution.

Software VPN's do solve a lot of business problems, there are
advantages/disadvantages of both hardware and software VPN's, but to move
data sync'ing with a SQL server, Access MDB the idea does work, I have
several clients who I have custom written VPN connection software bound into
access within a module to auto dial and sync data.

One of the major problems of exposing a SQL server directly on the web is
this what type of security is going to be used, SQL logins/Windows
Authentication. With SQL login's there are no lockouts based on getting the
password wrong x times, Windows Authentication, it means configuring users
on that server for each independent user.
 
If I run SQL on non-default port, have strong password, can i not sql server
securely? i'd like to run it like an isp.

i tried once before without changing port and did get hacked. Any
suggestions on successfully running an "open" sql server?
 
Hi Scott,

The strongest solution is to use windows authentication, changing the port,
will slow down a would-be hacker by minutes if he/she is port scanning you,
at least with Windows Authentication you have account lockouts, that will
slow down dictionary attacks, if you set your account lockouts to be
something like lockout for 5 minutes on every 5 bad login attempts this will
make the situation far more secure. Never use SQL login's across the
internet it is far to easy to sniff packets and get the username and
password within the connect string.
 
scott said:
If I run SQL on non-default port, have strong password, can i not sql
server securely? i'd like to run it like an isp.

i tried once before without changing port and did get hacked. Any
suggestions on successfully running an "open" sql server?
Click to expand...

Alex has given you some great advice here. (I can't really add anything more
to his advice!).

It is really up to you if you want to in fact run sql server that "open" to
the net. if the data on the server is not valuable, then I might risk it.
other wise, I would lock things up...and use a VPN. You can also look into
use a ssl....

"hoping" that someone does not find the port is little consultation if the
data is important. I mean, how valuable is the data, and how bad are the
consequences if someone tries to steal the data? Only YOU can answer this.

In the case of the internet, and clients data...I suggest you go all the way
in terms of security..as that might not be enough in some cases!!
 
What Windows security account does SQL use in trusted environment? Do I
create a new special Win account for SQL to use or does it use IUSR account?
 
What ever accounts you setup,

I will explain a bit about how windows security works on remote servers
(anything other than the computers on the same domain as your workstation),
you have already logged into windows it may be Windows 2000 / Windows XP
etc, you either logged into the local account on the workstation or you
logged into a domain account. When you hit another server (MS Windows only)
(this maybe across the internet) the first thing that happens is the login
details that you logged into your local workstation are used to attempt to
login so say for instance you logged in with the username of Scott and a
password of letmein, that username and password will be attempted on the
remote server, if this fails you get the famous username and password box
appears, this is because the remote server does not know who you are.

So what you may setup is this, on the remote server create a username of
Scott and a password of letmein (this is an example, for internet facing
computers nice long passwords with complex rules like %R1q@qwe45!qw would be
considered a pretty good password), in the SQL server setup the security for
the user scott allowing access to the specific tables that you need do not
give sysadmin permissions just because it is easy to do). Make sure on the
remote server you have set account lockout for x bad attempt and to auto
reset the account after say 5 minutes, otherwise you may spend half your
life unlocking accounts. Put a very secure password on the SA account and
never use it across the internet, as it will sail across the internet in
plain text. The most secure would be use a VPN connection to that server it
will also give you the most flexibility.

Within WebPages you should never specify the username and password in the
connect string, I change the IIS account to an account that I setup with a
password I know, set the anonymous account to that, setup that user with the
correct permissions in the SQL server.

e.g.

connectionString="Server=IS2;Integrated Security=SSPI;Database=IntraLAN"

the above works perfectly and you are not giving away any security
information.
 
Alex White MCDBA MCSE said:
Hi Justin,

VPN comes in several flavours, e.g. software,Hardware then pptp (non
certificates), L2TP (with certificates), if your hosting company was
running either Windows 2000 or 2003 server, then setting up RRAS (the
technology that allows for VPN connections). At a very basic level you do
not need any hardware other than the Windows 2000/3 server an internet
connection and Windows 2000 pro/server Windows XP at the client end.
Software level vpn implemented on MS software uses either Windows
authentication or RADUIS (what all the DSL providers use to authenticate
their DSL users).

It is true to say that L2TP that uses certificates as part of it's
security is much better than PPTP in terms of security, but is much harder
to setup.
I understand VPN well enough to do my job, which includes connecting to
100+ servers every single day via VPN to support and review them and to
then connect to PC's local on those networks.

VPN does come with problems, like you have unhindered access to the remote
network, and if you had a virus it is very easy for that virus to travel
through the tunnel unhindered. Given my last comment if all your remote
computers are running Windows XP SP2, with a good virus product then this
is a good solution.

Software VPN's do solve a lot of business problems, there are
advantages/disadvantages of both hardware and software VPN's, but to move
data sync'ing with a SQL server, Access MDB the idea does work, I have
several clients who I have custom written VPN connection software bound
into access within a module to auto dial and sync data.

One of the major problems of exposing a SQL server directly on the web is
this what type of security is going to be used, SQL logins/Windows
Authentication. With SQL login's there are no lockouts based on getting
the password wrong x times, Windows Authentication, it means configuring
users on that server for each independent user.


--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk
Click to expand...

Thanks for that. I wonder whether other hosting companies allow access to
their SQL databases directly across the internet. Whenever these hosting
companies mention SQL Server databases - this is one aspect they never seem
to specify. Certainly fasthosts.co.uk did allow this and it mad it pretty
handy for me and since the data was not sensitive and was only a published
copy of our lan data, I didn't really have concerns. I guess other hosting
companies must do similar things, but have not done much shopping around.
 
I can do this for you, take this conversation offline,
I am in the UK and my office number is 02084017000 or use my email address
to email me.
 
Back
Top