Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-10-04
9
Medium Priority
?
1,101 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
LVL 34

Accepted Solution

by:
arbert earned 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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