Need 'sa' password MSSQL 2012

[Principis]

Hi,

 

We have a program that uses a MSSQL database and I found out that it uses the 'sa' user to login.

 

The problem is I need the password. I can't change the login for the server in the program (as far as I can find). (Also I can't ask the company for the password)

 

I want to move the mssql server to a different (physical) server (it's now running in a vm from a Acronis image from the old server).

 

I can access everything with Windows authentication but I need the 'sa' password... resetting it is not an option.

 

Any ideas?

[Taswell]

so you don't know it, cant reset it, and you cant ask for it? how do they expect you to do anything? This sounds like some kind of test for an intern or something. I've put impossible task questions in job interviews just to see if they would ask for help.  

 

I've tried revealing it before but that failed pretty hard. 

 

Why cant you reset it?

[Principis]

 

42 minutes ago, ITheSpazI said:

so you don't know it, cant reset it, and you cant ask for it? how do they expect you to do anything? This sounds like some kind of test for an intern or something. I've put impossible task questions in job interviews just to see if they would ask for help.  

 

I've tried revealing it before but that failed pretty hard. 

 

Why cant you reset it?

Well because the program uses it to logon on the  database but I don't know how to change it in the stupid program I looked at every config file it's terrible...

 

Edit: Some background information: (I'm 18 and it's my family's company. I'm the tech because there is no-one near us and it is way too expensive to let someone come from further away.)

 

The program is used for patient information. We stopped using it so we can't ask the company for the password. (I don't know if we are allowed to use it without adding patients) we still need it because there is some financial information that we can't transfer automatically and manually would take days. 

 

I can give you a look at the program if you want (it's terribly written and really slow)

 

 

Edited February 5, 2016 by Principis

[leadeater]

The passwords are stored in a non reversible manor, you can't retrieve it in a clear text string so you can't know what it is. This is on purpose and storing passwords in a reversible format goes against security best practice.

 

I can give you methods on how to reset the SA password but I suspect your google foo in strong enough to already know how to do this and isn't what you are asking for also.

 

I can give you something that might be useful, if all you require is to give a new SQL instance the same SA password as the current one. Have a look at the following system stored procedure 'sp_help_revlogin' and use in conjunction with 'ALTER LOGIN'. You can use the stored procedure to retrieve the current SA password hash and then use ALTER LOGIN on the new SQL server to set it to the same password, not knowing what it actually is. Personally I think going ahead with this is not a good Idea as not knowing the SA password and also continuing to use the SA login for an application is in every way not best practice, not to mention never was .

 

If you need any help with the stored procedure etc let me know, I can give more complete commands if required.

[Principis]

On 5-2-2016 at 10:36 PM, leadeater said:

The passwords are stored in a non reversible manor, you can't retrieve it in a clear text string so you can't know what it is. This is on purpose and storing passwords in a reversible format goes against security best practice.

 

I can give you methods on how to reset the SA password but I suspect your google foo in strong enough to already know how to do this and isn't what you are asking for also.

 

I can give you something that might be useful, if all you require is to give a new SQL instance the same SA password as the current one. Have a look at the following system stored procedure 'sp_help_revlogin' and use in conjunction with 'ALTER LOGIN'. You can use the stored procedure to retrieve the current SA password hash and then use ALTER LOGIN on the new SQL server to set it to the same password, not knowing what it actually is. Personally I think going ahead with this is not a good Idea as not knowing the SA password and also continuing to use the SA login for an application is in every way not best practice, not to mention never was .

 

If you need any help with the stored procedure etc let me know, I can give more complete commands if required.

Thanks  I googled for hours  (I'm a quick learner that's why I do this stuff all by myself)

 

I don't really understand it what I need to do but I'm on vacation for a week I'll try it when I'm back

 

Also I tried to restore the system databases but it gave me an error that they were a different version old: 2012 new 2012 sp2

I updated the old mssql server to sp2 and tried it again (made a new backup) but the database wasn't updated (I tried it with the 'master' database)

 

Do you know how to update the master database and the other ones or don't I need those? I read online that I could restore those so that the sa password would be restored...

[leadeater]

2 hours ago, Principis said:

Thanks  I googled for hours  (I'm a quick learner that's why I do this stuff all by myself)

 

I don't really understand it what I need to do but I'm on vacation for a week I'll try it when I'm back

 

Also I tried to restore the system databases but it gave me an error that they were a different version old: 2012 new 2012 sp2

I updated the old mssql server to sp2 and tried it again (made a new backup) but the database wasn't updated (I tried it with the 'master' database)

 

Do you know how to update the master database and the other ones or don't I need those? I read online that I could restore those so that the sa password would be restored...

You could restore the master databases yes, Microsoft have information on the correct steps to do so. Same exact version of SQL Server is required. If you updated the old SQL server the master is always updated, check the full build number this will tell you what SP, RU/CU and patches have been applied. No you don't need to restore model, this is just a template for any new databases when created, and also msdb but you can restore them if you wish to.

 

https://msdn.microsoft.com/en-us/library/ms190679(v=sql.110).aspx

https://support.microsoft.com/en-us/kb/264474

http://blog.sqlauthority.com/2009/02/14/sql-server-restore-master-database-an-easy-solution/

[Uber_Beluga]

You mentioned you just wanted to move the database to a new server... Recovering SA passwords is a real pain that it sounds like you can avoid.

 

I would recommend looking through the SQL server installation directory and grabbing the yourdatabasename.mdf and yourdatabasename.ldf files.

The MDF is the actual database, and the LDF is the transaction log which oddly enough you need.

 

Install SQL onto your new server and through the new database wizard you can select those two files to create the database exactly as it was before.

It sounds a bit hacky, but it works. I've done it several times for valuable clients who can't keep track of passwords.

 

Oh, this guy has made a video that looks about right...

 

Good luck  

[leadeater]

13 hours ago, Uber_Beluga said:

You mentioned you just wanted to move the database to a new server... Recovering SA passwords is a real pain that it sounds like you can avoid.

 

I would recommend looking through the SQL server installation directory and grabbing the yourdatabasename.mdf and yourdatabasename.ldf files.

The MDF is the actual database, and the LDF is the transaction log which oddly enough you need.

 

Install SQL onto your new server and through the new database wizard you can select those two files to create the database exactly as it was before.

It sounds a bit hacky, but it works. I've done it several times for valuable clients who can't keep track of passwords.

 

Oh, this guy has made a video that looks about right...

 

Good luck  

This method only works for user databases not system databases. You have to follow the proper instructions on how to restore the master database, you cannot do it the same way as shown in this video.

[Principis]

@leadeater If i restore the system database will it also restore the sa password? (I thought so)

[leadeater]

11 hours ago, Principis said:

@leadeater If i restore the system database will it also restore the sa password? (I thought so)

Yes it will

[Principis]

5 hours ago, leadeater said:

Yes it will

Ok thanks I found a guide online I'll try it today