Solved

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

Posted on 2004-10-04
9
1,077 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
Technology Partners: 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!

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 73
sql query help 15 50
McAfee ePO 5.3.1 failure to install 2 35
Can I skip a node in XML? 9 29
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

679 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