Solved

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

Posted on 2004-10-04
9
1,072 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how the fundamental information of how to create a table.

808 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