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.
