SA password reset

Question

Post reply

SA password reset

ramyours2003

SSChampion

Points: 12266
More actions
August 13, 2010 at 3:41 am

#142356

what happens when we reset the Service account password ?

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

that area(sp.script/job) will get failed where you have hardcoded the password

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SkyBVI SSCoach Points: 15128 More actions · Answer 2

@ramyours

Ya, bhuvnesh is right and also

the credentials which are stored in the sql server have to be changed too

otherwise there will be errors in many jobs/scripts whcih verifies the credentials

before running.

Regards
Sushant Kumar
MCTS,MCP

george sibbald SSC Guru Points: 104210 More actions · Answer 3

if you don't change it in SQL as well via configuration manger service will fail on next restart.

---------------------------------------------------------------------

yitiana SSC-Addicted Points: 478 More actions · Answer 4

You can change sql sa password by command prompt.

Osql –S yourservername –E

1> EXEC sp_password NULL, ’yourpassword’, ’sa’

2> GO

This article is given 4 methods to change sql password, that could be helpful.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

If you created Maintenance plans with the 'sa' userid they will fail after the password change. You will have to go in and edit them for the new password.

steve block SSCertifiable Points: 7698 More actions · Answer 6

Be sure to have at least one other user with the SysAdmin Server Role so you don't lock yourself out.

Steve

SkyBVI SSCoach Points: 15128 More actions · Answer 7

@ markus

I dont think the maintenance plan will fail as when the password is reset , it will be automatically be updated..

Thanks,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 8

In SQL Server 200 no, In 2005 and 2008 yes. It stores the userid/password in the Integration Svc package now. I found this out the hard way as a SOX audit required us to change the admin password that was running the maint plans.

george sibbald SSC Guru Points: 104210 More actions · Answer 9

interesting stuff on sa but i thought the question was on the service account!

The OP seems to have backed out as well.

---------------------------------------------------------------------

mayank_tyagi Old Hand Points: 325 More actions · Answer 10

SA password changes is fully dependent upon your dependency on Login .

if you are using SA login in Replication ( changed login on Subscriber end), linked server (target) and maintenance plan (shrink or reindexing ) , logshhiping etc.

if these tasks are dependent on SA login ,then these tasks would be fail.

It is recommended that you should use another user with SA privillage for Adminstration.

steve block SSCertifiable Points: 7698 More actions · Answer 11

It IS unclear whether the Orig Poster thinks the sa user stands for Service Account or they really meant changing the password on the Account the run the Sql Server or Sql Server Agent service.

I guessed that the OP assumed that SA stands for Service Account instead of SysAdmin. I'm not sure when a service account's password would ever be changed since they don't normally login.

Steve

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 12

As far as changing the service account password... well... Our company is under a microscope right now and some of our systems are being reviewed very carefully. A SOX audit asked if we had ever changed the password on the service account... well... no... we hadn't.... they asked if anyone who knew that password had left the company or moved into a non DBA position... no... if the answer was YES they would have forced us to change the password.... AND it was on a report that went all the way up to the Board of Directors.

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 13

For security purpose you should not use SA, should have user(s) assign to that role. On all of my SQL Server boxes I completely remove SA from the logons and use my Windows Account the manage SQL Server.

P Jones SSChampion Points: 12353 More actions · Answer 14

Don't use any user's individual accounts - what happens when they leave?

Put users in domain groups e.g. SysAdmins and assign the permissions to these. Then you only have to remove people from a group once rather than hunt for every occurence of their login.

Create domain accounts for each service which only have permissions on their own server and another domain account for the proxy account to run job steps which require access to other servers.

When you change the service accounts, do it through the configuration manager and reporting config manager not just from windows services as it does all the required changes.