The SQL Server Security Plan

It should be clear to you now that SQL Server 2005 security is no simple matter. No matter how small or large your project might be, just blindly attacking logins, roles, permissions, users, and so on is a futile exercise that, while it seems easy enough in the beginning, can lead to certain chaos if not based on a solid security plan.

The architecture of SQL Server 2005 warrants that you divide the plan into two sections. One section deals with DBMS security, and the second section deals with data security. Splitting the plan according to the two priorities like this makes it easier to manage a complex security environment, to “divide and conquer,” so to speak.

DBMS Security Plan

Locking down a DBMS is not an easy thing to do when you need to make sure that your authorized users do not have to walk the length of the Great Wall of China just to log in. Many applications typically require a user to first sign into the local area network and then again into the database. This means that you are forcing the user to remember two passwords. Such a scenario often leads to blank passwords or passwords that get exposed on sticky notes attached to monitors and so forth.

You will thus save yourself-the SQL Server security administrator-a lot of hardship by relying on the security mechanisms available to you in the external environment. The following items should be considered in the formulation of your own DBMS security strategy:

• Upgrade to Windows Server 2003

• Use Kerberos security and discontinue NTLM security

• Deploy IPsec and SSL in high-security environments

• Use middle-tier or proxy services

• Create SQL Server login groups

• Use group policy with organizational units

• Formulate server role assignment policy

Database Security Plan

Your database security plan requires you to identify all elements in the internal environment of the DBMS that can be mustered to protect data from theft and destruction. You will also need to formulate or recognize business needs that dictate that some data, or aspects of data, need to be made available only to workers that need it to perform their jobs.

Some aspects of data security can be achieved programmatically Other aspects can be achieved by using the tools provided by Management Studio or using the administrative APIs, such as SQL-SMO. The following items must be considered in devising your database security plan:

• Formulate database DBA role assignment policy.

• Create user-defined database roles to protect database objects.

• Identify columns within a database that share similar security requirements.

• Identify logins that share similar security requirements.