Windows (Trusted) Authentication Vs SQL (Mixed-Mode) Authentication

Just a quick post for my future reference on the differences between Trusted authentication and Mixed-mode Authentication used by SQL Server

Windows Authentication

  • When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows.
  • SQL Server does not ask for the password, and does not perform the identity validation.
  • Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication.
  • Windows Authentication
    • uses Kerberos security protocol,
    • provides password policy enforcement with regard to complexity validation for strong passwords,
    • provides support for account lockout,
    • and supports password expiration.
  • A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

SQL Authentication

  • When using SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts.
  • Both the user name and the password are created by using SQL Server and stored in SQL Server.
  • Users connecting using SQL Server Authentication must provide their credentials (login and password) every time that they connect.
  • When using SQL Server Authentication, you must set strong passwords for all SQL Server accounts.
  • Three optional password policies are available for SQL Server logins.
    • User must change password at next login
    • Enforce password expiration
    • Enforce password policy
  • SQL Server Authentication cannot use Kerberos security protocol.
  • Supports environments with mixed operating systems, where all users are not authenticated by a Windows domain.


Microsoft falters again !

A new vulnerability has been discovered in Microsoft IIS 6.0 which allows an attacker to access protected(password) content in a website. The vulnerability arises because of the way IIS handles Unicode.

Microsoft has released a security advisory 971492 and it’s follow-up

According to the advisory the scale of vulnerable systems is reduced due to multiple factors

* An IIS server not running WebDAV is safe.
The Windows Server 2003 IIS (version 6) shipped with WebDAV disabled by default.
* An IIS server not using IIS permissions to restrict content to authenticated users is safe.
* An IIS server that does not grant filesystem access to the IUSR_[MachineName] account is safe.
* An IIS server that hosts web applications using only forms-based authentication is probably safe.

If your web server meets all of the following criteria, you will want to read on:

* IF an IIS 5, 5.1, or 6.0 webserver is running with WebDAV enabled;
* AND the IIS server is using IIS permissions to restrict a subfolder of content to authenticated users;
* AND file system access is granted for the restricted content to the IUSR_[MachineName] account;
* AND a parent folder of the private subfolder allows anonymous access;
THEN an anonymous remote user may be able to leverage this vulnerability to access files that normally would only be served to authenticated webserver users.

This vulnerability is primarily an information disclosure threat.

Thierry Zoller has made an excellent post explaining the vulnerability with graphical representation. It’s a must-read.

He made a reference to the SANS article on the original Unicode vulnerability in IIS 4.0 and 5.0 which explains the Unicode issues in depth.

There are a few tools that can be used to search for WebDav enabled servers on the network (referenced from Zoller’s blog
* Specifically for this vulnerability: Metasploit added test script to the trunk (use svn update to get the latest exploits)
* Webdav network scanner here
* Nmap webdav scanner

Till Microsoft releases a patch to fix this vulnerability, it’s best to disable WebDAV on IIS servers (Sharepoint user beware)