SQL Server Express Install via third party products
Posted by decipherinfosys on January 2, 2010
Got a call a few minutes back from a client of ours to whom we provide remote DBA services. They had installed a third party product on a test server which installs a SQL Server Express Edition for it’s back-end as part of the install. The install was not able to progress further because of an error about authenticating a SQL Server login account. They were using the built in sa (system administrator) login 
Couple of things that had to be done in order to get them up and running on this test server:
a) When SQL Server Express gets installed, by default, only the Windows Authentication mode is enabled and not “SQL Server and Windows Authentication” mode.
b) Once you fix the above, you would then need to assign a password for the “sa” builtin account. By default, in SQL Server Express install, it will be NULL.
c) Once that is done, then you need to enable the “sa” login.
Post that, you can proceed further with creation of additional logins and setting up the security and other configuration options. Let’s quickly cover the three steps from above.
For #a, you can easily change the authentication mode using the steps that we had outlined in an earlier post – here.
For #b, you can connect to the instance and change the password using the sp_password command. Or alternatively, you can connect through SSMS and change it (Click on Security/Logins –> Right click on “sa” login and select “Properties” and you can set it up there under the General Page).
For #c, You can simply use the Alter command to enable the login or can also do it from SSMS (Click on Security/Logins –> Right click on “sa” login and select “Properties” and you can set it up there under the Status Page). We had covered disabling an account in a previous post – you can simply replace “Disable” with “Enable” in that post and the same steps will apply.
Resources:
