How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?

Question

I have installed SQL Server 2008 Express Edition, but by mistake I kept the Windows authentication mode.

Now I want to change that to SQL Server mixed mode. How can I do this?

score 76 · Accepted Answer · answered Sep 08 '09 at 12:15

76

You can do it with SQL Management Studio -

Server Properties - Security - [Server Authentication section] you check Sql Server and Windows authentication mode

Here is the msdn source - http://msdn.microsoft.com/en-us/library/ms188670.aspx

answered Sep 08 '09 at 12:15

Svetlozar Angelov

21,214
6
62
67

Thank you very much. Atlast I got the success after going thru the link. – priyanka.sarkar Sep 09 '09 at 05:26
2

@Sevtlozer , I am also use same link , i am also able to connect with sql server authentication on my system ,but i am not able to connect database server from networked computer . I think my sql server agen service is not start . I am not able to start that service please suggest me . – Sushant Bhatnagar Feb 17 '12 at 04:47
@Sushant Network access is disabled by default to sqlexpress, see [msdn](http://msdn.microsoft.com/en-us/library/ms165647(v=sql.90).aspx) for how to enable – Bob Vale Jul 24 '12 at 08:23
This does not work if you are unable to log into the server. – Roy B Jan 15 '16 at 02:12
Whoaaaaa!!! after spending days...the above link resolved it. I was trying to connect through Windows Authentication but lots of exceptions I came across. But this link just made my day. Thanks a ton @Svetlozar Angelov – Ram Feb 06 '17 at 17:25
When I try to change the password by selecting SQL server authentication. I am getting following error **Cannot alter the login 'sa', because it does not exist or you do not have permission. (.Net SqlClient Data Provider)** – observer May 23 '17 at 10:59

Pablo Montilla · Answer 2 · 2014-12-09T14:49:10.620

29

I had no success with other attempts on a SQL Server 2012. What I did was use SQL Server Management Studio to generate a script to change the value, and got this:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

After that, I enabled the sa account using this:

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<strongPasswordHere>' ;
GO

...then, I restarted the service, and everything worked!

edited Dec 09 '14 at 14:49

answered Dec 09 '14 at 14:25

Pablo Montilla

2,941
1
31
35

1

Thanks for this tip – John-Philip Oct 18 '16 at 09:49
1

Nice one - especially for the command line invocation for the registry change. Thanks. – Jeremy McGee Nov 13 '17 at 12:28

score 15 · Answer 3 · edited Aug 04 '12 at 15:50

15

From this: http://weblogs.sqlteam.com/peterl/archive/2008/06/19/How-to-change-authentication-mode-in-SQL-Server.aspx

One can catch that you may change it through windows registry key

(SQLEXPRESS instance):

"Software\Microsoft\Microsoft SQL Server\SQLEXPRESS\LoginMode" = 2

... and restart service

edited Aug 04 '12 at 15:50

user419420

3
2

answered Jan 17 '12 at 15:52

Laurent de LAPRADE

151
1
2

score 13 · Answer 4 · answered Sep 08 '09 at 12:15

13

Open up SQL Server Management Studio and connect to your database server.
Right Click The Database Server and click Properties.
Set the Server Authentication to SQL Server and Windows Authentication Mode.

answered Sep 08 '09 at 12:15

Restore the Data Dumps

38,967
12
96
122

When I try to change the password by selecting SQL server authentication. I am getting following error Cannot alter the login 'sa', because it does not exist or you do not have permission. (.Net SqlClient Data Provider) – observer May 23 '17 at 11:10

score 5 · Answer 5 · edited Aug 17 '14 at 03:37

From MSDN:

To change security authentication mode:

In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the SA login:

In Object Explorer, expand Security, expand Logins, right-click SA, and then click Properties.

On the General page, you might have to create and confirm a password for the login.

On the Status page, in the Login section, click Enabled, and then click OK.

score 3 · Answer 6 · edited Jul 24 '12 at 08:17

3

Open the registry and search for key LoginMode under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server

Update the LoginMode value as 2.

edited Jul 24 '12 at 08:17

Druid

6,423
4
41
56

answered Jul 24 '12 at 07:56

BMG

489
6
9

This should be the correct answer. Simple and easy -- doesn't require Management Studio overhead. – Dario Zadro Oct 31 '19 at 21:06

score 0 · Answer 7 · answered Feb 25 '17 at 03:14

0

If the problem is that you don't have access to SQL Server and now you are using mixed mode to enable sa or grant an account admin privileges, then it is far easier just to uninstall SQL Server and reinstall.

answered Feb 25 '17 at 03:14

Underverse

1,271
21
32

How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?

7 Answers7

Linked