What's the best way to change the 'sa' password

Question

Post reply

What's the best way to change the 'sa' password

Willian Funes

SSC Journeyman

Points: 93
More actions
December 9, 2009 at 11:26 am

#135814

Hi everybody,
I'm new at the company and my boss is asking me to change the 'sa' password because the old Dba knew it.
I was thinking about the consecuence to do that and I know that i need to update windows service and some Jobs.. my question is what's the best way to do it.
SQL2005 allow to change the password, just going to -> security tabs -> Logins tab - and click to 'sa' user properties for change the password..
But I know that I can change it using the following code:
ALTER LOGIN [sa] WITH PASSWORD = N'MyNewPassword' OLD_PASSWORD = 'MyOldPassword';
GO
Wha's your recomendation and tip's about that.
Kind regards
WFunes

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

If its a one off you may as well use the gui, that way its never in clear text on the screen.

You wont have to change the service account or sql agent jobs.

If you have batch files with the sa password hardcoded - naughty. Take the opportunity to change to a different account. (preferably a windows account)

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

Willian Funes SSC Journeyman Points: 93 More actions · Answer 2

Thank's for your comments..

That's true.. i don't need to chance nothing about services or jobs.

Just tell me somthing .. According to your experience ¿how do you change the 'sa' password? from 'gui' or using 'alter login 'sa' --> like I posted i my topic..

regards

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

If you only have a SQL 2005 environment then either works. I like scripts because I can automate things with them and they are predictable. If you use SQLCmd Mode you can set runtime variables that don't persist the password in the script, and you can write a little command line code to automate pushing the change to all of the servers in your environment.

I personally manage 2000, 2005, and 2008 in my environment, so I don't use ALTER LOGIN, but instead call the older stored procedure from 2000 to make my changes:

EXEC master.dbo.sp_password @old=NULL, @new=N'newcomplexpassword', @loginame=[sa]

GO

I also do this in a multi-server query:

Multiple Server Queries with SSMS 2008 [/url]

so I can hit all my machines in one execution.

Willian Funes SSC Journeyman Points: 93 More actions · Answer 4

thank's a lot.

That's I wanted to know. In my case I know the 'sa' password, so I just need to change it (for security) using @old='OldPassw' and I think is the same for SQLExpress and SQL2000.

Kind regards

🙂

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

As a sidebar, this is one of the reasons why the SA login must never be used, why each DBA should have their own login, and jobs are never associated with their login.

If you can get buy-in on it, the password for SA shouldn't even be known by DBA's. And, the password should be cut in half and stored in two different safes just to ensure the "2 man rule" is always in effect. No one person should ever have the whole password until an emergency requires it. Right after the emergency is done, the password should be changed using the same process.

--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)
Intro to Tally Tables and Functions

Willian Funes SSC Journeyman Points: 93 More actions · Answer 6

I'm agree with you.

So, this is my first month in the company and I'm knowing

the way they work. I will try put in order the DB Administration

as soon as possible and I'll consider your recomendation about

'Post a new topic'

Regards

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Jeff I really like the 2 person rule. Who would have thunk something so simple could be so secure !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Dean Jones-454305 SSCarpal Tunnel Points: 4804 More actions · Answer 8

Good point Jeff. How about when there is only one DBA ? and maybe a system admin. would you give SA passwords to non DBA's ?

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

I would agree that you should not be connecting to SQL with the sa account, or the windows account the SQL service runs under for that matter, but as someone with sysadmin rights can change the sa password without having to know the current one the two man rule is not foolproof. I see no reason why DBAs should not have easy access to the sa password.

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

terry.peterson Grasshopper Points: 10 More actions · Answer 10

Jonathan - you mentioned using SQLCMD or cmd line code to automate the pwd changes - that is exactly what I am looking for - can you share some of that? Much appreciated.

Summer90 SSC-Dedicated Points: 32864 More actions · Answer 11

george sibbald (12/12/2009)
I would agree that you should not be connecting to SQL with the sa account, or the windows account the SQL service runs under for that matter, but as someone with sysadmin rights can change the sa password without having to know the current one the two man rule is not foolproof. I see no reason why DBAs should not have easy access to the sa password.

I tend to agree. We don't use the 'sa' login but the two of us here know it. We change it from time to time for security purposes. If you are a very small shop and you accidentally lock out your ID and it is the only ID that has 'sa' rights you NEED to know the 'sa' login to reset your ID...

Mike Hinds SSCarpal Tunnel Points: 4258 More actions · Answer 12

Right now I am the only DBA, for over 300 SQL servers. I don't know the SA password for any of them. I disable SA immediately upon creating a new instance, for the versions where SA can be disabled, and is not disabled by default. I then change the password (of the disabled account) to a string from a random generator, something like

I prefer "un-typable" characters (I know, not really). It won't stop a brute-force attempt, but it will sure slow it down.

Each server is created with the group named something like DOMAIN\MSSQL Administrators. If I'm hit by the proverbial truck, our IT Security division can put my replacement's administrative account in the group, and they remain in business. Meanwhile the auditors believe that SA won't likely be hacked, and I get a gold star.

Nobody "needs" SA.

Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS