June 28, 2005 at 3:08 pm
I have been given the mandate to change our current MSSQLSERVER logon (+SQLSERVERAGENT, SQLscheduleSERVER) accounts.
The current SQL Server instances (40+) all have the same domain administrator accounts. The CIO would like to have the SQL logon accounts to be a domain non-administrator account.
The plan is to replace the accounts gradually over a 2 week period by doing the following:
I'm hoping that this covers the bases (I've got no one here to ask). Does this look like a solid plan? Are there any issues that I missed or that I should watch for?
Thanks!
Glenn
June 28, 2005 at 11:14 pm
If you are using SQL Mail, then:
Login as the new domain account and create a new Outlook profile.
Run EM:
Assign SQL Mail this new profile.
SQL Agent properties:
If you are using the domain account to run Jobs then change the account by Choosing Use SQL Server Authentication and back to Use Windows Authentication.
Verify SQL Mail is functional by testing.
Stop and Start SQL Agent and send a test email to an Operator.
If I were you I would not rely on the BUILTIN\Administrators SQL Server permissions, I would add the new domain account as a SQL Server Login and sysadmin server role.
Andy
June 29, 2005 at 12:41 am
Add the new account to the local administrator group on SQL Server to be converted
In general, the domain user account does not need to be a local administrator, and should not be if not. It should have only minimal rights on the machine. Log on as a service policy is needed, as well as the necessary ACLs on file system, registry etc. If you use Enterprise Manager to set the account then EM will automatically set all this for you, otherwise you should take a look at kb article How to change the SQL Server or SQL Server Agent Service account without using SQL Enterprise Manager in SQL Server 2000.
June 29, 2005 at 8:36 am
Additionally, we remove the local Administrators group from the SQL Server's Server Administrators role. This keeps our system admins from messing with the SQL server stuff (or snooping!).
June 29, 2005 at 8:39 am
Thanks so much!!!
As always the site and its members really come through. For us newbie DBAs this is a great resource.
Glenn
June 29, 2005 at 9:57 am
What are the potential implications of switching from Mixed Mode authentication to Windows Authentication?
I would much prefer to go the windows route but all of the SQL instances support very mission critical databases (I can't afford any unscheduled downtime). But if I'm going to go through the effort of changing the logon accounts I was thinking/hoping that I could switch the authentication modes at the same time.
What do you think?
Glenn
June 29, 2005 at 10:19 am
The biggest issue with going to Windows Authentication is the lack of support for this from third party applications. Almost every single one of them that I've installed since the dawn of time (slight exageration) requires a SQL login instead of using a Windows login. That's without getting into the level of access that the third party apps expect this SQL login to have (different discussion/rant, different day). That's where you'll be running into the most problems. You'll want to do a complete review of all existing apps (in-house and third party) prior to making that switch. If you're like most of us, you won't be able to.
Good luck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2005 at 10:26 am
OK thanks!
I figured that it wouldn't be too straight forward. And yes... we do have alot of 3rd party apps (I know of several which do use SQL Authentication).
Do you think that it would be worth the trouble to create new SQL Server instance(s) that would use Windows Authentication and move any compatible databases onto those instances? I guess I don't know why apps (especially MS-specific apps) are not written to use Windows apps - is it that big a deal???
Glenn
June 29, 2005 at 10:50 am
Going back to your original question, are you missing anything, I ran into an Active Directory issue when we changed the account the MSSQLSERVER service was running under. You need to change the SPN to reflect the account the service is using. Apparently it prevents Kerberos from being used for communications. Check out the following references for more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;321044
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
http://www.mcse.ms/archive81-2005-4-1581848.html
-Nancy
June 29, 2005 at 12:49 pm
Wouldn't it be much easier to just remove the account from the Domain Admin group, and assign it to some other group? That would affect every server all at once, which may not be acceptable, but you could also "undo" the change by adding the account back in to the Domain Admin group.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
June 29, 2005 at 12:56 pm
Already considered this...
As I mentioned we can't take the chance that some/all of the databases/applications would fail. So, we're in a go slow mode. - I wish things could be that easy!!!
Glenn
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply