SQL 2005 secure external access?

[kokaina]

Hello, I just start to working with a client and he need a secure external access to his SQL server. This server will have a direct IP and hostname ( sql.domanin.com because need to be access with a application.
I'm new with SQL technology and I don't know how to do this.
There is in SQL some configuration about specific IP address that can access server? or what is the best way to secure external access?

 

[mrdenny]

The best way is to prevent it.

Are they building a new application or is this an existing application?

If it's an existing app, the most secure option would be to setup a VPN from the remote machines into the network, then use the app over the VPN.

If it's a new app, then the design should be changed so that the app sends calls via web methods on an web server protected by SSL, then the web methods log into the database and do what needs to be done.

SQL Server responds to all connection requests made to it. There's no way to tell SQL that only specific subnets are allows to talk to it.

If you must allow SQL to be accessed from the Internet, setup a seperate ENDPOINT which only the logon which the app will be using can log into. Then setup the firewall on the router to allow only the subnet or subnets which need to use the application to have access to the SQL Server.

If you have to allow the entire Internet access to the SQL Server then you need to have them redesign the app as a SQL Server which is open to the entire Internet will eventually be hacked into, it's just a matter of time.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[deepsheep]

We set something like Denny suggested and it works quite well.

We have a firewall that requires an SSL connection with either a username/password or a specific IP. No login,no SSL, no access, so random connections can't find SQL. On good login, it then routes the traffic to the SQL server required for the application. You also need to log into SQL as usual.

Setup was a pain, but worth it (at least for our application) when you weigh the options. However, over 90% of data access is still done without direct database access, it's just one small chunk of the overall solution that required it.