Topic: Error when trying to change server authentication in Sql Manager Express

[lanks]

After my previous problem with Sql was solved, my database had to be moved (the server was replaced).
In SQL Manager express i am trying to change the server authentication method to 'SQL Server and Windows Authentication mode', i click 'OK' and it asks for a password. I put in the desired password twice and i am given this error

Operation is not valid due to the current state of the object. (Microsoft.SqlServer.Express.SqlManagerUI)

I have searched for an answer but the few that received an answer didn't help.

[Tuoni]

Is it fully service packed?  That seems like a bug to me.

Try:
Checking most up-to-date service packs
Stopping/starting the SQL service before changing its authentication mode (could maybe be a client who hasn't disconnected? - or the service could not be running?)

If that doesn't work, you can change it in the registry:

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

New value: LoginMode =2

As with all registry changes, only try this if you're confident, make backups of any keys you change, I'm not held responsible etc etc.

Let me know

Edit: As an aside, are you aware that Express doesn't allow remote connections by default?  (I'm assuming here that you're going from SQL Server to an express edition)  Ignore that question if that's not the case

[lanks]

Is it fully service packed?  That seems like a bug to me.

Try:
Checking most up-to-date service packs

Stopping/starting the SQL service before changing its authentication mode (could maybe be a client who hasn't disconnected? - or the service could not be running?)

If that doesn't work, you can change it in the registry:

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

New value: LoginMode =2

As with all registry changes, only try this if you're confident, make backups of any keys you change, I'm not held responsible etc etc.

Let me know

Edit: As an aside, are you aware that Express doesn't allow remote connections by default?  (I'm assuming here that you're going from SQL Server to an express edition)  Ignore that question if that's not the case

It is fully updated.

If i stop the service i was unable to access the properties menu to change its authentication mode.

When i went into the registry, LoginMode already equaled 2.

I am going from express edition to express but the server computer has been replaced. It has been fully updated like the previous server which didn't have this problem.

[Tuoni]

It is fully updated.

OK.

If i stop the service i was unable to access the properties menu to change its authentication mode.

Sorry, I meant restart the service (though I'm guessing you did that) so that rules out any dodgy clients/half-started service

When i went into the registry, LoginMode already equaled 2.

That's odd, that means that login mode is set to the authentication mode as far as the server itself is concerned - maybe it's a problem with the client?

I am going from express edition to express but the server computer has been replaced. It has been fully updated like the previous server which didn't have this problem.

Ok, was just checkin'

try this:

Hi I had the same problem and resolved it by resetting the SA password before attempting to change the authentication mode by the following stored proc:

   1.  Connect to the SQL server
   2.  Open a new query window against the master database
   3.  execute the stored procedure sp_password specifying the @loginname param as 'sa' and the @new param with your new sa password.
   4.  via the gui change the mode to mixed (NB I wasn't prompted for a password this time)

Note:  MS suggest sp_password will dissappear and to use 'ALTER LOGIN' instead.  This didnt work for me.

I assume the initial password prompt and the lack of it after changing it via the stored proc means there was no initial password set.

Hope this helps

Let me know...