Learn

The forum of documents and methods for studying - Lượm lặt kiến thức


    4 Ways to Enable Mixed Mode Authentication for SQL Server

    QaniTri
    QaniTri
    Admin

    Nam Libra Monkey
    Tổng số bài gửi : 1568
    Tiền xu Ⓑ : 3891
    Được cảm ơn № : 6
    Ngày khởi sự Ngày khởi sự : 07/01/2013
    Đến từ Đến từ : HCMC
    Côngviệc / Sởthix Côngviệc / Sởthix : Languages, Softwares, Sciences, Martial arts

    4 Ways to Enable Mixed Mode Authentication for SQL Server Empty 4 Ways to Enable Mixed Mode Authentication for SQL Server

    Bài gửi by QaniTri 16th May 2017, 11:01

    This article introduces four ways for SQL Server users to enable Mixed Mode Authentication from installation to use.

    • Way 1: Enable Mixed Mode Authentication during SQL Server Installation

    • Way 2: Enable SQL Server Mixed Mode Authentication by Changing Instance Properties

    • Way 3: Enable Mixed Mode Authentication with SQL Query

    • Way 4: Enable Mixed Mode Authentication in Registry Editor


    Way 1: Enable Mixed Mode Authentication during SQL Server Installation

    If you have paid attention to the SQL Server installation, you would find there is a step setting Authentication mode. If you don’t change the default settings, Windows Authentication will become default authentication mode. If you choose Mixed Mode Authentication, the installation progress will ask you to set strong password for SA account.
    4 Ways to Enable Mixed Mode Authentication for SQL Server Choose-mixed-mode-in-sql-server-installation
    Once you set SA account during SQL Server installation, Mixed Mode Authentication would be available after finishing installing SQL Server. When you connect to SQL Server database, there would be two authentication modes for you to choose.  
    4 Ways to Enable Mixed Mode Authentication for SQL Server Two-modes-for-sql-server-login
    Even though you don’t choose Mixed Mode Authentication in SQL Server setup progress, following two ways still could help to enable SQL Server Mixed Mode Authentication.

    Way 2: Enable SQL Server Mixed Mode Authentication by Changing Instance Properties

    1. Right click on the SQL server instance.
    2. Click on Properties.
    4 Ways to Enable Mixed Mode Authentication for SQL Server Open-sql-server-instances-properties
    3. Click on Security on the left pane.
    4 Ways to Enable Mixed Mode Authentication for SQL Server Enable-sql-server-and-windows-authentication-mode
    4. Check SQL Server and Windows Authentication mode under Server authentication section. Click OK.

    Way 3: Enable Mixed Mode Authentication with SQL Query

    EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

    [Note: 2 indicates mixed mode authentication. 1 is for windows only authentication]

    Way 4: Enable Mixed Mode Authentication in Registry Editor

    This way applies to users who have no administrator privileges to SQL Server, and cannot run SQL query easily.
    Step 1: Open the registry editor.
    Step 2: Navigate to:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer

    Step 3: Change the value of LoginMode from 1 to 2.
    Step 4: Close the registry editor.
    Tips: One thing to note is I had a few different options under the Microsoft SQL Server branch so you may need to take an educated guess if yours is different.
    Refer to: How to Change SQL Server Authentication Mode
    Related Articles:

    • How to Enable SA Account in SQL Server

    • Why Cannot Login into SQL Server with Windows Authentication Mode

    • How to Enable or Disable Windows Built-in Administrator

    • 4 Effective Ways to Change SQL Server SA Password





    isunshare.com


      Hôm nay: 29th March 2024, 03:04