SQL Server 2005 Security Tips

Auditing and compliance demands often involve security issues. Here are some tips from the Microsoft SQL Server team for securing data in your enterprise, which can help streamline your compliance efforts.

Related: 10 Steps to SQL Server 2005 Security

Tip 1: Find Your Data

Many enterprises have huge amounts of data to manage, and sometimes the locations, permissions, and sensitivity of those disparate bits of data aren't always known. "Don't just do an assessment of where your data is," says JC Cannon, a privacy strategist in Microsoft's Corporate Privacy Group. "Find where the data is, but also find out the sensitivity of that data. Look at the permissions. Some people may need access to only parts of a table, but not all the data. Protect the sensitive data."

Tip 2: Manage Privileged Access

Tracking down the locations of all your enterprise data is a priority, but keeping tight controls on who has privileged access to a database—and when they access that data—is also vital. "You should really only connect to the server with the privilege level required for the task at hand," explains Al Comeau, Microsoft SQL Server security lead. "Many DBAs want to always connect as administrators, which is very bad practice. If your application is cracked, it presents a serious threat elevation to your data." Comeau adds that many tools are available to help manage privileged access, including Windows credentialing, hierarchies of security scopes, and a rich set of permissions. "All the ingredients are there to manage access properly," says Comeau. "Don't take the path of least resistance."

Tip 3: Mind your Endpoints

SQL Server 2005 introduced HTML endpoints, which SQL Server uses to create Web services. SQL Server 2005 also supports a variety of connection methods to the server, which is why careful management of the creation and supervision of those endpoints is so important. "You have the ability to encrypt those connections and to restrict access to only certain named endpoints," says Microsoft Senior Product Manager Niraj Nagrani. "This can help you manage external access to the database effectively."

Tip 4: Monitor Old Data

Finding data, managing access, and supervising endpoints are all vital parts of security for keeping active data protected, but what about those old, lost bits of neglected data that accumulate in every enterprise? Cannon urges enterprises to manage their old data with the same vigilance as they manage their newest, most sensitive information. "Organizations need to do a proper assessment of their data and not just collect information," says Comeau. "Old data can pose a significant security risk. Most security breaches come from the inside, and neglected data often still has sensitive information worth protecting."