Common SQL Server Security Issues and Solutions
Nice article by Paul Randal about security issues with SQL Server.
Some pithy excerpts:
"...A less obvious consideration is the security of the desktops of the people who have high-privileged access to SQL Server. If someone has sysadmin access to SQL Server but they leave their Windows desktop unlocked, then all the security in the world isn't going to prevent someone walking past the unattended system from potentially accessing sensitive data. A more insidious problem would be if someone walked past and changed some data-for instance, a dishonest employee who knows the schema of the human resources database and tries to undetectably change a salary..."
"...As such, a SQL Server service account should not be a high-privileged account (such as Local System or Local Administrator) because if SQL Server is compromised, there is the potential for the Windows system to also be compromised. The services typically run under a built-in account called Network Service, but if SQL Server requires access to other domain resources, a new domain user account should be created with the minimum privileges and resource accesses required. The SQL Server 2008 Books Online topic "Setting Up Windows Service Accounts" provides a comprehensive list of service accounts, required privileges, and resources. Note that if you must change a service account (or anything about it), you should always use the SQL Server Configuration Manager to ensure that all the necessary configuration changes are properly made..."
"...All of this can be accomplished within SQL Server by a comprehensive, hierarchical permissions system where users or roles (called principals) are granted or denied certain specific permissions on certain resources (called securables) such as an object, schema, or database. An overview of the SQL Server permissions hierarchy is illustrated in Figure 4. This also implies that you follow the principle of least privilege. For example, don't make all database developers members of the db_owner role. Restrict permissions to the public role and only grant permissions to the lowest level (user or role) to minimize direct access. A full discussion of best practices for permissions is beyond the scope of this article, but SQL Server 2008 Books Online includes a section called "Identity and Access Control (Database Engine)" that offers drill-downs into all the concepts...."
Now read the entire article over at:
Thomas W Shinder, M.D., MCSE
Sr. Consultant / Technical Writer