• Explanation of different SQL Server Authentication Modes

    Posted on March 17, 2012 by in Database, SQL Server

    SQL Server supports two authentication Modes 

    1)      Windows Authentication

    In case of Windows Authentication, the KERBEROS token assigned during the logon process would be sent to SQL Server when requesting resources. In this case, such kind of accounts must belong to the same domain as the hosting SQL Server. In the Active Directory domain environment, an additional level of protection is provided by Kerberos protocol, which governs the behavior of the Windows authentication mechanism. Microsoft recommends Windows Authentication.

    2)      Mixed Mode Authentication (Windows Authentication & SQL Server Authentication)

    Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server.

    Windows Authentication:

    Advantages:

    • Because the password is authenticated at network login, SQL Server does not need to know or verify the password of a user.  Instead of granting/denying access to individual accounts, you could give access to domain groups. So it reduces logging administration overhead.
    • Users don’t need to maintain different credentials for network and database access.
    • 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 Server Authentication:

    When should we use SQL Server Authentication?

    • If you have web based applications where users would create usernames & passwords as part of registration process, we have to use SQL Server authentication.
    • If you want to support legacy systems or non-windows system where you don’t have windows authentication,  you have to use SQL Authentication.
    • Imagine a  complex environment where you have different operating systems. In these cases you don’t have any other choice then using SQL Server Authentication

     

    Disadvantages:

    • It is less secure compared to windows  authentication which uses KERBEROS security protocol.
    • Monitoring activities of individual user is impossible.
    • It would be annoying to ask user to maintain different username’s/passwords for  system login and  database login.

    How to change the authentication mode to Mixed?

    The default authentication mode enabled in SQL Server is “Windows”. It is pretty easy to enable Mixed Mode.

    Steps:

    1)      Open SQL Server management studio and connect to the SQL Server using “Windows Authentication”.

    2)      Right click on the server and select “Properties”

    3)      Select “Security” left menu item on the “Server Properties” window.

    4)      Select “SQL Server and Windows Authenitcation Mode” radio button

    SQL Server Authentication Modes

    5)      Click on “OK”

    6)      Below confirmation pop-up shows up. Click “OK” again

    Explanation of different SQL Server Authentication Modes

    7)      Restart your management studio .

    Be Sociable, Share!

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply