SQL agent and autorisation

Question

SQL agent and autorisation

a.borgeld

Ten Centuries

Points: 1395
More actions
February 10, 2012 at 2:36 am

#251349

Goodmorning everyone,
I start my sql server and sql agent with a domain service user domain\svcsql. This user i use for different servers.
The user i added to my local SQL server groups in the user management of the servers that i use.
Let say i have two servers PROD and TEST and i have a agent job who schedules my backup from PROD to TEST (UNC - backup device).
\\TEST\D$\Backup where the domain\svcsql has modify/write rights.
I mostly get an operating system error and that means the user has not enough rights, because when i make this users and admin on the testserver then the backup succeeded.
But due security vulnerability i don't like this option. I can do the job as and admin, but it's not correct to use a working user for these jobs.
Has anyone has a tip to solve my issue.
Kind regards,
André

Viewing 6 posts - 1 through 5 (of 5 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

I'd recommend not using administrative shares in order to perform those backups.

Create a shared folder on the TEST server (such as \\TEST\ProdBackups$) and set the permissions on the share and the folder for the service account.

Shamless self promotion - read my blog http://sirsql.net

a.borgeld Ten Centuries Points: 1395 More actions · Answer 2

Thats a good idea, i will try a share with security.

a.borgeld Ten Centuries Points: 1395 More actions · Answer 3

Thanks it works fine and even more secure that rights a service user doesn't need.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Also, I would strongly recommend using different domain accounts for test and live servers. Think about it - at the moment, everything that your live server has access to, so does your test server. Even if this isn't a particular security issue for you, you will want your testing to fail if you accidentally point it at live resources.

I would go even further and recommend that you use a different domain account for each server for maximum security and integrity. That's how we do it here - indeed we even have different accounts for SQL Server and SQL Server Agent!

John

a.borgeld Ten Centuries Points: 1395 More actions · Answer 5

I like this solution. So when there is a security thread you only have to lock one account. And when your testing somethings and the account locks you don't bother production server.