17

I'm trying to change the password of the sa account using SQL Server Management Studio 2012. I've followed the steps below but the password is still the same.

  1. Login into the SQL Server Management Studio, Select Database Engine, \SBSmonitoring, Windows Authentication.
  2. Go to Object Explorer--Security folder--Logins folder.
  3. Right click on SA account and select the Properties option.
  4. In General Page, change the SA password and confirm it.
  5. In Status Page, change Login to Enabled. Click OK to save the change.
  6. Restart the SQL Server and all its services.

Any idea ?

Improve this question
3
  • What error are you getting when you try to log in? Has the server been configured for Windows Authentication only? Is the sa account disabled? Has the sa account been renamed and another account named sa been added? What do you get when you run these: SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Windows and SQL Server Authentication' END as [Authentication Mode]; SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE sid = 0x01; SELECT * FROM sys.syslogins WHERE sid = 0x01; – Bacon Bits Jun 11 '15 at 15:27
  • 2
    Verify your instance is set to Mixed Authentication. – user507 Jun 11 '15 at 15:42
  • 2
    The ERRORLOG will tell you the reason for logon failure. If mixed authentication is not enabled, you will see "Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only." Moreover, why on earth are you using sa? It's a well know login and a potential attack vehicle: disable it and configure a different sysadmin login. – spaghettidba Jun 11 '15 at 15:51
14

use this query it will change sa password 

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'newpassword' 
GO
Improve this answer
2
  • 1
    Thank you, but I still have problems. In fact, when executing these commands on SQL Server Management Studio I got this message: "Command(s) completed successfully." ... But when I try to log in with the new password it doesn't work. Even when I consult the SA properties I found that nothing was really changed, even though I've done a refresh ! – lobna Jun 11 '15 at 14:27
  • ihad test it with my db and its working perfect.just close the sql management studio and open it again and try with new password.and let me know if u have still problems – Arunprasanth KV Jun 11 '15 at 14:34
11

You were really near with your solution. You just have to restart the SQL Server instance \SBSMonitoring after changing the sa password as explained in the following article and ensure that Mixed Mode Authentication is turned on.

Solution

Reference: What is the default password for 'sa' account in windows sbs 2011 standard (I have modified the orginal listing slightly):

  1. Login into the SQL Server Management Studio

    • Select Database Engine, <SBSservername>\SBSmonitoring
    • open Server Properties
    • click on the Security section
    • select SQL Server and Windows Authentication mode
    • Click on OK
    • Restart SQL Server instance

  2. Go to Object Explorer--Security folder--Logins folder.

  3. Right click on SA account and select the Properties option.

  4. In General Page, change the SA password and confirm it.

  5. In Status Page, change Login to Enabled. Click OK to save the change.

  6. Restart the SQL Server and all its services.

This should do the job.

Alternative solution

Run the following statement to set the SQL Server instance to Mixed mode:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

Then restart your server and change the sa password as you normally would.

Mixed Mode (SQL Server and Windows Authentication mode)

If you don't turn on Mixed Mode authentication you will constantly receive an error message when trying to log in with the sa SQL Server login even though you have changed the password and enabled the login.

Login error Windows Authentication mode

Enabling Mixed Mode (aka, SQL Server and Windows Authentication mode) will get rid of this error message.

Improve this answer
0

There are various methods to do this:

1) You can do this with help of of command prompt

2) Using a third party software like SQL server password changer.link here

3) Try to restore the Master database from database backup. This won’t help in the long run because you’ll still encounter the same issue if you don’t remember the SA password.

Please refer to the below link for using command prompt which has worked for me in past:

https://technet.microsoft.com/en-us/magazine/jj853293.aspx

Improve this answer
0

if you are using "windows authentication" only, then your SA account is disabled by default. Verify that you are using mixed authentication, otherwise you won't be able to log in to sql server.

Improve this answer
0

I want to add to John K. N. excellent answer the link for changing the authentication mode

Here it shows you

1)the steps to change from only Windows authentication to Mixed mode authentication (which will enable you to use the server authentication and change the password)

Change authentication mode with SSMS

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

2)how to change the password

Using Transact-SQL

The following example enables the sa login and sets a new password. Replace [enterStrongPasswordHere] with a strong password before you run it.

ALTER LOGIN sa ENABLE ;  
GO  
ALTER LOGIN sa WITH PASSWORD = '[enterStrongPasswordHere]' ;  
GO

It just worked for me

Improve this answer
-1

My understanding is that you should always change the password through the SQL Server Configuration Manager. Otherwise it may not correctly change throughout the system.

Improve this answer
-1
  1. Login into the SQL Server Run as an administration
  2. select SQL Server and Windows Authentication mode log in to server
  3. Go to Object Explorer--Security folder--Logins folder.
  4. Right click on sa account and select the Properties option. (IF you want to change the name also right click on sa and rename it)
  5. In General Page, change the sa password and confirm it.
  6. In Status Page, change Login to Enabled. Click OK to save the change.
  7. Restart the SQL Server and all its services.
  8. Disconnect and Connect
  9. Login name sa (if not change the rename) and password is new one
Improve this answer
2
  • 1
    How do this differ from previous answers? – John K. N. Feb 1 '20 at 15:24
  • If you want to reset the password then definitely run it as an admin – Piyal Senarathna Feb 2 '20 at 4:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy