How to Change from Sql Server Windows mode to Mixed Mode *Using SQL* (SQL Server 2008)

Question

How to Change from Sql Server Windows mode to Mixed Mode using SQL through say QueryExpress or QueryAnalyser?

Answer 1

You need to change the LoginMode under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer where MSSQL10.MSSQLSERVER is the name of your current instance.

1 = Windows Only. 2 = Mixed.

Additional: here is a quick look on those stored procedures:

http://codebetter.com/blogs/raymond.lewallen/archive/2005/09/12/131869.aspx

Good luck

Answer 2

Mixed mode authentication is not a property within the database, but is a property read from the registry when the SQL Server Windows Service starts.

That means there is no DDL to modify authentication, but you can just set the registry entry that controls it.

If you read this blog post,

http://eduzine.edujini-labs.com/archives/24-HOWTO-Configure-Microsoft-SQL-Server-for-Mixed-Mode-Authentication.html

It shows you the registry key to alter, and also reminds you to restart the SQL Server.

Answer 3

As the others have mentioned, you need to set the MSSQLServer\\LoginMode registry key - but before you restart SQL Server for the change to take effect, set the SA password to something strong (e.g. a GUID). Newer versions may have fixed this, but IIRC with SQL 2000, the SA password would be blank by default.

e.g.

EXEC sp_password NULL, NEWID(), 'sa'