August 16, 2007 at 11:10 am
We have a few clients that needs to access our SQL database every so often. Currently we're using an IP security policy to block access to port 1433 except from permitted IP addresses. The only problem is, some of the clients do not have static IP addresses, so we're constantly having to update the IP address in the policy for them. I have given up trying to get them to tell me their IP address, so I have them e-mail me when they need access and I pull their IP address our of the e-mail headers.
Is there a better way?
I'd prefer to e-mail the clients an SSL certificate that they can install on their machines, then trust that certificate for access. Can this be done without a lot of hassle? I tried to find a way to do it in the Local Security Policy console, but couldn't figure out how.
Some of our clients use SQL Server Management Studio for their remote connection. Others use Access or Excel. For the most part all they are doing is opening their program and running a particular stored procedure which we have set up for them. The data they retrieve is confidential, though not top secret. I'm not really concerned about encryption so much as authentication. I want to keep everyone off of our server that doesn't have permission to be using it, but I'm not terribly concerned about those who do have permission gaining access to info they shouldn't have. They all have access to their data in other, less efficient, ways. Having said that, encryption would still be nice if it's not difficult to implement.
My goal is to give these select individuals who don't have a static IP address an alternative way of using their ODBC connection (or SQL Server Management Studio) to get their data while at the same time blocking port 1433 from remote unauthenticated access. I cannot seem to find any easy way to do this. The only solution I've found is to set up a VPN and that's more than what some of these people can handle. Just opening their Excel spreadsheet and clicking on "Run" is challenging enough for them.
--Doug
August 23, 2007 at 8:19 am
This is just one possible solution, but would you be able to make them register with a dynamic dns service (something like DynDNS http://www.dyndns.com/services/dns/dyndns/). This would allow to identify your clients by a specific hostname, and that hostname is updated constantly and automatically by your clients to resolve to their latest IP address. This could of course create a new security threat.
Regards,
Andras
August 24, 2007 at 8:03 am
Another alternate solution might be to use SQL security. You could set the users up one time with SQL logins, set them up as users with the appropriate security in the database(s) and then have them login using their "SQL password" to the server.
The ODBC connection might come up saying that they are not a valid user, but they can uncheck the "Trusted Security" box and then enter their SQL userid and password.
Just a thought,
August 24, 2007 at 9:04 am
Andras,
Thank you for the thoughts. I am not as well-versed in DDNS as I'd like to be, but based on my understanding, I'm not sure if this wouldn't still be a problem. I can see where it would certainly reduce hacking attempts, but I'd have to explore that further. It might narrow the hole, but I don't know if it would close it completely. As well, those of our users that don't have static IP addresses probably would be clueless in setting up DDNS. My real goal is to require a user certificate to get through the firewall. It seems like this should be a no-brainer, but I just can't figure it out.
Carla,
Thank you for your reply as well. Our users do already have their own SQL logins. It's preventing unauthorized users from even seeing the SQL server while allowing legitimate users to get in that I'm trying to accomplish. We firewalled our server a couple of years ago after finding 1,000's of hacking attempts each day for over a week. Someone was trying to use brute force to guess our SA password, which is fortunately a strong one. So now the ports are closed unless a static IP address matches our permitted list. I'd just rather use user certificates for authentication. That would, I hope, solve the issue.
Thanks again folks.
--Doug
August 24, 2007 at 9:30 pm
Talk to your firewall folks about setting up an IPSec connection between your offices and the remote users. Depending on the settings of the IPSec connection a changing IP may or may not be a problem.
Joe
August 27, 2007 at 8:47 am
I would agree with Andras. You can use IPsec and define a hostname. If the SQL can handle the resolution, you may be able to substitute the fqdn of a remote user's computer for the static ip address. You may also trying having the remote users request a static ip address from their ISP (I'm assuming these remote users are not joined to a domain, which is why the fqdn substitution in the IPsec policy will probably fail).
August 27, 2007 at 10:15 am
One more solution to throw into the mix: Remove SQL server from direct I-net access, but allow access through VPN connections. There are some very good VPN appliances out there that do Cert based security and encrypt the connection to boot.
Pro:
SQL Server no longer accessable on the internet so hacking, etc. is not a problem and securing the server becomes easier.
VPN appliance provides strong cert-based user authentication and session encryption.
Con:
Cost and setup of VPN appliance.
Steve G.
August 27, 2007 at 11:52 am
Thank you everyone for your responses. I am going to look further into the DDNS solution if I can't find a better way first as it'd be the easiest to implement on my side. If I can be comfortable in that solution being secure, then I'll go with it. Otherwise, I am not sure I'd like to go the VPN route, but may have no choice.
Your responses, however, did lead me to an article at http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx that may be the solution to my problem. I'm going to check that out before I do anything else.
--Doug
September 2, 2007 at 12:45 pm
I'm securing similar access using an IPSEC VPN. I send out the VPN client with a policy they import--no configuration required by them. All the client needs to do is install the client, load the vpn client software and connect.
The only issue with VPN is with the clients. Most VPN software recommend that they are the only IPSEC / IKE software on that machine. So, in a few cases, it may take an uninstall/reinstall process, instead of just installing the new VPN software--which usually works, even when there's other VPN clients installed--like one of my machines where I use 3 different clients.
Additionally, most firewalls do support ddns for vpn-to-vpn connections. Though, I think you could probably use it to update your IP restrictions, e.g. they support this bec not everyone will afford to have a static IP.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply