Problems with creating SQL server accounts with SQL express edition

I installed Visual Web Developer on my laptop running on Windows XP Professional (SP2). With this SQL server express edition too got installed. Then I installed SQL server management studio express to manage the databases

I used "windows authentication" to login the SQL server for the first time. Then I found sa password is already there. I was never given the opportunity to set this sa password during the installation. I tired to change it to my own password but I found that it's not accepting any changes.

Then I tired to create a SQL server account and try to login. I was not allowed. Then through MSDN I got to know that Express Edition does not support mixed mode authentication by default but I have to select that during the installation. But the problem is I was never given the opportunity to select this authentication mode during the install ion.

I tired changing the registry values ( set LoginMode to '2") as instructed in the MSDN to allowe mixed mode. Even after that still I'm not allowed to create sql server accounts with my own passwords. I'm allowed to create accounts but it doesn't allow me to set the password; every time I check it had replace the password I set to some unknown password which I cannot see due to masking.

Can anyone offer me some advice to understand what's happening?

aththidiyavidana:

I'm allowed to create accounts but it doesn't allow me to set the password; every time I check it had replace the password I set to some unknown password which I cannot see due to masking.

Sounds Strange Make sure you've restarted the SQL Express instance after you modify the Authentication Mode.

Did you mean the password of new created account will be replaced by an unkown string?

How do you know (Did you get an error when try to login SQL Server with the account and password you just create, or you just find the length of the password seems to be changed)?

If yes, what's error message? Something like "Login failed for user 'test'. The user is not associated with a trusted SQL Server connection" or looks like "Login failed for user 'test'. (Microsoft SQL Server, Error: 18456"?

|||

Thanks for the reply.

I have found out the reason for not getting a chance to set 'sa' password during the installation; By default it avoids the advanced configuration options. We have to uncheck the option to get the extra dialog boxes to select authentication modes. I was able to reinstall SQL Serve express with this option uncheck and managed set my own sa password.

But I still don't know why I was not allowed to change the sa password set by the installation program during my first attempt. Each time I tired to reset this password it gave me the impression that changes are accepted because no error messages were thrown. But when I accessed the properties again after the changes I realized that 'sa' password I submit had not been accepted

I don't think this is an error or a bug. I think this is how it works…it won't let me access the server using SQL server accounts or set/reset my own SQL server account passwords if "mixed mode" is not selected during the installation

Thanks again for the reply