Solved

How to reset SQL server authentication mode from windows authentication only back to mixed?

Posted on 2004-10-04
9
1,066 Views
Last Modified: 2008-01-09
I was using microsoft baseline security analyzer to check my local machine, and followed the suggestion, i changed my authentication mode to windows only, and removed buildin\administratos from sysadmin. Now I can't do anything to my local sql server database even I know exactly what my sa account password and another sysadmin account i created...... because my database refuse any connection if i try to connect as "sa".
Any idea? thanks.
0
Comment
Question by:dragonemp
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 12

Expert Comment

by:geotiger
ID: 12222894
You may have to login as SQL server service account on the server, then follow the following steps:

1. Start sql server enterprise manager
2. Expand to the server that I had made change
3. right click on it to start properties
4. click on security tab, and you will see "Authentication": * SQL server and Windows * Windows Only.
0
 

Author Comment

by:dragonemp
ID: 12222929
If I can log in as any sysadmin account then I wouldn't ask this question.....
The program is I only setup 2 sysadmin account, non of them are my local or domain account , it's just like "sa", "xxxadmin"......, after i changed to windows authentication only, none of them works.
0
 

Author Comment

by:dragonemp
ID: 12222933
"program" is "problem"
0
 
LVL 34

Expert Comment

by:arbert
ID: 12223125
You can edit the registry to change it back--here is the article:

http://www.winnetmag.com/SQLServer/Articles/ArticleID/24317/pg/2/2.html
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 12223127
Here is a recap of the paragraph:

"If you decide to follow either of those recommendations, it's essential that you first create the login with the sysadmin membership for the DBA group, then remove the BUILTIN\Administrators login. If your server's authentication mode is Windows-only and you remove all logins that have sysadmin membership before creating the login for the DBAs, you might find yourself locked out of SQL Server with no way to perform administrative tasks—such as creating new logins. If you fall into that trap, you can still change SQL Server's authentication mode to Mixed through the registry by editing the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName> \MSSQLServer\LoginMode registry subkey. Change the subkey value to 2, then restart the SQL Server service."


Brett
0
 

Author Comment

by:dragonemp
ID: 12223169
woohoo, arbert, that's exactly the answer I'm looking for, thx :)
0
 
LVL 9

Expert Comment

by:miron
ID: 12223183
well,

in order to change the settings you *do* need local or domain account with administrator privileges on this windows machine.

REG ADD HKLM\Software\Microsoft\MSSQLServer\MSSQLServer /v LoginMode /t REG_DWORD /d 0x1 /f

or manually editing this registry value to 0 on the sql server. Note, for sql server 2000 named instance registry path differs slightly, here is the link to Microsfot website. This article highlights variation in the registry path for named instance of sql server 2000

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1061.mspx

-- cheers
0
 
LVL 9

Expert Comment

by:miron
ID: 12223193
-- correction for the command line script

REG ADD HKLM\Software\Microsoft\MSSQLServer\MSSQLServer /v LoginMode /t REG_DWORD /d 0x0 /f

-- cheers
0
 
LVL 34

Expert Comment

by:arbert
ID: 12223382
"in order to change the settings you *do* need local or domain account with administrator privileges on this windows machine."

Yes, the article link I posted stated that....
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now