Amazon EC2 Instance - How to find SQL Server Password

Question

I have created an Amazon EC2 Instance that provides Windows Server 2008 with SQL Sever 2008 pre-installed. Now in order to use the SQL Server for creating databases, or restoring backups of the databases that I have on my local machine, I need the "sa" password for SQL Server 2008.

I have tried using the following but with no luck:

1. sa
2. password
3. "blank password"
4. "same password as the admin password for my EC2 instance"

Could someone please guide me as to how to get started with using the Amazon EC2 Datacenter with respect to the "sa" password.

Thanks

Answer 1

A bit of an update as of 2010-07-01, using a fresh Amazon image of Windows 2008 Datacenter (with SQL Server Express), SQL server was running, but the sa account was neither enabled nor with a known password. Follow these steps to fix it:

1. Use SQL Server Management Studio to connect to your data base server using Windows Authentication (Administrator user, for instance).
2. Expand the Security and Logins groups.
3. Open the sa account.
4. On the default screen ("General") set a new Password as you see fit.
5. Select the "Status" screen on the left.
6. Set the "Login:" option to "Enabled".
7. Click the OK button.
8. Right-click the root node (this will name your SQL server) and select Properties.
9. Select the "Security" screen on the left.
10. Set "Server authentication" to "SQL Server and Windows Authentication mode"
11. Restart the SQL Server service.
12. Enjoy your new-found sa access! (finally)