Trying to set up an Umbraco installation, using a Windows Server 2008 + SQL Server Express AMI on Amazon EC2, I kept getting Error number 18456 when trying to log in as ‘sa’ with the SQL Server Management Studio. This is the log of the error:
Cannot connect to IP-0AD1A3A5\SQLEXPRESS.
===================================
Login failed for user 'sa'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
Server Name: IP-0AD1A3A5\SQLEXPRESS
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
The stacktrace wasn’t helpful, since it basically showed exceptions being thrown when trying to login.
What made it bizarre was that I could look at the database if I logged into SQL Server Management Studio using Windows Authentication.
These are all the things I checked:
- Server Manager -> Configuration -> Services -> SQL Server, SQL Server Browser, started
- SQL Server Management Studio -> Security Login -> sa -> Properties -> Status -> Login, enabled
- SQL Server Management Studio -> Security Login -> sa -> Properties -> General -> enter a new password
- SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQLEXXPRESS -> TCPIP, enabled
- Windows Firewall -> Inbound Rules -> SQL Server Inbound, enabled
At this point, it might be obvious you what I did wrong: SQL Server Database Engine is set to Windows Authentication mode when it should be set to SQL Server and Windows Authentication mode. To fix this:
- SQL Server Management Studio -> Properties -> Security -> select SQL Server and Windows Authentication mode
- Restart database server
Then you can login as ‘sa’. Wohoo!
