You must Sign In to post a response.
  • Category: SQL Server

    Changing authentication mode in SQL server


    Just now i have installed SQL server 2014, during installation it asks for mixed mode or windows authentication mode. I gave Win authentication mode, but now i want to change it as mixed mode.
    Can anyone suggest me, how to change the mode without uninstalling SQL server.

    Thanks in Advance
  • #767363
    You do not need to reinstall the SQL server. You can change it using the Microsoft SQL Server Management Studio. You can change the server authentication in the following way.
    1. Right click the SQL Server.
    2. Go to the Security
    You can see the Server authentication. Select "Sql server and windows authentication"

    By Nathan
    Direction is important than speed

  • #767367
    yes agree with Mr. Nathan:
    Goto SQL Server Management Studio.
    Login with credentials.
    Goto Object Explorer and right-click the server then click Properties.
    Goto Security page--> under Server authentication, select the new server authentication mode, and then click OK.
    Then restart SQL Server Management Studio.

  • #767368

    Open SQL Server Management Studio Object Explorer, right click the server and then select Properties, in security page under Server authentication select the new server authentication mode then click OK and then acknowledge the same and then Restart the server.

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

  • #767376

    Start your Sql Server -> Select or type your server Name -> Select Your Authentication -> there 2 option have

    1.Windows Authentication ,2. Sql Server Authentication.
    Windows no need user id password but sql server Authentication need to user id and pwd
    you can follow this steps then you can change your authentication mode.

    Name : Dotnet Developer-2015
    Email Id

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767379
    When you go for mixed mode then you need to use SqlServerAdmin (sa)authentication and as the policy of the SQL you can not keep sa password as empty, see below steps to change the authentication mode
    To change security authentication mode

    1.In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
    2.On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
    3.In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
    4.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
    1.In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
    2.On the General page, you might have to create and confirm a password for the login.
    3.On the Status page, in the Login section, click Enabled, and then click OK.

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

Sign In to post your comments