Reset SA password

Question

Reset SA password

nickman

SSChasing Mays

Points: 635
More actions
July 1, 2014 at 6:48 am

#396984

We have CRM on a SQL 2012 Server, however no one in the company knows the SA password and there is no network account with SQL admin privileges either
so basically no one can log into the SQL server.
I have searched on Google but have been unsuccessful to find info that works to reset the SA password on the SQL Server.
Any help to reset the SA password will be greatly appreciated
Thanks

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

at one point, Microsoft had a script that you ran to simply add yourself as sysadmin to an instance. it required a stop and start of the instnace, but it adds you in.

it prompted you to enter the name of the instance, and did it's thing, stopping and starting the service in admin mode, adding the current user, and then starting the service in normal mode.

it was located here: but it has since moved to someplace i cannot find:

http://archive.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=addselftosqlsysadmin&DownloadId=9198

and this appears to be a copy of it that i found by googling "microsoft addselftosqlsysadmin", but i cannot guarantee that:

https://gist.github.com/wadewegner/1677788

here is a localcopy I kept of that file, if you trust a stranger from the internet:

addselftosqlsysadmin.zip

I've got other examples, like using psexec to run under the context of network service, and add the user without having to bounce the service, but it only works for 2008R2 and below, i think.

i believe there are blogs that point you towards using a DAC(dedicated Admin Connection) to try and do the same as well,but i cannot say if those work or not.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nickman SSChasing Mays Points: 635 More actions · Answer 2

Thanks for your quick reply.

I will investigate and reply back.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 3

If you can add your account to the local admins on the server and start the SQL service in single-user mode you can access the server and then reset the sa password. There is more information and detail in this link http://technet.microsoft.com/en-us/magazine/jj853293.aspx

adzuanamir SSChasing Mays Points: 637 More actions · Answer 4

if you have another users that have similar roles with SA (sysadmin roles), just simply login what that users and change the current sa password

Hema kumar-337490 Say Hey Kid Points: 707 More actions · Answer 5

If you have permissions then create an another user called saadmin and provide the systemadmin permissions to that account. login using systemadmin account, if no applications/connection strings are using then change the password for "sa" account.

Thank you,

nickman SSChasing Mays Points: 635 More actions · Answer 6

I will test some of you suggestions on Friday as this is a production SQL Server

and it needs to be schedule to bring offline.

Thanks again

adzuanamir SSChasing Mays Points: 637 More actions · Answer 7

in order to reset SA password i dont think required downtime

P Jones SSChampion Points: 12361 More actions · Answer 8

You could also contact the vendors of the CRM system and find out what their default passwords are when installing systems. It may well be that the sa password is set up in config files, ODBC connections etc. and changing it will break the CRM system. They shouldn't be even using it really but vendors do things like that.

ShaunF SSChasing Mays Points: 612 More actions · Answer 9

tripleAxe (7/1/2014)
If you can add your account to the local admins on the server and start the SQL service in single-user mode you can access the server and then reset the sa password. There is more information and detail in this link http://technet.microsoft.com/en-us/magazine/jj853293.aspx

Definitely try this one first but as you say, confirm it works in your test environment first. Also, its good practice to disable to rename the 'sa' account for security reasons, tho think this strategy through first.

ShaunF SSChasing Mays Points: 612 More actions · Answer 10

nickm-917421 (7/1/2014)
We have CRM on a SQL 2012 Server, however no one in the company knows the SA password and there is no network account with SQL admin privileges either
so basically no one can log into the SQL server.
I have searched on Google but have been unsuccessful to find info that works to reset the SA password on the SQL Server.
Any help to reset the SA password will be greatly appreciated
Thanks

How did you go with the sa account issue? It would be interesting to find out of that link recommended resolved the issue.

betty_bakke SSC Enthusiast Points: 105 More actions · Answer 11

If you want to reset you SQL Server password, follow these steps:

Add an SA account using SQLCMD and reset SA password:

1. Press Start+R, type sqlcmd and press Enter

2. Execute following SQL statement to add new or existing account to the SA server:

3. EXEC sp_addsrvrolemember ‘DOMAIN\Username’, ‘sysadmin’; GO Note: replace the username with the name you want to add.

4. Now, open MS SQL Server Management Studio. Login with the new account.

5. Navigate to DB > Security > Logins

6. Open the properties of SA account and reset the password.

After this, configure SQL Server by user mode and execute some more steps. You can read further process from here:

sonikarawat.wordpress.com/2014/07/01/simple-manual-techniques-to-recover-lost-sa-password-in-microsoft-sql-server/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

betty_bakke (7/5/2014)
If you want to reset you SQL Server password, follow these steps:
Add an SA account using SQLCMD and reset SA password:
1. Press Start+R, type sqlcmd and press Enter
2. Execute following SQL statement to add new or existing account to the SA server:
3. EXEC sp_addsrvrolemember ‘DOMAIN\Username’, ‘sysadmin’; GO Note: replace the username with the name you want to add.
4. Now, open MS SQL Server Management Studio. Login with the new account.
5. Navigate to DB > Security > Logins
6. Open the properties of SA account and reset the password.
After this, configure SQL Server by user mode and execute some more steps. You can read further process from here:
sonikarawat.wordpress.com/2014/07/01/simple-manual-techniques-to-recover-lost-sa-password-in-microsoft-sql-server/

Betty,

Have you ever tried this and had it work? There are two reasons I ask... the first is that I can't seem to make it work. The second, of course, is that I'm greatly relieved (security wise) and hope no one can make it work! (At least not like that. ;-))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 13

Once I had similar issue, and found this article useful

http://technet.microsoft.com/en-us/magazine/jj853293.aspx

Igor Micev,My blog: www.igormicev.com

ShaunF SSChasing Mays Points: 612 More actions · Answer 14

Igor Micev (7/6/2014)
Once I had similar issue, and found this article useful
http://technet.microsoft.com/en-us/magazine/jj853293.aspx

Hi Igor, so the steps in the article resolved your issue? or did you have to change any for your circumstances?