Recover Password / Access to MS SQL Server 2008 Express R2

On a Windows 7 system I have a copy of Sql Server 2008 R2 Express loaded.  I want to access it via the sqlcmd and build some adhoc reports.   The pass word is not avilable --

So I used Google and found a number of How To processes but none will work for me --

After I stop a, add the -m flag and restart the server I start a CMD box as administrator -

sqlcmd --

sqlcmd -S .\servername   (Confirmed with osql -L)

sqlcmd -A

sqlcmd -E  

And various combinations of these last three all get me errors messages but none of them let me enter sql commands to create a new user / grant authority..

Any tips from those here with more Windows / SQL server experience than I have..

One of the many similar guides to  accomplish this..
     http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/
SQL-2008-Recovery.txt
LVL 15
PhonebuffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PhonebuffAuthor Commented:
A little more data -- although I am still stuck --

I am starting the server -m;-t 7806;-d......................................

The log does show it should be in Administrative mode -

2015-05-06 18:02:06.98 Server      Server is listening on [ 127.0.0.1 <ipv4> 49294].
2015-05-06 18:02:06.98 Server      Dedicated admin connection support was established for listening locally on port 49294.

But I can not connect no matter what I do to enter a new user.  

C:\Users\CPS 10>sqlcmd -S 127.0.0.1,49294
Msg 18470, Level 14, State 1, Server CPS09-PC\ALARMSERVER, Line 1
Login failed for user 'CPS09-PC\CPS 10'. Reason: The account is disabled.

See Attached -  For full log - Same name as before / different contents -
SQL-2008-Recovery.txt
0
MysidiaCommented:
If running SQL2005, 2008 or 2012; I suggest downloading SysInternals PsExec.

Temporarily Disable UAC, Reboot,

And use PsExec startup  Sqlcmd as the NT AUTHORITY\SYSTEM  user.
So long as SQL is configured in a suitable authentication mode,  the System user should have full access.

Invoke Sqlcmd with a command such as

PsExec -s -i "C:\path\to\file\sqlcmd.exe"

If your SQL is not a default instance you may need to use the -S SERVER\INSTANCE  option with sqlcmd.
And the '-E'  option for trusted connection.

The exact location of sqlcmd.exe will vary with your install path, the default depending on version of SQL server.

If successfully starting SQLCMD and connecting as SYSTEM,  there you should be able to do  such things as....:

use master
GO
CREATE LOGIN blah1 with PASSWORD='blah2'
GO
CREATE LOGIN [DOMAIN\blah2] FROM WINDOWS
GO

EXEC SP_ADDSRVROLEMEMBER 'blah1','SYSADMIN'
GO

EXEC SP_ADDSRVROLEMEMBER 'blah2','SYSADMIN'
GO
0
PhonebuffAuthor Commented:
Thank you for the suggestion --

But I am still stuck, see new Logs / Trace on the attachment -
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

MysidiaCommented:
Your error output says that user CPS 10  account is disabled.

Did you use a tool such as PSExec  -  https://technet.microsoft.com/en-us/sysinternals/bb897553
to run SQLCmd as  the Local System user  and connect as that System user instead of the 'CPS 10'  user?

Example:
C:\Tools\SysinternalsSuite>whoami
contoso\nobody1
C:\Tools\SysinternalsSuite>psexec -s whoami
nt authority\system
C:\Tools\SysinternalsSuite>psexec -s -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" -E
1> use master
2> GO
Changed database context to 'master'.
1> select CURRENT_USER
2> GO
dbo
1>
0
PhonebuffAuthor Commented:
Yes,

psexec -s whoami  does return "nt authority\system"

But all attempts to connect to the instance so far tell me that 'nt authority\system" is disabled - Error 18470.

So I can't get to the a prompt --
0
PhonebuffAuthor Commented:
Update --

   So I can now get into SQL Management Studio --

   Object Explorer comes up and showes me the database- But when I try and open it I still get
     Microsoft Server Error 916 --

   Anyone have any other ideas -- See error message Details attached =
SQL-2008-Recovery.txt
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Stop the SQL Server, make a backup of the database files and uninstall the SQL Server.
Install it again and give yourself permissions.
Attach the database files.
0
PhonebuffAuthor Commented:
-- Not Abandoned -- Just don't have a answer yet -- Other issue have pushed this down the list --

Victor's solution may be how we go, but not able to try it do to other availability issues.  Will probably build a back up slice and try it there -
0
PhonebuffAuthor Commented:
Please don't delete this -- I never found a specific answer but This information is very helpful and to the point of the initial question -
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try to follow Joseph's article?
0
PhonebuffAuthor Commented:
Victor, as I said in my objection to delete -- I have not solved this but I felt that the information provided is very valid and direct to the question, and did not want it deleted.  

I will start over this week, and follow Joseph's article as suggested.  But the issue seems to be that the application that installed the SQL (208 R2 Express) instance to start with specifically crippled it to certificate only.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PhonebuffAuthor Commented:
So,

   While all the paths got my down the road I have so far been unable to access the data -- At this time the project has been shelved as the data has been recreated / recovered.  An placed in a PpostgreSQL environment.  

   Thank you all --
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.