Goal: connect to SQL Server Express 2008 database using SQL Server authenticated account "sa", rather than my Windows authenticated account.
When installing SQL Server 2008 Express, I took all default options except when filling in the Service Accounts. It did not allow me to enter "sa" as the Account Name for the "SQL Server Database Engine" service. So I found my network user {NETBIOS DOMAIN\LOGIN} and entered that id along with my network password. I then chose the default "Startup Type" of Automatic. That allowed me to complete the installation.
Normally, on Windows XP or Windows 7, SQL Server Express installs fine. This install was on Windows Server 2008.
When I created a new database, I noticed my network name showed up under the database properties. Database Properties > General > Owner. So I reset that user to "sa" for that one database. Creating new databases defaults it to the value I initially set during install.
I used this command to reset that user.
EXEC sp_changedbowner 'sa'
The latest version of SQL Server Express (downloaded today) also defaulted the "sa" login to Disabled (Database Server > Security > Logins > sa > Login Properties > Status > Login: Disabled). So I enabled it.
http://sarangasl.blogspot.com/2011/06/sql-server-authentication-enabling.html
I still cannot log into the database server ({MACHINE NAME}\SQLEXPRESS) with account sa. I event reset the password (Database Server > Security > Logins > sa > Login Properties > General > Password & Confirm Password). What am I missing?
Best Answer
I had to do two things to solve it.
1.) I didn't have mixed mode turned on. I had to right click the server node in the Object Explorer and go to Server Properties.
https://stackoverflow.com/questions/2474839/unable-to-login-to-sql-server-sql-server-authentication-error-18456
2.) restarted Windows Service for SQL Server.