SQL Server 2008 Installation Notes (and error 18456)
In the past year or 2, I must've installed SQL Server 2008 Express dozens of times, and each time I seem to run into the same few configuration issues. Although there are other nuances and caveats to installing this product, the ones I'll document here top my initial check-list.
Essentially, there are 3 things that I always do on a new installation (or don't do, and then waste a half hour troubleshooting):
- Enable TCP/IP connections
- Enable mixed-mode authentication
- Disable "Prevent saving changes that require table re-creation"
Enable TCP/IP Connections
The first thing that gets me every time, is when my freshly-deployed application throws a "network-related or instance-specific error occurred while establishing a connection to SQL Server" while trying to connecting to my freshly-minted database server.
This is because SQL Server 2008, by default (presumably for security reasons), does not enable the listener on TCP port 1433. In order to enable this, you need to launch the "SQL Server Configuration Manager" under Start > Programs > SQL Server 2008 > Configuration Tools. Within this utility, select SQL Server Network Configuration > Protocols for <instance name>, and enable TCP/IP, like so:
Enable Mixed-Mode Authentication
The second thing that always gets me is the overly-generic "Login failed for user" error message. I've connected to the server using Windows authentication, created my database, and assigned a new user for use in my application, and then get the following error message (though usually in the form of a yellow-screen-of-death):
I usually end up re-checking permissions, resetting the password, trying to manually login using SSMS, even trying the sa account (which gives the same error), until I remember this setting:
By default (again, presumably for security reasons) only Windows authentication is enabled.