sa's password in SQL Server 2008 R2 Express

Question

Hi,

In the SQL Server 2005 Express I can chose  the expert mode during the installation. This enables me to set the password  of the user “sa”.

Now I tried to install SQL Server 2008 R2 Express but I had no possibility to change / set the password of the user “sa”.

I read in the internet that the default-password of the user “sa” should be a blank password. Since in SQL Server 2008 R2 Express there was no way to set / change “sa”’s password I tried to login with a blank password. But this also didn’t worked.

So my questions are:

1.    Where during the SQL Server 2008 R2 Express setup can I set / change the “sa”’s password.
2.    Where in the SQL Server 2008 R2 Express can I set / change the “sa”’s password after the installation.
3.    What’s the default password of “sa” in SQL Server 2008 R2 Express?
4.    Why a blank password for the unmodified “sa” account didn’t worked? 

Thank in advance for all your helpful answers.

Answer 1

SQL Server 2008 has better security out of the box. Sa account has been disabled out by default.

Speaking of the installation, SQL Server 2008 allows you to set authentication mode (Windows or SQL Server) during the installation process. You will be forced to choose the strong password for sa user in the case if you choose sql server authentication mode during setup.

If you install SQL Server with Windows Authentication mode and want to change it, you need to do 2 different things:

1. Go to SQL Server Properties/Security tab and change the mode to SQL Server authentication mode

2. Go to security/logins, open SA login properties

a. Uncheck "Enforce password policy" and "Enforce password expiration" check box there if you decide to use weak password

b. Assign password to SA user

c. Open "Status" tab and enable login.

 

I don't need to mention that every action from above would violate security best practices that recommend to use windows authentication mode, have sa login disabled and use strong passwords especially for sa login.

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

You need to use a strong passwod for SQL Server sa account.

>>I read in the internet that the default-password of the user “sa” should be a blank password. Since in SQL Server 2008 R2 Express there was no way to set / change “sa”’s password I tried to login with a blank password. But this also didn’t worked.

I am sorry it is completely wrong

1.    Where during the SQL Server 2008 R2 Express setup can I set / change the “sa”’s password.

You can setup Sa password during the setup where you choose authentication mode (windows or sql server authentication) it requires you to supply atleast one sa password.

2. Where in the SQL Server 2008 R2 Express can I set / change the “sa”’s password after the installation.

using T-SQL

Use Master
Go

ALTER LOGIN [sa] WITH PASSWORD=N'StrongPassword'
Go 

In order to reset 'sa' password using management stdio, you still need to login as sysadmin fixed server role using windows authentication....

there is a possiblity that you may not even login as syadmin thourgh Windows Authentication (By default SQL 2008 removed local adminisrators from sysadmin roles unless you explictly add them during the setup)

in that case

you can put the SQL Server in single user mode and then any member of comuter's local administrator can gain access to SQL server as sysadmin.

Connecting to SQL Server When System Administrators Are Locked Out

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

 Here is another blog post that you can also use to gain initial aaccess to your SQL server..

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

3. What’s the default password of “sa” in SQL Server 2008 R2 Express?
there is no such thing.

4. Why a blank password for the unmodified “sa” account didn’t worked?

SQL Server will not allow you create a login with blank passwords. Many moons ago (SQL 2000 time) there was a nasty bug in the setup that allowed a blank password for SA (created havoc by Voyager Alpha Force and many others) but Microsoft fixed in one of the service packs since then SQL Server does not allow blank passwords.

Answer 3

Thanks a lot for those fast answers.

 

I want to try  "Dmitri Korotkevitch" ' s way to change sa's password.

 

But I wasn't able to find:

    >>SQL Server Properties/Security tab <<

Where is this tab?

 

I looked in

      * SQL Server Installation Center

      * SQL Server Configuration Manager

      * The properties of the service "SQL Server".

 

But I did not saw >>SQL Server Properties/Security tab <<.

 

Where is this tab?

 

 

Thanks in advance for your answers

Answer 4

try this :

open the command window (dos prompt) >> OSQL -Sserver\instancename -E >> hit enter >> ALTER LOGIN [sa] WITH PASSWORD=N'StrongPassword' >> GO >>Alter Login SA Enable >> go

NOTE : if you have a default instance there no need to use -S option

Best Regards

---

Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

Answer 5

try this :

open the command window (dos prompt) >> OSQL -Sserver\instancename -E >> hit enter >> ALTER LOGIN [sa] WITH PASSWORD=N'StrongPassword' >> GO >>Alter Login SA Enable >> go

NOTE : if you have a default instance there no need to use -S option

Best Regards

---

Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

