Tuesday, February 7, 2012

Enabled SA account on SQL Express when access is denied

I was unable to enable SA account or enabled Mixed mode authentication. Keep getting access denied errors when trying to do so using a local admin account on the box. Win 7 pro. SQL Express 2008

The steps below helped.

First Attemp:
(NOTE: This didnt work, the mode did change to Mixed but still access denied on SQL to enable SA or add another sysadmin account)

Force SQL server to support mixed-mode authentication.
1. Run REGEDIT
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
NOTE: This key may vary slightly based on the installed version and instance name.
3. Set "LoginMode" to 2.
4. Restart SQL Server.
(Source: http://support.microsoft.com/kb/285097 )

Second Attempt:
(note: Magic, worked fine.)
Force SQL server to let you in temporarily
1. Go to services.
2. Stop SQL Server.
3. Grab the SQL server command-line (right click the service - properties).  Mine is:
"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
4. Open an administrative command prompt.
5. Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
6. Open another administrative command prompt.
7. Run "sqlcmd -S localhost\SQLEXPRESS" from that same directory (replace with your server and instance name)
8. Now you can do all the stuff everyone told you to do that didn't work.  For example, to create a hero user with administrative accss:

CREATE LOGIN TheBoss WITH PASSWORD='123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember 'theBoss','sysadmin'
GO
9. QUIT and close the command-prompt
10. Go to the SQL Server command-line window and hit ctrl+C.  It will prompt "Do you wish to shutdown SQL Server (Y/N)?" and enter Y.
11. Close the command-prompt
(Source: http://msdn.microsoft.com/en-us/library/dd207004.aspx )

III. Finally, login using your hero:
1. Restart the SQL Server service
2. Login using SQL Server authentication as the user "theBoss" with password "123"
Yewheww...

thanks to MobyDisk on http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/60b888bf-30dc-4eaf-bea8-5fd55181f8e5

7 comments:

  1. This was absolutely perfect. Certainly save me a lot of time

    ReplyDelete
  2. Thanks a lot....

    Nikhil
    http://www.vrukshtech.com/

    ReplyDelete
  3. May be an old article but it still works as of Feb. 2020! Thank you!

    ReplyDelete
  4. Very useful post. This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. Really its great article. Keep it up. access control system singapore

    ReplyDelete
  5. This sort of assault is one that is computerized and requires great measure of arrangement to succeed. Be that as it may, whenever it is done it doesn't need a lot of work to rehash.
    https://onohosting.com/

    ReplyDelete
  6. Your database engineer should work intimately with the arrangements draftsman so framework plan and database configuration adjust. The most effective way to do this is to guarantee that each goes to the next's plan meetings. Your database planner can plan a proficient database assuming they know how it will be utilized by the framework and the framework's clients. https://hostinglelo.in/

    ReplyDelete