SQL Server security: Enhancements in encryption, authentication and auditing

Service provider takeaway: SQL Server's security capabilities have undergone big changes in recent years. Service providers can learn what's changed from version to version and why the security enhancements to SQL Server 2008 can help drive the upgrade cycle.

SQL Server security has evolved appreciably over the past decade, driven by hacking, permissions requests from customers and auditing requirements. Service providers should become familiar with these SQL Server security features to educate their customers and to deploy the features at customer sites. This tip details the evolution in encryption, authentication and auditing capabilities, from before SQL Server 2000 to the current version, SQL Server 2008.

Hacking/encryption

Ten years ago, the majority of hackers were in it for the fame; they wanted to create a name for themselves and impress peers by defacing websites or hacking into private networks. Nowadays, it's all about money. There's an underground market for "cards" (credit card numbers and related information) and "fulls" (a full set of information required to commit identity theft). Both sets of information have a short shelf life and live in databases. Theft of both cards and fulls is very difficult to track and is often noticed weeks or months after the occurrence. In response to this growing threat, businesses have taken on the balancing act of encrypting as much of their data as possible while not significantly degrading database performance.

In SQL Server 2005, Microsoft added encryption support through the use of the following functions:

One problem with this approach is that the stronger the encryption method, the slower the decryption process. EncryptByPassPhrase provides the we

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Relational Database Management Systems (RDBMSes) Troubleshooting a failed SQL Server 2008 installation Microsoft SQL Server 2008 guide Oracle Database 11g study guide Federated databases Metadata Basics Oracle RAC troubleshooting advice and application migration tips Oracle Database 11g tutorial Systems products to pay attention to Reasons to upgrade to SQL Server 2008 SQL Server security: Authentication Database Management Services Troubleshooting a failed SQL Server 2008 installation Microsoft SQL Server 2008 guide Oracle Database 11g study guide FAQ: SQL Server 2008 high-availability services High-availability options in SQL Server 2008 Bridging the IT/business gap in business intelligence projects Business intelligence consulting: Problems and solutions Data management concerns of MDM-CDI architecture SSIS brings business intelligence services prospects Oracle RAC troubleshooting advice and application migration tips

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

akest form of encryption and is the fastest, while EncryptByAsmKey provides the strongest encryption but is the most resource-intensive and consequently has the slowest decryption algorithm. Secondly, encryption based on symmetric key, asymmetric key and certificates are nondeterministic algorithms. In other words, they have a time component; each time you issue them, the encrypted value will be different. This means that you can't index encrypted columns, since, for instance, the word "aardvark" could be encrypted differently each time a function is called. (You can find more information about this topic on this MSDN blog.)

If your customers are using SQL Server 2005, you should educate them about which encryption algorithm -- or combination of algorithms -- will work best for them. For example, a passphrase that's encrypted with an asymmetric key algorithm will likely be the best combination for fast decryption and strong encryption. For very strong encryption requirements, you should suggest using an asymmetric key. You should also help clients determine what portions of their data should be encrypted. Many architects encrypt only a portion of their data -- for instance, last name or all but the last four digits of a Social Security Number or credit card number. This approach requires minimal decryption and protects against a "fulls" hack.

In SQL Server 2008, Microsoft added several features to strengthen encryption, including:

Many IT departments are undecided as to whether they should upgrade from SQL Server 2000 to SQL Server 2005 or go straight to SQL Server 2008. Some of SQL Server 2008's encryption features will make a move to that version compelling or even necessary for your customers.

Go to page: 1 - 2 - 3