forgot to add that you need to give this command as well after enabling the SA password:

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

Your registry settings might be different so change the registry path please.

---

Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

Answer 6

I want to try  "Dmitri Korotkevitch" ' s way to change sa's password.

 

But I wasn't able to find:

    >>SQL Server Properties/Security tab <<

Where is this tab?

Open management studio, connect to the server with windows authentication, select the server and open context menu (right mouse click).

 

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 7

You may need to download the free version of SQL Server Management Studio (SSMS), from here https://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displayLang=en.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 8

Thanks for all those fast answers.

 

When I tried to install the SSMS in the feature selection dialog everything is gray/ disabled. When I press the select all or the unstelect all button nothing changes. The Features list contains the following features:

   Instance Features

   Shared Features

       SQL Client Connectivity SDK

   Redistributable Features

 

Before the item "SQL Client Connectivity SDK" there is an checked check box which is gray / disabled. Therefore I can't uncheck it.

 

In the bottom of this dialog the following error message is displayed:

   >> To continue, select a feature to install. If all features are already selected, these features are

         installed. You can not add additional features. <<

 

When I press the next button at the bottom of this dialog a error message box pops up and referes to the error message displayed in the bottom of the dialog.

 

Since everything is disabled I can't change / unselect / select something in this dialog.

 

     * What's wrong?

     * How can I finish successfully the SSMS setup?

 

P.S: I am using SQL 2008 R2 Express

 

 

==============================================

 

I also thried >> OSQL -Sserver\instancename -E<<.

 

This didn't worked.

 

When I typed   osql -L    no server    is displayed.  I checked in the services.msc the sql server services ( it is running ).

 

Why doesn't > OSQL -Sserver\instancename -E<<. work?

Why does osql -L display   no server  ( even the sql server services is running ) ?

 

 

Thanks in advance for all your answers.

 

Answer 9

Hi,

To install shared features like Management Studio or Intergration Services, please select New installation or add shared features instead of Add feaures to an existing instance of SQL Server 208 R2 in the Installation Type page.

You could download SQL Server 2008 R2 Management Studio Express at https://www.microsoft.com/downloads/en/details.aspx?FamilyID=56ad557c-03e6-4369-9c1d-e81b33d8026b.

Hope this helps.

Thanks,
Chunsong

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 10

Hi All!

My Sql Server 2008 Management Studio forgets passwords. How can I solve it?

Thanks a lot.

Answer 11

Hi All!

My Sql Server 2008 Management Studio forgets passwords. How can I solve it?

Thanks a lot.

If you don't remember that login password any more, please let your SQL Server administrator help you reset the password.

---

Best Regards
Alex Feng | Forum Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail].

Answer 12

I am not sure whether you found a solution for sa password reset in 2k8 express. May be useful to others.

Steps to reset the password:

1. Go to SQL Server Properties/Security tab and change the mode to SQL Server authentication mode

2. Go to security/logins, open SA login properties

a. Uncheck "Enforce password policy" and "Enforce password expiration" check box there if you decide to use weak password

b. Assign password to SA user

3. In SQL 2k8, we may get the following error when we reset the password,

"Cannot set a credential for principal 'sa' . (Microsoft SQL Server, Error: 15535)"

Resolution:

but this can be fixed by selecting the "Map To Credential" check box on the General tab of the Login Properties - sa dialog box.

4. You may get the following error after above step,

“User is not associated with a trusted sql server connection"

Resolution:

In SQL Server Management Studio, Right-click the Server name, select Properties > Security

Under Server Authentication, select SQL Server and Windows Authentication Mode

The server must be stopped and re-started before this will take effect.

5. Make sure the sa (at status tab of sa properties) is enabled after restart the server.

Answer 13

I am not sure whether you found a solution for sa password reset in 2k8 express. May be useful to others.

Steps to reset the password:

1. Go to SQL Server Properties/Security tab and change the mode to SQL Server authentication mode

2. Go to security/logins, open SA login properties

a. Uncheck "Enforce password policy" and "Enforce password expiration" check box there if you decide to use weak password

b. Assign password to SA user

3. In SQL 2k8, we may get the following error when we reset the password,

"Cannot set a credential for principal 'sa' . (Microsoft SQL Server, Error: 15535)"

Resolution:

but this can be fixed by selecting the "Map To Credential" check box on the General tab of the Login Properties - sa dialog box.

4. You may get the following error after above step,

“User is not associated with a trusted sql server connection"

Resolution:

In SQL Server Management Studio, Right-click the Server name, select Properties > Security

Under Server Authentication, select SQL Server and Windows Authentication Mode

The server must be stopped and re-started before this will take effect.

