none
How to change login mode in SQL Server 2008 R2

    Question

  • I had successfull installation of SQL Server 2008 R2.After that I noticed that I had enabled SQL Server Authentication only.Now I want to change this authentication setting to Mixed mode without reinstalling.

    Is there any way available?

    Monday, August 08, 2011 6:38 AM

Answers

  • Have a look at the following...

    http://msdn.microsoft.com/en-us/library/ms188670.aspx

    ...you can only have windows authentication(windows only) or sql server authentication and windows authentication(mixed mode), so if you already have sql server authentication working you must be using mixed mode already, there is no such option as SQL Server authentication only. Anyway check what you have using the steps in the link above.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Monday, August 08, 2011 6:52 AM
  • Hi Rahul,

    During Installation, it only asks for - Windows Authentication or Mixed Authentication ( SQL Server Authentication and Windows Authentication ).

    When you select Mixed Mode, it asks  for SA password which I assume made you think you had incorrectly selected SQL Authentication only which is not the case.

    So I think  you did the installation correctly if you intend to use mixed mode authentication.

    At database level, you can have users to have SQL Logins only or Windows Logins but that is entirely different from authentication mode.

    Hope this helps!!


    Regards, Vishal Srivastava
    Monday, August 08, 2011 8:11 AM

All replies

  • Have a look at the following...

    http://msdn.microsoft.com/en-us/library/ms188670.aspx

    ...you can only have windows authentication(windows only) or sql server authentication and windows authentication(mixed mode), so if you already have sql server authentication working you must be using mixed mode already, there is no such option as SQL Server authentication only. Anyway check what you have using the steps in the link above.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Monday, August 08, 2011 6:52 AM
  • Hi Rahul,

    During Installation, it only asks for - Windows Authentication or Mixed Authentication ( SQL Server Authentication and Windows Authentication ).

    When you select Mixed Mode, it asks  for SA password which I assume made you think you had incorrectly selected SQL Authentication only which is not the case.

    So I think  you did the installation correctly if you intend to use mixed mode authentication.

    At database level, you can have users to have SQL Logins only or Windows Logins but that is entirely different from authentication mode.

    Hope this helps!!


    Regards, Vishal Srivastava
    Monday, August 08, 2011 8:11 AM
  • Hello Rahul,

    Check the following article which explains How to Identify and Change Currently Used SQL Server Authentication Mode in SQL Server.

    Hope that Helps!

    Cheers,
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem and Vote as Helpful if a Post is Helpful!

    Monday, August 08, 2011 10:56 AM