How to write secure SQL Server–based applications
Designing secure applications is more about good planning than anything else. So before you begin that new project, think it over carefully. With application-level intrusions becoming increasingly common, you need to keep attackers from exploiting your code and circumventing your expensive firewalls and other defense systems. To write secure SQL Server—based applications, you need to know who is accessing your database, with what privileges, and what type of data they're accessing. You need to be aware of what code is making its way back to the server at every tier. With thorough planning, well-tested code, and a dash of paranoia, you won't find yourself sitting in front of the senior managers answering questions about why your code is to blame for the latest hack attack. And you might just be able to catch a few winks.
Use the Principle of Least Privilege
If someone in your office needed access to the supply closet, would you also give them the keys to the executive offices and the petty cash drawer? I doubt it. So why do so many SQL Server applications use the sa account or the system administrator role for everyday connectivity? I blame laziness. Using sa or a sysadmin-level account means developers need never worry about who has which permissions or rights to perform the tasks inherent in any application. But you don't have to give up; you can give out the right permissions and have good security, too.
When you're developing a new application, think about what rights the application will need at runtime. Using administrative privileges temporarily at installation time is OK because the typical application needs to create the databases, tables, and other objects that the application needs. However, after installation, the application shouldn't require such broad rights to perform operational tasks. You need to take the time during installation to prompt the user for (or create) a runtime account and a password so that the application can run with a lower level of privilege.
After you create the user account, create a script that will let the user access the necessary database objects. Listing 1 contains a sample script that you might execute during installation to set up a low-privilege account and give it minimal rights. This script adds a new login, creates the user account, adds the user to a role, and assigns permissions to some database objects. For demonstration purposes, I added the user to a database role, but the average user probably won't need this level of privilege.
With the integrated security model, you don't need to create a SQL Server login; you just grant SQL Server access to an existing Windows account. By using integrated security, you can avoid using mixed security mode, and you can eliminate the password required for SQL Server authentication and the dangers that come with storing the password. By dangers, I mean that when (notice I didn't say if) the latest Microsoft IIS source-code disclosure vulnerability hits, your SQL Server password won't just be sitting in global.asa (or wherever you put it), waiting to be plundered. If attackers can connect directly to your SQL Server, they can easily use the username-password pair from the connection string to go straight for the database. Integrated security lets the existing IIS user context access SQL Server, thus removing the application's dependency on a SQL Server account to perform necessary operations.
In an integrated model, you could modify the account-creation script to look like this sample to keep from having to store a SQL Server password in a connection string:
/* Create login in master.dbo.syslogins. */
EXEC sp_grantlogin N'SEAHAG\IUSR_SEAHAG'
GO
EXEC sp_defaultdb N'SEAHAG\IUSR_SEAHAG', N'pubs'
GO
Many security-savvy administrators prefer this approach to the SQL Server account approach for several reasons, including centralized account administration, a more robust password system, and account-lockout capabilities.
Remember that the sa account or another login with sysadmin privileges has total control over SQL Server and can also perform OS tasks within the security context of the account under which SQL Server is running. For most installations, these extensive privileges mean that a SQL Server administrator can become LocalSystem, a local administrator, or even a domain administrator.
Also, don't think that making the user a member of the db_owner SQL Server database role is a safe shortcut. Keeping privileges low is like having an insurance policy. Even if you fail to secure other areas of the application, you won't inadvertently give an attacker enough power to destroy all you've built. A malicious user with db_owner privileges can drop tables and read and modify all the data in your database.
Prev. page
[1]
2
3
4
next page 
