December 4, 2003 at 9:04 am
We are going to open up one of our servers to the Web so that we can capture information that users enter
from a form, and to also display these results.
I have set up a WebSightings user with rights to execute a stored procedure that I wrote for inserting data. Also they have select rights to the lookup tables and also to a view I created to display the results, and that is all.
I have removed BuiltIn/Administrators from the users.
I was wondering what else I can do to make sure that it is as secure as possible, I am really paranoid about someone hacking in, and am new to the security side of Sql server
December 4, 2003 at 9:07 am
I'm sure Brian Kelley will also respond. In the meantime take a look at http://www.sqlsecurity.com. Especially at the featured links on the homepage. They lead to some interesting white papers on injection..
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 4, 2003 at 12:10 pm
Experience says that if you open the database to the web someone will get in. It's a very big door and there are some very talented people out there.
Bonne Chance.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
December 4, 2003 at 1:31 pm
Sure thing that a skilled person might get access this way or another, but you can prevent the vast majority of part-time crackers and hackers from trying. And that's better than nothing, or?
Anyway, SQL Server security can be as good as it can get, when someone gains access over the webserver and/or the OS, what is SQL security worth anymore?
I think this should even be more of concern.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 4, 2003 at 1:58 pm
Okay, let's take this step-by-step...
Removing the BUILTIN\Administrators, while a good idea, doesn't help your SQL Server security with respect to the web. The reason being a connection from your web server is likely not coming with Windows authentication and even if it is, it's not likely coming with an account that happens to be an administrator on the box. So while this is typically a good practice, realize that so far as hardening your server from the web, it doesn't do a whole lot. The next few steps you've taken do.
You've created an account and restricted its rights to execute a stored procedure so far as the INSERT is concerned. That's good. However, unless you cannot determine the parameters of the queries against the tables and views, you want to force any queries through stored procedures there as well. One of the main reasons comes from a programming perspective...
Data access shouldn't be done using a Connection.Execute or a Recordset.Open in order to retrieve the recordset back from the SQL Server. Rather, You want to try and shoot everything through a Command object (ADODB.Command is the object type if you're coding). You can specify parameters for the command object and that goes a long way towards helping with the input validation problem leading to SQL Injection. Still, good input validation should be performed even with the Command object.
That brings up the next main point... the app itself needs to be coded securely. Using things like UNION or semi-colons, an attacker could use SQL Injection. There are numerous papers out there on how to perform such an attack, NGSSoftware is probably a good place to start:
http://www.nextgenss.com/research.html
Patrick brings up a good point and that is expect a penetration at some point. This is especially true if the connection string is unencrypted on the server somewhere (such as in global.asa)... when an attacker gains control of the web server (always plan for when, not if), then they'll use the web server against you. With that said, a savvy attacker will simply modify the web pages against you, meaning if you have other data on the system, assess the risk.
This is all pretty high-level stuff but basically restrict the web user account to the absolute minimum privileges it needs on the SQL Server, try to keep sensitive data off of that SQL Server, and make sure the web app follows best practices for both secure coding and server hardening. Those are big steps in the right direction.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 5, 2003 at 2:23 am
We tend to keep our IIS Servers on separate boxes to SQL Server.
We remove PUBLIC permissions to virtually all the stored procedures in the MASTER database because by default any logged on user can run these.
SysAdmins still have access, but not the general user population.
For web sites all access to the databases in question is via stored procedures. If we are using ADO then we use the command object.
The file containing the connection string is stored in a file outside of the web site and read via a DLL. This means that the website has no direct access to the connection string because as far as the website is concerned the file containing it doesn't exist.
We tend to put most of the site functionality into DLL's in any case and restrict the ASP pages to calling methods within the DLL's.
Not only is this faster in execution, but it is a damn site easier to debug a "proper" language than ASP. I realise that the previous statement is probably obsolete with .NET but my company doesn't use .NET.
He was not wholly unware of the potential lack of insignificance.
December 5, 2003 at 8:29 am
It also needs to be pointed out the compiled code makes it harder for an attacker to investigate the data access. An attacker would have to grab the DLL and disassemble and not every attacker has the capability of reading and understanding Assembly, though your good ones will.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 5, 2003 at 11:21 am
Let me add another vote for isolating the data access through DLLs. Do not let any ASP code (or any client application) ever touch the database directly. Doing this can be as simple as having the DLL retrieve and use disconnected ADO recordset. If you have already written the site, just modifying it to use VB DLLs can usually be easily done. Also, by using ADO recordsets, you eliminate the possibility of SQL Injection attacks mentioned above.
Mark
December 5, 2003 at 11:34 am
Also be sure that your passwords are secure and give sa a very strong password.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
December 6, 2003 at 7:05 am
I'll second the vote for no direct data access from any app. This is a huge thing, not just for security but for being able to test and reuse code. If you're dealing with customers that access your intranet, eventually you'll get one that requires this as part of their security policy and you could end up doing a lot of rewriting. The nice part about the dll is you can actually move it to another box and move some of the workload, using either DCOM, .Net Remoting, or web services as your preference/tools indicate.
Andy
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply