30

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

Prashant
  • 937
  • 1
  • 10
  • 23
  • Are trusted connections working? – Chris S May 06 '10 at 12:54
  • I am not sure what you mean, but I am not trying to connect to the SQL Server remotely. I have remotely logged into the instance, and am trying to create databases on the remote instance itself. It is as good as trying to create databases on the SQL server installed on my desktop. – Prashant May 06 '10 at 13:06

6 Answers6

47

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)
Joel
  • 19,175
  • 2
  • 63
  • 83
jkr
  • 471
  • 1
  • 3
  • 2
  • Also, if you are utilizing local ephemeral storage, you’ll need to change your mssqlserver service to run under LocalSystem account, or add the log on user for the SQL service to the allowed all security for the drives. – Nick Dec 06 '16 at 20:45
  • Is there an instance of SQL Server installed when you use an AMI with SQL Server Express? – Rob L Feb 14 '17 at 14:34
  • Perfect! I just launched an EC2 using Windows_Server-2019-English-Full-SQL_2017_Web-2019.09.11 - ami-0900f3b560fc718bd. I followed the steps above to get sa access. – Lei Shi Oct 10 '19 at 21:34
13

On a fresh Amazon EC2 "small" instance, as of 2010-05-31, the SQL services are disabled by default.

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

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.

Update

Another reason why SQL is disabled, by default:

I'm pretty sure they they want to avoid being "that" company that lets loose another
"code red" or "sql slammer" worm - @Dan Esparza

Contango
  • 76,540
  • 58
  • 260
  • 305
  • 2
    I'm pretty sure they want to avoid being 'that' hosting company that lets loose another 'code red' or 'sql slammer' worm. (http://en.wikipedia.org/wiki/SQL_Slammer for more info) – Dan Esparza Oct 06 '10 at 20:14
  • 1
    Wrong. Amazon has hardware firewalling on all of the ports by default (you have to open them up manually), so thats not the problem, the real problem is that Amazon EC2 doesn't provide enough RAM for a small instance to run SQL properly at all. – Contango Oct 07 '10 at 11:02
  • 2
    This is *definitely* not the case with more recent instances. JKR's answer below is the one to use. – davewasthere Oct 01 '12 at 11:04
2

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.

IMHO
  • 769
  • 2
  • 6
  • 26
1

The short answer for how to get into SQL Server is you must RDP to the server using the Administrator account for that server (not a domain admin) and then connect to SQL using Windows Authentication. From there, you can then enable sa or add any other accounts.

Jesse A.
  • 11
  • 2
0

When running the Microsoft SQL Server Management Studio, make sure you run it as administrator even if you have administration rights.

Leon
  • 11
0

You could not view the password directly as it would be present under syslogins in an encrypted format.

But, You can reset the pwd.

Login to SSMS with "Windows Authentication" --> Expand "Security--> Logins"

Under "Logins", Select the appropriate LoginName and open the properties window. There you can Provide a new Password to your login.

Ravi
  • 1
  • 1