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):

  1. Enable TCP/IP connections
  2. Enable mixed-mode authentication
  3. 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:

SQL Server Network Configuration

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):

SQL Server error 18456

 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:

SQL Server Mixed-Mode Authentication

 By default (again, presumably for security reasons) only Windows authentication is enabled.


There are no comments

Add your comment