Below is a longer version of the equivalent Technet Article under Security TechCenter > Learn > Security Columns > MVP Articles > Database Security Best Practices
The security of corporate databases and related aspects have become increasingly important as enterprises consider the data they store, purchase, or acquire as assets that are critical to operations. Consequently, those involved with responsibility and control over these resources must assume the role of stewards of the data;- otherwise, the risks to business operations involved for lack of such a pro-active attitude, could lead to commercial peril.
My interpretation on this subject of data security will consider all the issues I can reflect upon, deliberated over, have studied and blogged about over the past couple of tumultuous years, following Erasmus’ adage to leave no stone unturned. I believe the responsibility to protect data is under acknowledged by executives and management, leaving the bureaucratic obligation left in the hands of ineffectual auditors, when it is truly up to the database administrators (DBAs) principally and developers additionally, to handle data responsibly. Hence, the points I address below should enable the individuals tasked with the data security role, indirectly or directly, to straightforwardly achieve the indispensable level of responsibility, even if those imaginably at the higher ranks of the organization do not realize the direct link between proper data management (since data is a valuable asset in itself) and the organisation’s reputation.
Secure Your Data with an Effective Disaster Recovery Plan and Test It Regularly
This subject apropos an effective disaster recovery strategy can easily fill an entire book: consequently I dedicated an extensive article of over four thousand words to the topic last summer, after months of editing. The article provides a complete open-source disaster recovery plan, where readership from all sources is at almost forty thousand (40k), cumulatively from Blog sources and French translation. It will be updated shortly to include Database Mirroring as an alternative, before SQL Server 2011’s functionalities are released, in which new features will enhance High Availability greatly.
Groom Your Database of Sensitive Data to Reduce the Risk of It Being Disseminated
If you do not want those who have access to the data to be able to see all archived information—for example, human resources data that includes salary information—take the time to properly archive it. Simultaneously, however, you will want to be sure not to lose information required by regulation.
Separation of Duties with Respect to Internal Control: Maintain Compliance on DBs Used for Public Reporting
If you are a developer, it’s important to be aware of the segregation of duties within your respective organization, and to ensure that internal controls fall under the general Committee of Sponsoring Organizations (COSO) of the Treadway Commission framework, which is more explicitly defined in the Sarbanes-Oxley (SOX) Act. One of most important points of SOX compliance is the segregation, or separation, of duties, which basically means developers do not have access during production (to prevent the tendency to test there) and that specific procedures are followed when developing code before placing it in production. These procedures satisfy risk management practices under the COBIT framework (or ITIL) to avoid data being inadvertently, mistakenly, or maliciously changed due to ad hoc changes. Territorial as it may seem, it is necessary for physical DBAs to control production databases from development, and, without fail, to follow change management practices as defined by these frameworks. By following procedure and documentation by the COBIT or ITIL standards, we govern ourselves in a controlled environment, with respect to the production systems. Each task has to be validated (code walkthrough/fresh eyes) by a third person who is not writing the actual code. The database developer should not be able to execute anything in production without an independent review of the documentation/code for the work that is being performed. Typically, the role of the developer is to pass code to a DBA; however, given the cutbacks that have resulted from the current economic climate might not be readily available. If a DBA is not involved, it is important, at minimum, for a peer to conduct a code review. This ensures that the role of the developer is clearly separate.
If your organisation is in denial (worst case scenario) or lacks understanding (best case scenario) of internal controls, please remind management that databases which fall under internal controls (meaning the data used for public reporting, annual reports, et al.) are subject to the separation of duties. Furthermore, to track all changes and have recoverability, a bare minimum for a SQL Server database recovery is Bulk-Logged Recovery. Make sure to back up all your transaction logs and keep them indefinitely, which, in addition to providing a backup, will satisfy auditors (below I describe a way to read operations from the transaction log files directly).
Another point of internal control is adherence to the principle of providing the least amount of privileges, especially in production. To allow developers more access to get their work done, it is much safer to use impersonation for exceptions that require elevated privileges (see EXECUTE AS to do that temporarily). Often developers may dismiss this as “overhead” while on their path to coding glory (or out of lethargy 🙂 Please be aware, however, that DBAs must do all that is considered responsible because they are the de facto data stewards of the organization and must comply with regulations and the law.
Group Your Objects Together Logically
You have probably all noticed a change since SQL Server 2000 with respect to object ownership: formerly DB.ObjectOwner.Object, now DB.Schema.Object since 2005. With Schemas, we can benefit from the grouping of the objects to make sense of our often complicated relational database management systems. For sure, a user can still create an object within the database as UserName.Object, that is a little messy to clean up when that person leaves the company, thus Sp_rename can be used to fix those pesky messes however, if you have to deal with them.In order to grant schema rights to a user, here are a few examples:
Grant select, insert, update, execute on Schema::SchemaName to [UserName]
-- this is assuming that you have used a specific user UserName generated and SchemaName exists
CREATE SCHEMA [SchemaName] AUTHORIZATION [dbo] – members of DBO have control over the Schema
-- if you are using the built-in roles, then see below
CREATE USER [Domain\UserName] FOR LOGIN [Domain\UserName] WITH DEFAULT_SCHEMA= [db_datareader]
If you want a user to have read access, make the default schema db_datareader and not database owner (DBO).
If you grant the DBO rights, then you have granted DBO rights with control to all roles and schemas.
USE [DatabaseName] -- if you have to elevate their rights, then use the db_datawriter role first, instead of DBO
GO
EXEC sp_droprolemember N'db_datareader', N'Domain\UserName'
GO
USE [DatabaseName]
GO
EXEC sp_addrolemember N'db_datawriter', N'Domain\UserName'
GO
When we create an object, we can simply associate it within a schema (Create SchemaName.Object), which, in turn, makes managing the security of that group of objects easier. The other advantage is that we can place a schema within the database by the name of the application that uses it, which is a logical separation. Likewise, if multiple applications depend on a single database, schemas make it easy to understand which objects are used by what/whom and how within the database.
As you can see in the above example, it's easier to manage the security on the group of objects too, since you would set the permissions on schema level and not for each individual object, which was noticeably tedious beforehand. If you wish to maintain the principle of least privilege, giving DBO access to users is not the best solution in regard to security. A preferred workaround for elevated privileges would be by means of EXECUTE AS instead (for more, see a great article on that here).
(Note: You can also use stored procedures and views to minimize data access, but because this is more commonly known than security facets of schemas, I will not be expanding on that subject here.)
Easily Watch Over Processes Within Your Database Thanks to Activity Monitor
As part of a vigilant DBAs’ duty to best serve as a steward, and to comply with regulations—particularly if your organization is public or governmental—I would like to introduce one of my favorite SQL Server Management Studio enhancements with SQL Server 2008 that DBAs should be familiar with: Activity Monitor (AM). To use AM, right click on a SQL Server 2005/8 instance in the object explorer and take a look (or click on icon just below Tools/Window in SSMS, the toolbar icon that is the farthest right):
The latest version of Activity Monitor helps you see the processes, users, and applications that are currently using the database in the sortable/filterable Processes pane, and, of course, whether they are blocking transactions.
Even if you do not have SQL Server 2008 instances, it is still worth it to install SQL Server 2008 Management Studio complete client tools. Activity Monitor has built-in backward compatibility (except SQL Server 2000 and older); thus, you can take advantage of the dynamic management views already existing in SQL Server 2005 while connecting from the SQL Server 2008 Management Studio Activity Monitor. Previously, for example, to view the equivalent information in the Activity Monitor, one had to load information directly from the dynamic management views, or in SQL Server 2000 (exec sp_who2), in Excel sheets using an external data source connection to understand what was really going on across the a specific servers’ activity. Thankfully, this is now all built into SQL Server Management Studio because Activity Monitor provides sortable columns, which enable exceptionally swift pinpointing of problematic operations by database, through information on execution time/frequency, reads/writes, and CPU usage.
Since the initial version of SQL Server 2008 was released, you have been able to view real-time critical SQL Server process details and even sort by the worst-performing queries—whether it be by the number of times the offending code is run per minute, which login is running it, which database it is in, which application it is running from, etc. In Activity Monitor, practically everything you need to fix SQL Server issues is available, including the option to right click on a line in Recent Expensive Queries to optimize the problematic code right away. There are four panes with graphs for each, plus collapsible details, so you can view/filter processes, resource waits, disk activity, and Recent Expensive Queries.
If you cannot update your instances to SQL Server 2008 for a while, which would not be surprising considering the economic tsunami that just hit the world, then you can use the updated client tools in SQL Server Management Studio 2008, then at least you can use the updated client tools to enjoy this eye-opening and cost-effective updated feature—in my opinion, a critical step in remediation.
Set You Database Instance Security to Audit Successful and Failed Logins
The SQL Server log file may become significant in size if many users and/or applications connect to your database server, but now that disk space is cheap, do not skimp on your auditing capabilities. After all, you want to know the who/what/when of logins to your particular database instance. To set your server to audit logons to the SQL Server Instance, right-click on the server instance in SQL Server Management Studio, select properties, choose Security and click the radio button under Login auditing ‘Both failed and successful logins.’
Keep the Integrity of Your Database Secure by Querying Your Transaction Log File
All operations are recorded to your database transaction log file, especially if you are using Full Recovery Model; therefore, you can take advantage of a few simple queries to double-check who is running which command in the database. For more on this, read up on how to view the important information within your transaction log file. Some would consider this transaction log file forensics; I prefer to think of it as doing your job as a vigilant DBA. In the linked blog post, I describe how you can set up jobs to alert you of unwanted data manipulation language (DML) statements (such as inserts, updates, delete) or data definition language (DDL) (such as create, drop, alter).
Hack Your Databases Before Some Unwanted Access Occurs
As mentioned by Don Kiely in his great article “Hack Your Database Before the Hackers Do,” one should use a series of tools—at least three in my opinion (Microsoft SQL Server Best Practices Analyzer, SQL Password Checker from Idera, and Microsoft Baseline Security Analyzer)—to ensure due diligence.
A word of caution: If you are not the DBA, please make sure you have explicit authorization to gain access into production before proceeding. Even if you are on a project, do not assume that you can simply try to gain unauthorized access into production, as this would be considered an intrusion by the database administration team or miguided auditors.
Maintain Strong Passwords: There is No Excuse; the DBA Is Accountable
Use a password generator such as Password Manager Pro from ManageEngine, and keep the password in a password vault. Password strength is facilitated by free online password generators that uses a password length of 12 alphanumeric characters. Avoid default passwords or cutting and pasting a user name (or a using a password name that describes the level of access). Needless as it may be to say, these are not shortcuts you want to take, especially since simple software (e.g. SQL Password Checker, mentioned above) can verify password strength.
Validate User Access: Attempt to Evaluate Potential Risks to Database Security
Not only should you fully understand what roles, or specific permissions, are given for a specific user and/or application, but you can also confirm that access is at the desired level by logging on as that specific user. If you are making big changes to your systems that involve several user access changes, be sure to go through a significant deployment management process (short and long templates can be downloaded for free).
Account Management: Keep the Access List Updated
As mentioned by Sean Smith, who has helped to contribute to this article, it is essential to tidy up by means of disabling accounts, either for termed employees, or for employees who no longer require database access. It is amazing how many accounts remain active for such people whose role or mandate has changed.
Protect Against SQL Injection
Lack of proper protection against SQL Injection, fully described here by SQL MVP Adam Machanic, can cause immeasurable damage, whether intentional or accidental. In the previous link to his book Expert SQL Server Development, Adam describes the ways to protect against this, although it is unfortunately seldom considered or implemented.
Databases Are Not A Place To Be Lax On Security
As mentioned by Ericka Chickowski, in her article regarding DBAs Playing [an] Increasingly Crucial Role In Security, it is a vital aspect of our job. The points above should help guide you in the right direction as you work to help protect your organization’s assets and customer data. The consequences of not safeguarding data or failing to comply with regulations for data security can include significant fines, of up to one million dollars (yes $1M), as is done by the Information Commissioner’s Office in the United Kingdom vis-à-vis violations of the Data Protection Act, and can jeopardize business operations and the reputation of your organization. Failing to be vigilant with data could also result in the loss of billions of dollars of public money, a situation I unfortunately witnessed first-hand six months after I had internally provided a report detailing an audit failure at Canada’s largest public Institutionalized pension fund manager, the Quebec Deposit and Investment Fund (CDP Capital).
Additional Resources for Risk Management, IT Security and Data Governance from Microsoft:
Data Governance - Managing Technological Risk
Discusison of the core data governance capabilities related to technology.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e4ff1afe-69cd-4bfd-aeaa-c0519ba04272
Data Governance - A Capability Maturity Model
This paper presents a blueprint for organizations to implement the capabilities needed to establish a successful DGPC program.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1a45054b-b073-44e6-84f8-bee33c268f33
Microsoft Anti-Cross Site Scripting Library V4.0
AntiXSS 4.0 helps you to protect your applications from cross-site scripting attacks.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=f4cd231b-7e06-445b-bec7-343e5884e651
Microsoft Office Protocol Documentation
The Office protocol documentation provides technical specifications for Microsoft proprietary protocols that are implemented and used in the Microsoft Office system.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e7a23d42-0835-440f-9400-badfe9672b21
Microsoft and Data Privacy
This paper examines trends in the evolving data management landscape and describes how Microsoft is providing leadership in protecting individuals’ personal information.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=297c2531-2621-446a-9efb-db87f76de02e
Microsoft SharePoint Products and Technologies Protocol Documentation
The Microsoft SharePoint Products and Technologies protocol documentation provides technical specifications for Microsoft proprietary protocols that are implemented and used in SharePoint Products and Technologies.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5e94ad07-902c-422f-aadd-ff2bba9e540a
Microsoft and Data Breach Notification
Microsoft and Data Breach Notification
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b378d038-566d-47e1-8946-ba0561e0b988
TwC Enterprise Data Governance White Paper
Private enterprise privacy white paper, providing Microsoft's perspective on the role that technology plays in helping enterprises responsibly protect and manage personal information.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=53035b0d-66be-415a-aadc-ae47105af354
Data Governance White Paper
Data Governance White Paper
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5c711a4d-e7a9-44af-985e-f36774417f2b
IT GRC Process Management Pack for System Center Service Manager
The Microsoft® IT GRC Process Management Pack for System Center Service Manager(SCSM) provides end-to-end compliance management and automation for desktop and datacenter computers. Deeply integrated with SCSM the IT GRC Process Management pack translates complex regulations and standards into authoritative control objectives and control activities for the IT organization’s compliance program.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=aff058bb-628f-4a7d-b566-317cb53f666e
A Guide to Data Governance for Privacy, Confidentiality, and Compliance
Data governance is an approach that public and private entities can use to organize one or more aspects of their data management efforts, including business intelligence (BI), data security and privacy, master data management (MDM), and data quality (DQ) management. This series describes the basic elements of a data governance initiative for privacy, confidentiality, and compliance.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8ba05a48-f46c-491d-8857-6d9d6da2d3e3
Data Governance - People and Process
This paper examines the People and Process core capability areas required to enable Data Governance for Privacy, Confidentiality and Compliance.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b42cc616-fb59-4942-bc43-d4992dcb6f51
Microsoft and Data Breach Notification: Guidance for Enterprise Organizations
Document presenting data breach risks and concerns for organizations, and guidance for responding to a data breach.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=49151c48-dd1d-4094-8722-d1e74f821426
Privacy Guidelines for Developing Software Products and Services
This document is a set of privacy guidelines for developing software products and services that are based on our internal guidelines and our experience incorporating privacy into the development process.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c48cf80f-6e87-48f5-83ec-a18d1ad2fc1f
IT Compliance Management Series
The IT Compliance Management Series—a combination of IT Compliance Management Libraries for Windows Server 2008, Windows Server 2008 R2, Windows 7, and Microsoft System Center—provides prescriptive guidance that helps IT pros configure Microsoft products to address specific IT governance, risk, and compliance (GRC) requirements.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=37ec588c-e1bc-415b-acaa-b9b4d494f466
