Microsoft SQL Desktop Engine Guide

Earlier last week, I went to one of our client site to setup an application which was done by me. The application utilizes the MSSQL database in which I have been using the MSSQL Enterprise version from the beginner. However, due to licensing issue in using the Enterpise version, the client request to use the MSDE version.

I was cracking my head to solve the "authentication" error at the site which takes around 5 hours, worst than what I expected. Then, I manage to solve it after googling for that error and came across Microsoft MSDN support website.

Crap aside, I believe if any of you wants to setup the software, below are some additional note which are good to know;

SA account password
Another simple step to make your database more secure is to verify that the SA account has a secure password. Many developers and system administrators leave the SA account password blank, which permits anyone to gain access to the database.

To change the SA account password on your MSDE database, follow these steps:1. On the computer that is hosting the instance of MSDE that you are connecting to, open a command prompt window.
2. Type the following command, and then press ENTER:

osql -U sa

This command connects you to the local, default instance of MSDE by using the SA account.
3. Type the following commands on separate lines, and then press ENTER:

sp_password null
'mynewpassword'
'sa'

Note Replace 'mynewpassword' with the new password.

Note that you receive the following message, which indicates that your password was changed successfully:
Password changed.

Read More about osql command here.

Change Windows Authentication mode to Mixed Mode
To change the value of LoginMode to 2, follow these steps:1. In Control Panel, open the Services tool, and then stop MSSQLSERVER and all other related services (such as SQLSERVERAgent).
2. On the Start menu, click Run, type regedt32, and then click OK to start Registry Editor.
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 2, verify that the Hex option is selected, and then click OK.
6. Restart the MSSQLSERVER service and the SQLSERVERAgent service for this change to take effect.


I'm blogging this to share the info I found. I believe I will come back here to search for this... :)