@manhnguyenv

Welcome to my blog!

Default SA password of SQL Server

December 8, 2018 09:47

Default SA password of SQL Server (edit)

Here are the steps I did to fix this:

  • Login to the SQL Server using a local account which has administrative privileges account (eg: .\Administrator)
  • Once you have logged into Windows, open SQL Management Studio
  • Connect to the SharePoint SQL instance using Windows Authentication
  • Enable Mixed Mode Authentication (this is not enabled by default for the SharePoint SQL instance)
  • Set a password for the sa account
  • Enable the sa account
  • Restart the SQL instance (required due to change in authentication mode)

Enabling Mixed Mode Authentication:

  1. Right click on the SQL server instance
  2. Click on Properties
  3. Click on Security on the left pane
  4. Click on SQL Server and Windows Authentication Mode under the Server authentication section

You can also use the following SQL query to do the same:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

[Note: 2 indicates mixed mode authentication. 1 is for windows only authentication]

Setting a password on the sa account:

  1. Under the SQL instance, expand Security and Logins
  2. Right click on sa and click on Properties
  3. Type the new password in the Password and Confirm Password boxes

You can also use the following SQL query to do the same:

ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF

[Note: CHECK_POLICY=OFF ensures that Windows password policies of the computer on which SQL Server is running will NOT be enforced on this login]

Enabling the sa account:

  1. Under the SQL instance, expand Security and Logins
  2. Right click on sa and click on Properties
  3. Click on Status on the left pane
  4. Click on Enabled under the Login section

You can also use the following SQL query to do the same:

ALTER LOGIN [sa] ENABLE

Question

How to change a password for the `sa` user in MS SQL?

Answer

This can be done using the osql utility in a command prompt or using Microsoft SQL Management Studio.

1. Using the command prompt:

  • For a local MS SQL server use the following command: 

    osql -E -Q "exec sp_password NULL,'new_password','sa'"

  • For a remote MS SQL server use the following command: 

    osql -S servername_or_address -E -Q "exec sp_password NULL,'new_password','sa'"

2. Using Microsoft SQL Management Studio:

  • Open Microsoft SQL Management Studio. It can be done from the command line by executing ssms.exe .
  • Login into Microsoft SQL Management Studio with Administrator user credentials.
  • Go to Security > Logins > sa > properties .
  • Change the password.

Note: These actions are possible for Administrator account only.

If Windows administrator does not have permissions to reset 'sa' user password, follow instructions from the article: Connect to SQL Server When System Administrators Are Locked Out

Categories

Recent posts