Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I installed SharePoint server 2010 recently on a test server. It was installed in standalone mode.

After the installation, I realized that it had installed SQL Server Express 2008 (10.0.2531.0) automatically as well.

I need to know the password for the sa user of this SQL server instance that it automatically installed. (I was not prompted for a password during the SharePoint installation)

Reason that I need the sa password of the SharePoint SQL instance:

SharePoint is working without any issues. However, I need to create a new login on the SQL server and give it permissions to some of the databases.

I can login to the SQL server using Windows Authentication. However, using this login, I do not have permissions to create a new login.

I need to create a new login in order to complete the steps mentioned here: http://www.sharepointassist.com/2010/01/29/the-local-farm-is-not-accessible-cmdlets-with-featuredependencyid-are-not-registered/comment-page-1/#comment-1566

share|improve this question
    
I think if its 2008 R2, sa is disabled by default. All you need to do is enable the sa account and set a new password. Obviously you will need to use a windows admin account for this. –  Steve Mar 19 '14 at 17:00
    
You can also look at : stackoverflow.com/questions/3781365/… –  Steve Mar 19 '14 at 17:02
    
@Steve Thanks. The sa login appears to be disabled. However, it does not allow me to enable it when logged in with the Windows account that I used while installing SharePoint. –  slayernoah Mar 19 '14 at 17:17
    
I still think you need to have a computer administrator role for this, if this is not possible then you should get someone who is to enable the sa account. –  Steve Mar 19 '14 at 17:21
    
@Steve Thanks. I will check this and let you know! –  slayernoah Mar 19 '14 at 18:59

1 Answer 1

up vote 6 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.