How To Verify and Change the MSDE System Administrator Password

Backup And Restore…

OSQL -Usa -PmyPassword -n -Q "BACKUP DATABASE {databasename} TO DISK = 'c:\msdb.dat_bak'"

OSQL -Usa -PmyPassword -n -Q "RESTORE DATABASE {databasename} FROM DISK='c:\msdb.dat_bak'"

OR Backup and Restore….

Backup and Restore a Database

To protect the data in SQL Server databases, back up each database periodically. In the event of a system problem, such as the failure of a computer or hard disk, you can restore the database from the last backup after fixing the system. The topic in SQL Server Books online called "Database Backups" explains how to do this. The Transact-SQL links at the bottom of the topic indicate you use the BACKUP statement to make a database backup and the RESTORE statement to restore a database using the previous backup:

USE MyDB

BACKUP DATABASE MyDB TO TAPE = '\\.\tape0' WITH FORMAT, NAME = 'Full Backup of MyDB'

You can later rebuild MyDB using that backup:

USE master

RESTORE DATABASE MyDB FROM TAPE = '\\.\tape0'

Web.Config file settings to impersonate user…

Web.config file settings for the connection string…

</appSettings>

'Get the connection string....

Dim connstring As String

connstring = ConfigurationSettings.AppSettings("ConnectionString")

'*******************************************************************

objConn = New System.Data.sqlclient.SqlConnection(connstring)

I forgot/lost the sa password. What to do?

Forgot or lost your sa password? Never changed. Don't worry, there is a

way out :)

Login to the SQL Server computer as the Administrator of that computer.

Open Query Analyzer and connect to SQL Server using Windows NT

authentication. Run sp_password as show below to reset the sa password:

To login

go to windows run command

type in Osql -E

This logs in through Windows security , if startup window just flashes

restart SQL Server

from tray icon.

enter next two lines to change password to willneverforget

EXEC sp_password @new = 'will_never_forget_again', @loginame = 'sa'

How To Verify and Change the MSDE System Administrator Password

View products that this article applies to.

This article was previously published under Q322336

IN THIS TASK

SUMMARY

How to Verify If the SA Password is Blank

How to Change Your SA Password

How to Determine or Change Your Authentication Mode

Security Best Practices for a SQL Server Installation

REFERENCES

SUMMARY

This step-by-step article discusses the steps you can use to change the SQL Server sa (system administrator) password.

You can configure Microsoft SQL Server Desktop Engine (MSDE) versions 2000, or earlier, to run in Mixed Authentication mode. The sa account is created during the installation process and the sa account has full rights in the SQL Server environment. By default, the sa password is blank (NULL), unless you change the password when you run the MSDE Setup program. To conform with the best security practices, you must change the sa password to a strong password at the first opportunity.

back to the top

How to Verify If the SA Password is Blank

1. On the computer that is hosting the instance of MSDE to which you are connecting, open a command prompt window.

2. At the command prompt, type the following command, and then press ENTER:

osql -U sa

This connects you to the local, default instance of MSDE by using the sa account. To connect to a named instance installed on your computer type:

osql -U sa -S servername\instancename

You are now at the following prompt:

Password:

3. Press ENTER again. This will pass a NULL (blank) password for sa.

If you are now at the following prompt, after you press ENTER, then you do not have a password for the sa account:

1>

Microsoft recommends that you create a non-NULL, strong password to conform with security practices.

However, if you receive the following error message, you have entered an incorrect password. This error message indicates that a password has been created for the sa account:

"Login Failed for user 'sa'."

The following error message indicates that the computer that is running SQL Server is set to Windows Authentication only:

You cannot verify your sa password while in Windows Authentication mode. However, you can create a sa password so that your sa account is secure in case your authentication mode is changed to Mixed Mode in the future.

If you receive the following error message, SQL Server may not be running or you may have provided an incorrect name for the named instance of SQL Server that is installed:

[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).

back to the top

How to Change Your SA Password

1. On the computer that is hosting the instance of MSDE to which you are connecting, open the command prompt window.

2. Type the following command, and then press ENTER:

osql -U sa

At the Password: prompt, press ENTER if your password is blank or type the current password. This connects you to the local, default instance of MSDE by using the sa account. To connect by using Windows authentication, type this command: use osql -E

3. Type the following commands, on separate lines, and then press ENTER:

4. sp_password @old = null, @new = 'complexpwd', @loginame ='sa'

5. go

NOTE: Make sure that you replace "complexpwd" with the new strong password. A strong password includes alpha-numeric and special characters, and a combination of upper and lower case characters.

You will receive the following informational message, which indicates that your password was changed successfully:

Password changed.

back to the top

How to Determine or Change Your Authentication Mode

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

If you are not sure how to verify the authentication mode of your MSDE installation, you can check the corresponding registry entry. By default, the value of the Windows LoginMode registry subkey is set to 1 for Windows Authentication. When Mixed Mode authentication is enabled, this value is a 2.

The location of the LoginMode subkey depends on whether you installed MSDE as the default MSDE instance or as a named instance. If you installed MSDE as the default instance, the LoginMode subkey is located in the following registry subkey:

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:

HKLM\Software\Microsoft\Microsoft SQL Server\%InstanceName%\MSSQLServer\LoginMode

NOTE: Before you switch authentication modes, you must set a sa password to avoid exposing a potential security hole.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

274773 FIX: If You Change Windows Security to Windows/SQL Security the SA Password is Blank

To switch from Mixed Mode to Integrated (Windows) authentication, follow these steps:

1. To stop MSSQLSERVER and all other related services (such as SQLSERVERAgent), open the Services applet in Control Panel.

2. Open the Registry Editor. To open the Registry Editor, click Start, click Run, and then type: "regedt32" (without the quotation marks)

Click OK.

3. Locate either of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer

-or-

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\

4. In the right-pane, double-click the LoginMode subkey.

5. In the DWORD Editor dialog box, set the value of this subkey to 1. Make sure that the Hex option is selected, and then click OK.

6. Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.

back to the top

Security Best Practices for a SQL Server Installation

Each of the items that follow will make your system more secure and they are part of the standard security "best practices" for any SQL Server installation.

Secure your sa login account with a non-NULL password. There are worms that only work if you have no security for your sa login account. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

313418 PRB: Unsecured SQL Server with Blank (NULL) SA Password Leaves Vulnerability to a Worm

Therefore, to make sure that the built-in sa account has a strong password, you must follow the recommendation provided in the "System Administrator (SA) Login" topic in SQL Server Books Online, even if you never directly use the sa account.

Block port 1433 at your Internet gateways, and then assign SQL Server to listen on an alternate port.

If port 1433 must be available on your Internet gateways, enable egress and ingress filtering to prevent misuse of the port.

Run the SQLServer service and SQL Server Agent under a Microsoft Windows NT account, not a Local System account.

Enable Microsoft Windows NT Authentication, and then enable auditing for successful and failed logins. Then, stop and restart the MSSQLServer service. Configure your clients to use Windows NT Authentication.

back to the top

REFERENCES

For additional information regarding how a blank sa password can be exploited, click the article number below to view the article in the Microsoft Knowledge Base:

313418 PRB: Unsecured SQL Server with Blank (NULL) SA Password Leaves Vulnerability to a Worm

For additional information about a change in behavior with post-SQL Server 2000 Service Pack 1 when the authentication mode changes, click the article number below to view the article in the Microsoft Knowledge Base:

274773 FIX: If You Change Windows Security to Windows/SQL Security the SA Password is Blank

back to the top