As developers, we are more often than not caught violating some of the most critical Best Practices for securing the data we pump into databases. If you are finally sick of getting yelled at by the DBAs (Database Administrators) at work, maybe you should check out the following list of SQL Server best practices (BP) I have compiled from various BP Whitepapers.
Nonetheless, if you are a DBA and you haven’t cultivated the habit of yelling at your developers for violating these rules, maybe this is more down your alley! Focus!
Best practices for surface area reduction
• Install only those components that you will immediately use. Additional components can always be installed as needed.
• Enable only the optional features that you will immediately use.
• Review optional feature usage before doing an in-place upgrade and disable unneeded features either before or after the upgrade.
• Develop a policy with respect to permitted network connectivity choices. Use SQL Server Surface Area Configuration to standardize this policy.
• Develop a policy for the usage of optional features. Use SQL Server Surface Area Configuration to standardize optional feature enabling. Document any exceptions to the policy on a per-instance basis.
• Turn off unneeded services by setting the service to either Manual startup or Disabled.
Best practices for SQL Server service accounts
• Use a specific user account or domain account rather than a shared account for SQL Server services.
• Use a separate account for each service.
• Do not give any special privileges to the SQL Server service account; they will be assigned by group membership.
• Manage privileges through the SQL Server supplied group account rather than through individual service user accounts.
• Always use SQL Server Configuration Manager to change service accounts.
• Change the service account password at regular intervals.
• Use CREDENTIALs to execute job steps that require specific privileges rather than adjusting the privilege to the SQL Server Agent service account.
• If an agent user needs to execute a job that requires different Windows credentials, assign them a proxy account that has just enough permissions to get the task done.
Best practices for authentication mode
• Always use Windows Authentication mode if possible.
• Use Mixed Mode Authentication only for legacy applications and non-Windows users.
• Use the standard login DDL statements instead of the compatibility system procedures.
• Change the sa account password to a known value if you might ever need to use it. Always use a strong password for the “sa” account and change the “sa” account password periodically.*
• Do not manage SQL Server by using the sa login account; assign sysadmin privilege to a known user or group.
• Rename the “sa” account to a different account name to prevent attacks on the “sa” account by name.*
Best practices for network connectivity
• Limit the network protocols supported.
• Do not enable network protocols unless they are needed.
• Do not expose a server that is running SQL Server to the public Internet.*
• Configure named instances of SQL Server to use specific port assignments for TCP/IP rather than dynamic ports.
• If you must support SQL logins, install an SSL certificate from a trusted certificate authority rather than using SQL Server 2005 self-signed certificates.
• Use “allow only encrypted connections” only if needed for end-to-end encryption of sensitive sessions.
• Grant CONNECT permission only on endpoints to logins that need to use them. Explicitly deny CONNECT permission to endpoints that are not needed by users or groups.
Best practices for system stored procedures
• Disable xp_cmdshell unless it is absolutely needed.
• Disable COM components once all COM components have been converted to SQLCLR.
• Disable both mail procedures (Database Mail and SQL Mail) unless you need to send mail from SQL Server. Prefer Database Mail as soon as you can convert to it.
• Use SQL Server Surface Area Configuration to enforce a standard policy for extended procedure usage.
• Document each exception to the standard policy.
• Do not remove the system stored procedures by dropping them.
• Do not DENY all users/administrators access to the extended procedures.
Best practices for password policy
• Mandate a strong password policy, including an expiration and a complexity policy for your organization.
• If you must use SQL logins, ensure that SQL Server 2005 runs on the Windows Server 2003 operating system and use password policies.
• Outfit your applications with a mechanism to change SQL login passwords.
• Set MUST_CHANGE for new logins.
Best practices for administrator privileges
• Use administrator privileges only when needed.
• Minimize the number of administrators.
• Provision admin principals explicitly.
• Have multiple distinct administrators if more than one is needed.
• Avoid dependency on the builtin\administrators Windows group.
Best practices for database ownership and trust
• Have distinct owners for databases; not all databases should be owned by sa.
• Minimize the number of owners for each database.
• Confer trust selectively.
• Leave the Cross-Database Ownership Chaining setting off unless multiple databases are deployed at a single unit.
• Migrate usage to selective trust instead of using the TRUSTWORTHY property.
Best practices for using schemas
• Group like objects together into the same schema.
• Manage database object security by using ownership and permissions at the schema level.
• Have distinct owners for schemas.
• Not all schemas should be owned by dbo.
• Minimize the number of owners for each schema.
Best practices for database object authorization
• Encapsulate access within modules.
• Manage permissions via database roles or Windows groups.
• Use permission granularity to implement the principle of least privilege.
• Do not enable guest access.
• Use users without logins instead of application roles
Best practices for catalog security
• The catalog views are secure by default. No additional action is required to secure them.
• Grant VIEW DEFINITION selectively at the object, schema, database, or server level to grant permission to view system metadata without conferring additional permissions.
• Review legacy applications that may depend on access to system metadata when migrating the applications to SQL Server 2005.
Best practices for remote data source execution
• Phase out any remote server definitions.
• Replace remote servers with linked servers.
• Leave ad hoc queries through linked servers disabled unless they are absolutely needed.
• Use constrained delegation if pass-through authentication to a linked server is necessary.
Best practices for execution context
• Set execution context on modules explicitly rather than letting it default.
• Use EXECUTE AS instead of SETUSER.
• Use WITH NO REVERT/COOKIE instead of Application Roles.
• Consider using code signing of procedural code if a single granular additional privilege is required for the procedure.
Best practices for data encryption
• Encrypt high-value and sensitive data.
• Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.
• Password-protect keys and remove master key encryption for the most secure configuration.
• Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.
• Always back up your database to back up your symmetric and asymmetric keys.
Best practices for auditing
• Auditing is scenario-specific. Balance the need for auditing with the overhead of generating addition data.
• Audit successful logins in addition to unsuccessful logins if you store highly sensitive data.
• Audit DDL and specific server events by using trace events or event notifications.
• DML must be audited by using trace events.
• Use WMI to be alerted of emergency events.
• Enable C2 auditing or Common Criteria compliance only if required.
Best practice analysis utilities recommendations
• Run BPA (SQL Server 2005 Best Practices Analyzer) against SQL Server 2005.
• Regularly run MBSA (Microsoft Baseline Security Analyzer) 2.0 to ensure latest SQL Server 2005 patch level
• Regularly run MBSA 2.0 for SQL Server 2000 instances
Best practices for patching SQL Server
• Always stay as current as possible.
• Enable automatic updates whenever feasible but test them before applying to production systems.
For even more details check out the White Paper on “SQL Server 2005 Security Best Practices“.
Larry Galbraith is discussing. 
Larry Galbraith 9:56 pm on December 14, 2007 Permalink |
Oh, I am not a developer because I am doing mostly administrative tasks with SQL. But I can say the same about myself. I completely agree with you that it’s very important to “always use a strong password for the sa account and change the sa account password periodically” and rename it to narrow down the surface for an attacker. But the reality is that I usually have to weaken security in some cases. Sometimes it’s needed for some users, sometimes it’s needed to allow software that works with SQL to be able to work with the database. There are many reasons some of them are important, some less important but the essence is that you have to make security weaker. Sometimes. But it always hard to restore the strong security back to the proper solid state when everything is aligned across security settings for every table and every DB. Again, sometimes I have to provide the user with different access settings for different SQ L instances. And then this user has to change something somewhere and it goes further on and on. That’s how you usually get security hole in your SQL perimeter. I really appreciate when someone like you posts guides and best practices like this as it allows to get things sorted without the need to re-invent the wheel. The back side of security management in SQL is that SQL Management studio exposes just a tenth fraction of percent of tool that you have to have under your fingers. But not all is that bad. Recently I came across the tool from Scriptlogic called Security Explorer http://www.scriptlogic.com/products/security-explorer/sql-server/ that allows managing SQL security and search for security breaches for a selected server or instance remotely. The great thing to me is (keeping with the practice we’ve been talking above) that it can search for local admin accounts that have been renamed. As admin I know how important is to have a proper guide and tool at the hand.