How to check sql server agent security settings?

Question

I have multiple SQL Servers (2005 SP4 Standard) with daily jobs running backups and restores them to DR servers to an off site location which is also within our intranet.

My question is when my production server agent backs up the databases and calls the DR server agent job to restore the backup files how do I know if the connections are secure? Are sql server agents secure by default? I use windows login to run all SQL Server agents.

Thanks in Advance!

Answer 1

Hi,

What do you mean on "connections are secure"? Can you explain your concern a bit more detailed?

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Hi JNY,

What does the meaning of "secure"?

For SQL Server Agent security, we can follow these guidelines to improve the security of your SQL Server Agent implementation:

1.Create dedicated user accounts specifically for proxies, and only use these proxy user accounts for running job steps.

2.Only grant the necessary permissions to proxy user accounts. Grant only those permissions actually required to run the job steps that are assigned to a given proxy account.

3.Do not run the SQL Server Agent service under a Microsoft Windows account that is a member of the Windows Administrators group.

Here is a link about how to Run Sql Server Agent and Sql Server Jobs with least privilege in Sql Server 2005:

http://blogs.msdn.com/b/varun_sharma/archive/2007/08/30/how-to-run-sql-server-agent-and-sql-server-jobs-with-least-privilege-in-sql-server-2005.aspx

 

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

It is recomended to run sql server agent service through a dedicated sql service account.

---

Please click "Propose As Answer" if a post solves your problem. OR "Vote As Helpful" if a post has been useful to you. Thanks, Debasish Mohanty Blog:http://sqlcodebank.blogspot.com

Answer 4

The Backup files are actually written under the SQL Server Service account's rights.  Even though SQL Agent may start the job that issues the BACKUP command, the actual action is taken by the SQL Server.

So, security of the backup location is based on the standard security set up for that location on the domain.  If the backup share is open to everyone, then it is not secure.  If the share and folder are only available to the SQL Server service account and the administrators who must use it, then it is fairly secure. 

There are other steps that you can take, with varying degrees of overhead and usefulness:
http://msdn.microsoft.com/en-us/library/ms190964(v=sql.90).aspx
http://msdn.microsoft.com/en-us/library/ms189128(v=sql.90).aspx

In summary, SQL Agent and SQL Server do not manage the security of the backup location.  You or your administrators need to ensure that only a restricted set of approved account have that access.

RLF