dragonemp
asked on
How to reset SQL server authentication mode from windows authentication only back to mixed?
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.
Any idea? thanks.
ASKER
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.
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.
ASKER
"program" is "problem"
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
http://www.winnetmag.com/SQLServer/Articles/ArticleID/24317/pg/2/2.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
woohoo, arbert, that's exactly the answer I'm looking for, thx :)
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\MS SQLServer\ MSSQLServe r /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
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\MS
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
-- correction for the command line script
REG ADD HKLM\Software\Microsoft\MS SQLServer\ MSSQLServe r /v LoginMode /t REG_DWORD /d 0x0 /f
-- cheers
REG ADD HKLM\Software\Microsoft\MS
-- cheers
"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....
Yes, the article link I posted stated that....
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.