SQL 2005+Enable or Disable SQL Server Authentication
SQL Server supports two types of security, known as Windows Authentication and SQL Server Authentication. These security options are configured during the installation of the database management system but can be reconfigured later as requirements change.
SQL Server Security Modes
Microsoft SQL Server supports two authentication models. These are Windows Authentication and SQL Server Authentication. Windows authentication is the default option for new SQL Server installations. This type of security uses the Windows principal token from the user's operating system to make trusted connections to SQL Server. This means that connections are made using the account details that the user provided to Windows. The user does not provide a user name or password when accessing the server.
SQL Server Authentication uses logins that are created in SQL Server and not linked to Windows user accounts. Each time the user connects to a database, they must provide a login name and password. During installation, SQL Server authentication can be enabled by selecting "Mixed Mode" security. This mode allows connections to be made using either Windows authentication or SQL Server authentication. If mixed mode is used, a user named "sa" is created with a supplied, strong password.
Enabling or Disabling SQL Server Authentication
It is considered best practice to configure SQL Server instances to allow only Windows authentication. However, some situations require that SQL Server authentication be used. For example, some older applications are not able to connect using Windows authentication and some connections may be required from alternative operating systems. You may also decide to take advantage of SQL Server logins for web site users or users from untrusted domains. In such cases, you may wish to enable SQL Server authentication after installation.
You can enable or disable SQL Server authentication easily using SQL Server Management Studio. In the Object Explorer, right-click the name of the server that you wish to reconfigure and select "Properties" from the menu that appears. This displays the Server Properties dialog box. Select the "Security" option from the list. You can then choose either "Windows Authentication mode" or "SQL Server and Windows Authentication mode". Click OK to save the changes and restart the SQL Server service to enable the new setting.
NB: When you enable SQL Server authentication, the sa login remains disabled. This login is a common target for attacks and should be enabled only if it is essential to do so. If you do enable the sa user, ensure that you provide a strong password.
27 March 2010