322336 how to: Verify and change the MSDE System Administrator Password

This article discusses how to change SQL Server SA(System Administrator) password step.

You can configure Microsoft SQL Server Desktop Engine (MSDE) version 2000 or earlier to run in Hybrid Authentication mode.SAThe account is created during installation andSAHas full permissions in the SQL server environment. By default,SAThe password is null unless you change the password when running the MSDE installer. To meet the requirements of the best security solutionSAChange the password to a strong one.

Back to Top

How to verify that the SA password is empty

1.	Open the Command Prompt window in the host computer of the MSDE instance you are connecting.
2.	Type the following command at the command prompt and press Enter: Osql-U sa In this way, you can useSAThe account is connected to the local default instance of MSDE. To connect to the named instance installed on your computer, type: Osql-U sa-s servername/InstanceName The following prompt appears: Password:
3.	Press enter again. This operation will beSAPass a null (null) password. If the following prompt appears after you press enterSAThe account has no password: 1> To meet the requirements of the security solution, Microsoft recommends that you create a non-empty strong password. However, if you receive the following error message, the password you entered is incorrect. This error message indicates thatSAPassword created for the Account: "Login Failed for user 'sa '." The following error message indicates that the computer running SQL Server is set to Windows authentication only: Login Failed for user 'sa '. Reason: not associated with a trusted SQL server connection. Unable to verify in Windows Authentication ModeSAPassword. However, you can createSAPasswordSAThe account remains secure when the Authentication mode is changed to hybrid mode in the future. If you receive the following error message, it indicates that SQL Server may not be running, or you may provide an error name for the name instance of the installed SQL Server: [Shared Memory] SQL server does not exist or Access denied. [Shared Memory] connectionopen (connect ()).

Back to Top

How to change SA Password

1.	Open the Command Prompt window in the host computer of the MSDE instance you are connecting.
2.	Type the following command and press Enter: Osql-U sa InPassword:At the prompt, press enter (if the password is empty) or enter the current password. In this way, you can useSAThe account is connected to the local default instance of MSDE. To use Windows authentication for connection, type this command: UseOsql-e
3.	Type the following commands in each individual line and press Enter: sp_password @old = null, @new = 'complexpwd', @loginame ='sa' go Note:: Make sure to replace "complexpwd" with a new strong password. Strong passwords include a combination of letters, numbers, special characters, and uppercase/lowercase characters. You will receive the following prompt, indicating that the password has been changed successfully: Password Changed.

Back to Top

How to determine or change the Authentication Mode

Important: This article contains information about modifying the registry. Before modifying the registry, you must back up the registry and know how to restore the Registry in case of a problem. 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 Microsoft Windows registry description

Warning: Improper use of the Registry Editor can cause serious problems. You may need to reinstall the operating system. Microsoft cannot guarantee that you can solve problems caused by improper use of the Registry Editor. You are at your own risk to use the Registry Editor.

If you cannot determine how to verify the Authentication Mode installed by MSDE, you can view the corresponding registry key. By default, for Windows authenticationLoginmodeSet the value of the registry subitem to 1. If mixed-mode authentication is enabled, this value is 2.

•	LoginmodeThe location of the sub-item depends on whether you install MSDE as the default MSDE instance or a named instance. If MSDE is installed as the default instanceLoginmodeThe subitem is located in the following registry subitem: HKLM/software/Microsoft/MSSQLServer/loginmode
•	If MSDE is installed as a named instanceLoginmodeThe subitem is located in the following registry subitem: HKLM/software/Microsoft SQL Server/% InstanceName %/MSSQLServer/loginmode

Note:: Before switching the Authentication mode, you must setSAPassword to prevent potential security vulnerabilities from being exposed.

For other information, click the following article number 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 hybrid mode to integrated (Windows) Authentication mode, follow these steps:

1.	To stop MSSQLServer and all other related services (such as SQLServerAgent), openServiceProgram.
2.	Open Registry Editor. To open the Registry Editor, clickStartAndRunAnd then type "regedt32" (excluding quotation marks) ClickOK.
3.	Find one of the following sub-items (depending on whether MSDE is installed as the default MSDE instance or as a named instance ): HKEY_LOCAL_MACHINE/software/Microsoft/MSSQLServer -Or- HKEY_LOCAL_MACHINE/software/Microsoft SQL Server/<Instance name>/MSSQLServer/
4.	In the right pane, double-clickLoginmodeSubitem.
5.	InDWORD EditorIn the dialog box, set the value of this subitem to 1. Make sure you have selectedHEXOption, and then clickOK.
6.	Restart the MSSQLServer and SQLServerAgent services for the change to take effect.

Back to Top

The best security solution for SQL Server Installation

Each of the following items will enhance the system security, and they all belong to the standard security "Best Solution" installed on any SQL Server ".

•	Use a non-empty passwordSALog on to your account. Some worms onlySAThe Logon account takes security measures. For other information, click the following article number to view the article in the Microsoft Knowledge Base: 313418 PRB: insecure SQL Server with an empty (null) SA password will give the worm a chance Therefore, make sure that the built-inSAThe account has a strong password and must follow the suggestions in the "System Administrator (SA) Logon" topic of SQL Server books online (even if you have never directly usedSAAccount ).
•	Block port 1433 on the Internet gateway, and assign the SQL Server listener to the slave port.
•	If port 1433 must be used on the Internet gateway, enable inbound and outbound filtering to prevent misuse of the port.
•	Run the SQL Server service and SQL Server proxy under the Microsoft Windows NT account rather than the local system account.
•	Enable Microsoft Windows NT authentication, and then enable audit for successful and failed logon. Then, stop and restart the MSSQLServer service. Configure your client to use Windows NT authentication.

For more information about how to use an empty SA password, click the following document number to view the article in the Microsoft Knowledge Base:

313418 PRB: insecure SQL Server with an empty (null) SA password will give the worm a chance

For more information about SQL Server 2000 Service Pack 1 subsequent version behavior changes when the Authentication mode is changed, click the following article number 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