Choosing a Secure Authentication System

My company is deciding between SQL Server Authentication and Windows Authentication for future applications. Which authentication method provides better security?

Many companies use SQL Server Authentication, which I think is easier to use than Windows Authentication. But SQL Server Authentication isn't the most secure option. (You can read about the range of SQL Server's security vulnerabilities in David Litchfield's white paper "Threat Profiling Microsoft SQL Server" at http://www.nextgenss.com/papers/tp-SQL2000.pdf.)

First, SQL Server lacks a lockout option to protect against brute-force attacks, such as a dictionary attack on an authenticated account. Second, SQL Server password encryption is basically useless; you can find a number of sources that provide code to decrypt a SQL Server-authenticated password, which you can easily capture by using a network sniffer. You can find a T-SQL function that decodes passwords captured from a trace at http://www.sqldbatips.com/presentations/really_hacking_sql.ppt. I don't usually disclose hacking tips, but these examples are well known to all the "bad guys" ­and as a "good guy," you need to know what to expect and protect against.

Windows Authentication is substantially more secure than SQL Server Authentication. For a detailed discussion of the superiority of Windows Authentication, see Morris Lewis's "Guard Your Data with Kerberos," July 2002, InstantDoc ID 25080. Microsoft has designed a more robust security and authentication system for SQL Server 2005, and you can read about these improvements at http://www.microsoft .com/sql/2005/productinfo/securityfeatures_2.asp or in Kalen Delaney's "Inside SQL Server 2005 Security," May 2004, InstantDoc ID 42031. But for now, use Windows Authentication to provide better security for your applications.

Discuss this Article 1

Anonymous User (not verified)

on Jan 14, 2005

If you are going to use your SQL Server to host 3rd party application databases, you'll probably be forced to keep SQL Authentication enabled. Many 3rd party vendors are using SQL Authentication exclusively.

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More