Choosing an Authentication Mode
During setup, you must select an authentication mode for the Database Engine. There are two possible modes: Windows Authentication mode and mixed mode. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled.
If you select Mixed Mode Authentication during setup, you must provide and then confirm a strong password for the built-in SQL Server system administrator account named sa. The sa account connects by using SQL Server Authentication.
If you select Windows Authentication during setup, Setup creates the sa account for SQL Server Authentication but it is disabled. If you later change to Mixed Mode Authentication and you want to use the sa account, you must enable the account. Any Windows or SQL Server account can be configured as a system administrator. Because the sa account is well known and often targeted by malicious users, do not enable the sa account unless your application requires it. Never set a blank or weak password for the sa account. To change from Windows Authentication mode to Mixed Mode Authentication and use SQL Server Authentication, see How to: Change Server Authentication Mode.
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.
Security Note
|
|---|
|
When possible, use Windows 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. For strong password guidelines, see Database Engine Configuration - Account Provisioning.
Three optional password policies are available for SQL Server logins.
-
User must change password at next login
Requires the user to change the password the next time that the user connects. The ability to change the password is provided by SQL Server Management Studio. Third-party software developers should provide this feature if this option is used.
-
Enforce password expiration
The maximum password age policy of the computer is enforced for SQL Server logins.
-
Enforce password policy
The Windows password policies of the computer are enforced for SQL Server logins. This includes password length and complexity. This functionality depends on the NetValidatePasswordPolicy API, which is only available in Windows Server 2003 and later versions.
To determine the password policies of the local computer
-
On the Start menu, click Run.
-
In the Run dialog box, type secpol.msc, and then click OK.
-
In the Local Security Settings application, expand Security Settings, expand Account Policies, and then click Password Policy.
The password policies are described in the results pane.
Disadvantages of SQL Server Authentication
-
If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time that one connects to the database can be annoying.
-
SQL Server Authentication cannot use Kerberos security protocol.
-
Windows offers additional password policies that are not available for SQL Server logins.
Advantages of SQL Server Authentication
-
Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication.
-
Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.
-
Allows users to connect from unknown or untrusted domains. For instance, an application where established customers connect with assigned SQL Server logins to receive the status of their orders.
-
Allows SQL Server to support Web-based applications where users create their own identities.
-
Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset SQL Server logins.
Note
Using SQL Server Authentication does not limit the permissions of local administrators on the computer where SQL Server is installed.
To change security authentication mode
-
In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
-
On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
-
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
To restart SQL Server from SQL Server Management Studio
-
In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
To enable the sa login by using Management Studio
-
In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
-
On the General page, you might have to create and confirm a password for the sa login.
-
On the Status page, in the Login section, click Enabled, and then click OK.
-
In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
-
On the General page, you might have to create and confirm a password for the sa login.
-
On the Status page, in the Login section, click Enabled, and then click OK.
To enable the sa login by using Transact-SQL
-
Execute the following statements to enable the sa password and assign a password.
ALTER LOGIN sa ENABLE ; GO ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ; GO
Good Luck !!!
- 2/10/2012
- Anvyas
Use the following link to change your authentication mode, you need to change registry key of "LoginMode".
http://www.arcomit.co.uk/support/kb.aspx?kbid=000002
For Quick View:
Resolution
The location of the LoginMode subkey depends on whether you installed MSDE as the default MSDE instance or as a named instance. If you installed MSDE as the default instance, the LoginMode subkey is located in the following registry subkey:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode
If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
To change the value of LoginMode to 2, follow these steps:
In Control Panel, open the Services tool to stop MSSQLSERVER and all other related services (such as SQLSERVERAgent)
To open Registry Editor, click Start, click Run, type regedit, and then click OK.
Locate either of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\-or-
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\
In the right pane, double-click the LoginMode subkey.
In the DWORD Editor dialog box, set the value of this subkey to 2, make sure that the Hex option is selected, and then click OK.
Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.
Best of Luck
- 6/12/2011
- Bilal Iqbal
- 6/12/2011
- Bilal Iqbal
Below is the link where you can resolution for changing SQL authentication mode after installation, you need to change "LoginMode" registry key.
http://www.arcomit.co.uk/support/kb.aspx?kbid=000002
Best of Luck
- 6/12/2011
- Bilal Iqbal
