locked
How can i change my authentication mode. RRS feed

  • Question

  • Hi, When i installed my MS SQL Server i have given it as Windows authentication mode..Now i want to change it to sql server authentication mode..

     

    Can anybody suggest wht i have to do...

    Friday, April 16, 2010 8:01 AM

Answers

  • Yes, you can change the authentication mode from Windows Authentication to Mixed Mode Authentication (SQL Server and Windows Authentication). To change it follow the steps mentioned below:

    1. Connect to the SQL server instance using SSMS. Right-click on the instance and go toProperties.

    2. Go to the Security tab on the left side of the Server Properties page. Select theSQL Server and Windows Authentication Option.

    To make these changes work, you need to restart your SQL Server instance.

    TO Enable the sa account follow the steps mentioned below:

    1.Connect to the SQL instance using SSMS and expand it. Go to theSecurity folder.

    2. Expand Security folder and expand Logins. Right-click on thesa  login, go to Properties. Set the sa password. If thepassword policy enforced is checked then provide a complex password as mentioned byDinesh.

    3. Go to Status page of sa account , in the Login section, selectEnable. Click on Ok to close the properties windows.

    You should be able to login to the SQL instance using sa login.

    Note: When SQL Server is installed with Windows Authentication, by default, thesa account is disabled login to the instance.

    Hope this will resolve your issue.


    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, April 16, 2010 12:08 PM

All replies

  • Right Click serevr from SSMS.

    Go to Security

    Change to Server and Windows Authentication mode


    Blog: http://dineshasanka.spaces.live.com
    Friday, April 16, 2010 8:27 AM
  • Hi,

    Connect To Server Through Management Studit --> Right Click On Server --> Properties --> Click on Security Tab --> There You Find 1) Windows Authentication Mode & 2) SQL Server And Windows Authentication. select 2 option.


    Failure in Life is failure to try...
    Friday, April 16, 2010 8:27 AM
  • Means i want to put a password for my sql authentication ...is that possible...
    Friday, April 16, 2010 8:34 AM
  • Yes, You have to put password for sa account
    Failure in Life is failure to try...
    Friday, April 16, 2010 8:46 AM
  • i want to put a password now ...but i searched a lot not able to find.....
    Friday, April 16, 2010 8:51 AM
  • Hi Vishvesh,

    What are you looking at, to change the password are you searching???

     

     


    Failure in Life is failure to try...
    Friday, April 16, 2010 8:53 AM
  • when i installed my server i installed on windows authentication mode ....now i want to change to sql server authentication mode with sa user and sa password..

    i can change the connection mode but where i have to give the sa username and sa password...

     

    ..u r saying go to properties and change to sql server authentication ..but need a user name and password..

     

    what i have to type in that one...i never give a sa account.. so what i have to do to change the sa accounts....

     

     

     

    Friday, April 16, 2010 9:01 AM
  • Connect to the server with Windows Authentication. Exapnd Security --> Logins.

    Under Logins, you have the 'sa' login object, double click to open the properties and set the password.

    Hope this helps.


    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, April 16, 2010 9:09 AM
  • it is not working .it is giving error...

    what if i need to installed a new server...

     

    Friday, April 16, 2010 9:28 AM
  • Do you mean to say, you are unable to connect to the SQL instance using Windows Authentication, after you make the changes (Windows --> SQL Authentication)?

    Can you paste the error message?

    Thanks


    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, April 16, 2010 9:35 AM
  • i am getting this error..

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Change password failed for Login 'sa'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1442.32+((SQL_PreRelease).080529-2343+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Change+password+Login&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Password validation failed. The password does not meet Windows policy requirements because it is not complex enough. (Microsoft SQL Server, Error: 15118)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1442&EvtSrc=MSSQLServer&EvtID=15118&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Friday, April 16, 2010 9:54 AM
  • I think the password you are providing for your 'sa' account, is not a complex password.

    You can fix this issue in two ways:

    1. Either use a complex password for your sa account

    2. Uncheck the 'Enforce Password Policy' option, in the sa properties page, however, it depends on your company policy (& not recommended).

    Let me know, if this helps.


    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, April 16, 2010 10:21 AM
  • You might have use simple password such as "sa"

    use some thig like Pa$$w0rD


    Blog: http://dineshasanka.spaces.live.com
    Friday, April 16, 2010 10:29 AM
  • cannt we edit the installed server from windows authentication to sql server authentication...
    Friday, April 16, 2010 10:56 AM
  • Yes, you can change the authentication mode from Windows Authentication to Mixed Mode Authentication (SQL Server and Windows Authentication). To change it follow the steps mentioned below:

    1. Connect to the SQL server instance using SSMS. Right-click on the instance and go toProperties.

    2. Go to the Security tab on the left side of the Server Properties page. Select theSQL Server and Windows Authentication Option.

    To make these changes work, you need to restart your SQL Server instance.

    TO Enable the sa account follow the steps mentioned below:

    1.Connect to the SQL instance using SSMS and expand it. Go to theSecurity folder.

    2. Expand Security folder and expand Logins. Right-click on thesa  login, go to Properties. Set the sa password. If thepassword policy enforced is checked then provide a complex password as mentioned byDinesh.

    3. Go to Status page of sa account , in the Login section, selectEnable. Click on Ok to close the properties windows.

    You should be able to login to the SQL instance using sa login.

    Note: When SQL Server is installed with Windows Authentication, by default, thesa account is disabled login to the instance.

    Hope this will resolve your issue.


    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, April 16, 2010 12:08 PM