Amazon EC2 Instance - How to find SQL Server Password

Question

Hi

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

RDC to the server under Administrator and connect to MSSQL instance using Enterprise Studio, using trusted connection. Then go to Security and set sa password to what you need it to be.

Answer 2

On a fresh Amazon EC2 "small" instance, as of 2010-05-31, the SQL services are disabled by default (why, why, why Amazon - thats crazy!).

Fix: Enable any service starting with "SQL".

1. "Start..Administrator Tools..Services".
2. Select every service starting with "SQL", set the startup type to "Automatic".
3. Start every service starting with "SQL".
4. Some services will refuse to start, don't worry about this.

Now, you can go back into "SQL Server Management Studio", and everything will work nicely.

Update: On 2010-05-31, I tested the instructions above, with a fresh Amazon EC2 instance with SQL Server installed, and everything worked just as I described.

Update: 2010-10-07: The real reason Amazon EC2 disables SQL Server, by default, on a small instance is that it doesn't actually have enough RAM to run things properly. I'm not even sure why they installed it in the first place. From bitter experience, I'd recommend that you run MySQL, given the choice, as its faster and lighter, or upgrade to a decent sized instance.

Answer 3

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)