Monday, September 17, 2012 7:20 PM
Background: We are doing our first SQL 2012 production install. This machine will be replacing an aging SQL 2005 server that is used for critical business processing. It's primary function will be running jobs that move stuff around (e.g., SSIS packages, SFTP, scripts, powershell, etc.). The service account used by the old machine has a lot of access. We plan to phase out and remove this account. We will be moving jobs one at a time, fixing each as needed.
1) Primary question: Is a Managed Security Account appropriate to manage security for the jobs? For both the SQL Server service and agent service?
2) Do I need to plan one set of AD accounts per server? Currently, we use the same set of domain accounts for most of the internal SQL Servers.
3) If we have trouble with SFTP, we remote into the server with the account to manually test access to the site. This won't be possible with a MSA? Is there any way to allow it to happen temporarily?
4) Perhaps I am way off base. Perhaps I really need a combination of an MSA with a non-MSA proxy account for the jobs? Delegation? Other?
Randy in Marin
- Edited by Randy in Marin Monday, September 17, 2012 7:21 PM
Monday, September 17, 2012 8:50 PM
I found a whitepaper "SQL Server 2012 Security Best Practices - Operational and Administrative Tasks" that helps. This is copied from the whitepaper:
- On Windows 2008 R2 or Windows 7 operating systems use managed service accounts and virtual server accounts
- On Windows 2008 SP2 or Windows Vista SP2 operating systems, use the Network Service account or a specific user account or domain account rather than a shared account for SQL Server services.
- Always use SQL Server Configuration Manager to change service accounts.
- If you use a user or domain account, change the service account password at regular intervals.
- Use CREDENTIALs to execute job steps that require specific privileges rather than adjusting the privilege to the SQL Server Agent service account.
- If a user needs to execute a job that requires different Windows credentials, assign them a proxy account that has just enough permissions to get the task done.
It appears I can use a credential for SFTP and be able to remote in with the account it uses. Is there any reason to not use the virtual account for the agent and SQL service in this case?
If want to use SSMS to create or restore a backup via a remote share, is granting access to the virtual account (<domain_name>\<computer_name>$) an option? What would be the proper way to get access to the share?
Randy in Marin