locked
Recovering a .BAK file with an unknown password? RRS feed

  • Question

  • Hi,

     

    I have a client who had their list of clients stored in an MS SQL database.  They've asked me to export their data into their new system, and have given me their .BAK file from their existing system. I've just gone to restore it in Management Studio Express, but it seems that the .BAK file has a password that the client is unaware of.

     

    Is there any way of either recovering or resetting the password? I've googled around but haven't found anything of use so far.

    • Moved by Krzysztof Kozielczyk Thursday, July 22, 2010 11:49 PM This is a general Database Engine question (From:SQL Server Express)
    Thursday, July 22, 2010 10:00 AM

Answers

All replies

  • Who setup their backups to begin with?  This is one of those danger areas when dealing with databases, because it was password protected for a reason, and while you say it is their database and has a list of their clients, what proves that to be true?  That said, the password doesn't encrypt the data, so a third party tool like Redgate Object Level Recovery Native, or Apex SQL Recovery can be used to extract the objects and information out of the password protected backup.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Monday, July 26, 2010 9:36 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:29 AM
    Friday, July 23, 2010 1:35 AM
  • yes, I fully appreciate that fundamentally the whole area of "I want to find out a users password" is a little concerning...  essentially, the database is 'owned' by our client, and is part of a software application they purchased.  Basically the client now want to move to our product, but don't want to re-enter all of their clients, so they asked us to import them.

    In this instance, I actually don't agree with the practise of putting a password on the backup; the client is unaware of it (I'm assuming the security is inbuilt into their old product), so they can't actually access their own data.

     

    My security concerns aside, thanks for your recommendations! 

    Friday, July 23, 2010 10:03 AM
  • If they still have the old system, they can take a backup manually that isn't password protected, or they can shutdown SQL temporarily to copy the database mdf and ldf files, either of which would allow you to access the database.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Tom Li - MSFT Monday, July 26, 2010 9:37 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:29 AM
    Friday, July 23, 2010 11:37 AM
  • It's a sad state of affairs when software that Microsoft really should be providing is having to be provided by third parties. Something as simple as recovering a .Bak file when it isn't even encrypted should be provided as a tool by Microsoft without all this wasted time.

    Jonathan, I've spent hours trying to find a solution to getting a .bak file recovered after a client forgot their password. Funnily enough, I even ended up on the Redgate site a few times but didn't realise the OLRN app would do the job.

    Thanks :)


    Stuart
    Monday, August 22, 2011 10:38 PM
  • It's a sad state of affairs when software that Microsoft really should be providing is having to be provided by third parties. Something as simple as recovering a .Bak file when it isn't even encrypted should be provided as a tool by Microsoft without all this wasted time.

    Jonathan, I've spent hours trying to find a solution to getting a .bak file recovered after a client forgot their password. Funnily enough, I even ended up on the Redgate site a few times but didn't realise the OLRN app would do the job.

    Thanks :)


    Stuart


    If you knew the password, the backup would be completely accessible from SQL Server.  It is not Microsoft's problem that you don't know the password associated with the backup file, and the Microsoft Tape Format (MTF) specification provides protection for the backup through the use of a password.  If they provided a mechanism that allowed you to restore a backup using the MTF without requiring the password, it would basically invalidate the entire point of having a backup.  The fact that you can restore using OLRN is the entire reason behind the implementation of TDE in SQL Server 2008, which blocks the usage of OLRN from being able to recover from this type of problem.

    In the end, there is absolutely no replacement for properly securing your backups and knowing/testing the necessary steps to recover from those backups using the builtin functionality.  If you had to revert to a third party to restore/recover your database, you didn't take the necessary steps or due dilligence in planning to be able to recvover from the SQL native backup.


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, August 23, 2011 5:42 AM