I need to change the password off the SQL Server service on about 70 servers,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via SQLMonster.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via SQLMonster.com" wrote:
>> Bruce
>> The main reason that I've come across for changing the service account
>> password only via SQLEM is to maintain connections with any Full Text
>> Indexes that you may be using.
>> Changing the password in any other way is guaranteed to require an FTI
>> catalog rebuild (or worse)
>> As for the frequency of password change, I would question why you feel
>> the
>> need to change these on the service accounts. IMHO these accounts should
>> never see the light of day outside of their designated purpose (ie don't
>> log in using these accounts), so should be relatively secure in the long
>> term.
>> Also, if you change these, you are likely to open a whole can of worms
>> regarding access to other resources (file shares, other SQL servers,
>> clustering, replication etc)
>> Yes, it does requrie a restart of services.
>> I have, however, implemented 'monthly' password changes on the sa
>> account,
>> as this is far more visible. It's relatively straightforward to automate
>> using a DTS package.
>> Doing this also discourages developers from hard coding apps to use the
>> sa
>> account ;-))
>> Hope this helps
>> Andy H