5. Make sure the sa (at status tab of sa properties) is enabled after restart the server.

---

romnciat

Answer 14

I had similar problem , I just ran sql studio as administration and logged in as windows authentication , then only was able to follow above mentioned steps .

Answer 15

Hi all, I was given a laptop with SQL Server 2008 R2 Express edition installed in it.

I tried logging in using SQL Server Authentication, USer:  sa, it doesnt work. I can login using Windows Authentication. I dont know whether they have only Windows authentication or Mixed mode and sa user.

I have tried above methods. results are as fellows:-

@Chirag:-Tried the following method

 Use Master

Go

ALTER LOGIN [sa] WITH PASSWORD=N'StrongPassword'
Go 

Result:-  

Msg 15151, Level 16, State 1, Line 1

Cannot alter the login 'sa', because it does not exist or you do not have permission.

@Chandruu:- Tried Steps 1,2 it doesnt allow me to do that.

 

As per my present situation, please suggest what can be done. Also, if you could answer the following questions:-

1. Is it possible that it has only Windows authentication and there is no 'sa' user for it.

2. How to detect 'sa' user if there is one.  On Security\Logins\sa is present.

 

I dont want to reinstall SQL SERVER express2008 R2 again. Please if you could help.

 

Thanks in advance,

DCS

 

Answer 16

Hello,

I probably hit an axe on my own feet..

I logged in with Mixed mode authentication and deleted the users of sql server. Now I am neither able to create new user, nor reset the password for sa user. It throws error saying that User does't have permission. 

Now I have reinstalled sql server and while installing it, I added two extra users and set the password of sa user. Hope it will solve my problem. But is there any other way to add SysAdmin user if all other users are deleted?

Thanks in advance 

---

Aditya N B

Answer 17

Try with http://stackoverflow.com/questions/2833373/sql-server-2008-database-engine-login-failed-for-administrator-user-in-windows-7

Usually, SQL-Express is installed as a named instance; therfore, you'll have to replace the name of the service MSSQLSERVER (the name used for the default instance of SQL-Server) with the name of the service running the instance of SQL-Server Express; which is probably mssql$sqlexpress (or mssql$ followed by the name of the instance if you have chosen something else than SQLEXPRESS for the instance name).

Answer 18

I am not sure whether you found a solution for sa password reset in 2k8 express. May be useful to others.

Steps to reset the password:

1. Go to SQL Server Properties/Security tab and change the mode to SQL Server authentication mode

2. Go to security/logins, open SA login properties

a. Uncheck "Enforce password policy" and "Enforce password expiration" check box there if you decide to use weak password

b. Assign password to SA user

3. In SQL 2k8, we may get the following error when we reset the password,

"Cannot set a credential for principal 'sa' . (Microsoft SQL Server, Error: 15535)"

Resolution:

but this can be fixed by selecting the "Map To Credential" check box on the General tab of the Login Properties - sa dialog box.

4. You may get the following error after above step,

“User is not associated with a trusted sql server connection"

Resolution:

In SQL Server Management Studio, Right-click the Server name, select Properties > Security

Under Server Authentication, select SQL Server and Windows Authentication Mode

The server must be stopped and re-started before this will take effect.

5. Make sure the sa (at status tab of sa properties) is enabled after restart the server.

It works =)!!!!!!!!! Thanks man =)

Answer 19



just run this script to solver the issue...

http://archive.msdn.microsoft.com/addselftosqlsysadmin/

then you will have full sys admin priveleges to the database engine.









Answer 20

Hi guys,

I am new to the forums, so hello everyone.

I just have installed Windows Small Business Server 2011 Standard which has preinstalled SQL2008R2 and SharePoint.

There are SHAREPOINT and SBSMONITORING instances present on my machine.

I need to install 3rd party application that is compatible with SQL 2008R2, but I simply can't access my SQL engine. I do not know the SA password, but I assume from that thread history, that the SA account is disabled by default. I tried to login using windows authentication but there is a timeout as well. How may I login to the SQL as an administrator? My account is system administrator level. Standard "Administrator" account for the Win2k8R2 is also disabled. Should I use this one?

Kind regards,

The guy looking for some help.

Answer 21

You should start a new thread for your problem.

But until then, start by running through this short tutorial Tutorial: Getting Started with the Database Engine It covers the basics and may resolve your problem.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 22

OK I will. Thought that maybe the problem is similiar to the one here so placing it alltogether might be a good idea.

Actually I solved some of my problems, if not all of them.

Thanks for your response and the link. I see I can get a lot of knowledge from it.

Answer 23

This worked for me!  Thanks!