[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1104
  • Last Modified:

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.
0
dragonemp
Asked:
dragonemp
  • 3
  • 3
  • 2
  • +1
1 Solution
 
geotigerCommented:
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
 
dragonempAuthor Commented:
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
 
dragonempAuthor Commented:
"program" is "problem"
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
arbertCommented:
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
 
arbertCommented:
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
 
dragonempAuthor Commented:
woohoo, arbert, that's exactly the answer I'm looking for, thx :)
0
 
mironCommented:
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
 
mironCommented:
-- correction for the command line script

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

-- cheers
0
 
arbertCommented:
"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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now