Database Simplified Headline Animator

Database Simplified

Monday 17 October 2011

Change Windows Authentication To Mixed Mode In SQL Server

This post describes changing Window Authentication mode to Mixed Authentication mode in SQL Server. When you install SQL Server by default window authentication is installed If you do not choose mixed authentication at the time of SQL Server installation. However these modes can be changed even after installation of SQL Server. Let’s See How We Can Change Window Authentication of Mixed Mode.

Note : Changing Mode From Window To Mixed Or Visa Versa Requires SQL Server Service Restart.

Step 1 : Start SQL Server Management Studio

In Object Explorer Right Click In ServerName and Select Properties As Can be seen in following image.

image

Step 2 : You will Get Server Property Dialogue Box.

In Property Dialogue Box Select Security Option and under Server authentication Select “SQL Server and Window Authentication mode” and Click OK.

image

Step 3  : Enable “sa” login (Optional)

sa login is disabled by default when SQL Server is installed with window authentication. So when you choose mixed authentication you can also enable sa login (which is disabled by default).

Goto  ServerName in Object Explorer –> Security –> Logins –> Choose sa login name. Right Click on login name (sa) and click properties option see following screen shot.

image

you get following dialogue box  (login property), Under General Page. You need to set strong password and can choose option like “Enforce Password policy” making you password more secure.

image

Goto Status Page : See Following Screen Shot and Select Enable. Click Ok.  Now Re-Start SQL Server Service and login with SQL Server Authentication.

image

19 comments:

  1. Thanks a ton... Really very very helpful for me...

    ReplyDelete
  2. Brilliant this was very helpfull

    ReplyDelete
  3. Each database transaction is required to adhere to any and all rules implemented for maintaining database integrity and minimizing the risk of database failure.kpi dashboards

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. I think that thanks for the valuabe information and insights you have so provided here. Folder Lock, For COMPUTER - Don't Spend Time Seeking, Read About Cellular Desktops On this page how to password protect files

    ReplyDelete