December 29, 2009 at 1:51 pm
I am trying my best to get my enterprise to implement the Principal of Least Privileges with their SQL backups. Right now its a mess -- some Jobs have no owner, some have an owner who is a DBA who left (and the account is disabled and the backup Jobs fail), some just have SA (leaving the data potentially vulnerable, or difficult to change the SA password).
I am trying to "do it right" -- it seems to be harder than I thought!
Here are the issues I am having. I would most definitely appreciate any clarification on all this!
SA for everything. I've seen some places where all backup and admin jobs are run under SA. I believe this is terrible practice because 1. You really should have a ridiculously strong password on your SA account, and 2. you should change it often, and 3. like it or not that will break many poorly-written custom-apps that exist here in the real enterprise world. So if you use SA for everything, lots of people will know the password out of necessity, and you can't change it = your data is now vulnerable.
Domain DBA account. It sure seems like you should create a Windows Active Directory "service account" for your SQL administration jobs. I was thinking of creating an account called domain\SQLDBA, and have all SQL optimize/check/backup Jobs run under this account. Ideally this Domain account would have the most limited credentials possible in the domain. But I don't know what this means in the real world. What Active Directory Group should this account be added to? At first glance it sounds like Backup Operators, but that seems to be an account used to back up Domain Controllers, not SQL Servers! So would you just leave this account in only the Users default group? Then do what you need to do within SQL?
SQL Login. So lets say you have an account called domain\SQLDBA, and in SQL Server you create a Login to each of your Instances, also called domain\SQLDBA, using Windows Authentication. Now this is confusing: a Maintenance Plan and the Jobs it creates are all at the Instance-level. But there is no Instance-level Backup Operator. So what Server Role(s) would you assign to this SQL Login?
Next obvious question: what Database Role(s) would you assign to this SQL Login -- there is db_backupoperator, but why is that at the database-level, when Maintenance Plans and Jobs (that are doing the backups) are at the Instance-level?? Does it matter what Database Role(s) this Login has??
UGH! I am doing testing while writing this, and I actually gave this SQL Login every single Server Role and Database Role and STILL my backups failed! The Job reports "Unable to perform a SETUSER to the requested username because the username is invalid for database 'master'. The step failed." Double-UGH! A little more investigation and it turns out that Jobs run under SQL Agent, and SQL Agent runs under an account you set, and THIS account needs access as well (I'm not sure if it needs Instance- or Database-level access.)
What is going on?! This seems like a scrambled up mess.
[RANDOM QUESTION: Once you set a Login as the account that is performing backups, if you change that account's password, do backups keep working, or do they break?? If I get this "service account" thing working, can I change the password on that account every 90 days -- to be in compliance with my Enterprise security office -- and not have to change every instance of SQL??]
It gets worse...
It turns out this is all part of a larger issue I've been trying to understand. When you create a Maintenance Plan, that has an Owner. But you can't see it in Enterprise manager, but its there. Do this:
SELECT * FROM msdb.dbo.sysdbmaintplans
All a Maintenance Plan does (this is SQL 2000-specific, but I believe 2005 has the same issue!) is it creates Jobs. And in Enterprise Manager you can see and set the Owner of the Jobs. And the owner of a Maintenance Plan and its Jobs can get out of whack, causing your Jobs to fail.
Run this to see if your Maintenance Plan and Job owners are out of whack...
SELECT
[Plans].plan_name 'Maintenance Plan Name',
[Plans].owner AS 'Maintenance Plan Owner',
[SysLogins].loginname AS 'Job Owner',
[SysJobs].name AS 'Job Name'
FROM msdb.dbo.sysdbmaintplans AS [Plans]
LEFT OUTER JOIN msdb.dbo.sysdbmaintplan_jobs AS [Jobs]
ON Plans.plan_id = Jobs.plan_id
LEFT OUTER JOIN msdb.dbo.sysjobs AS [SysJobs]
ON [Jobs].job_id = [SysJobs].job_id
LEFT OUTER JOIN master.dbo.syslogins AS [SysLogins]
ON [SysJobs].owner_sid = [SysLogins].sid
ORDER BY Plans.plan_name ASC
Oh, BTW, in SQL 2000 here's what happens: You create a Maintenance Plan, and it has a secret owner, you. But all its doing is creating Jobs, and all Jobs do is call xp_sqlmaint, and all that does is call sqlmaint.exe I think sqlmaint.exe runs under SQL Agent, and THAT is why, ultimately, your Maintenance Plan fails (if THAT account doesn't have sufficient privileges). What a freakin' mess! And 2005 is no better -- read that article.
Now aren't y'all supposed to be a bunch-o-SQL geniuses on this board? 😉 Is this all clear-as-day to you, and you can make it clear to me in 1.00 seconds?? Do you have well-documented Standard Operating Procedures for all you SQL Servers, accounts, backups, etc, etc?? Do you all do it the RIGHT way, with highly-efficient, locked-down, Super-SQL-Servers?! The enterprise I inherited is a mess!
All help MUCH appreciated!!
December 30, 2009 at 8:23 am
Interesting question. I use sa and, interestingly enough, Tibor Karaszi has a blog post about this exact question today. Check it out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 30, 2009 at 8:32 am
I prefer to create a domain account for that, domain\SQLBackups. Give it rights to the backup directories, give it rights in SQL, and be done with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2009 at 8:42 am
Jack Corbett (12/30/2009)
Interesting question. I use sa and, interestingly enough, Tibor Karaszi has a blog post about this exact question today. Check it out.
Errr... I'm pretty sure he put that post on his blog after reading my question! His answers are, ummm... unhelpful.
Look, SQL gives us access control right down to the column-level. Then I hear many? most? DBA's say, "just use sa". Really? Why, exactly, did Microsoft bother to create a db_backupoperator Database Role? Does anyone ever use this thing? Or any of the other security features of SQL?
Or does every admin just add all users in their domain to the Domain Admins group, so everyone has God Rights to everything. Everything *will* work (including the hacking of your data, and the unmitigated spread of viruses, etc...)
December 30, 2009 at 8:52 am
subs99 (12/30/2009)
Jack Corbett (12/30/2009)
Interesting question. I use sa and, interestingly enough, Tibor Karaszi has a blog post about this exact question today. Check it out.Errr... I'm pretty sure he put that post on his blog after reading my question! His answers are, ummm... unhelpful.
Look, SQL gives us access control right down to the column-level. Then I hear many? most? DBA's say, "just use sa". Really? Why, exactly, did Microsoft bother to create a db_backupoperator Database Role? Does anyone ever use this thing? Or any of the other security features of SQL?
Or does every admin just add all users in their domain to the Domain Admins group, so everyone has God Rights to everything. Everything *will* work (including the hacking of your data, and the unmitigated spread of viruses, etc...)
Of course!
But seriously, why not do what I suggested?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2009 at 8:56 am
I agree with setting a domain account for these backup jobs, or you could set up a local server windows account to do it also.
If you do use SQL Server security (logins) to do this create specific logins to use besides SA.
The probability of survival is inversely proportional to the angle of arrival.
December 30, 2009 at 9:07 am
GSquared (12/30/2009)
I prefer to create a domain account for that, domain\SQLBackups. Give it rights to the backup directories, give it rights in SQL, and be done with it.
OK, I think this may be the correct way to do it.
But what AD group do you add it to, or just leave it in the Users group?
When you create a SQL Login with this account, what Server Role(s) do you add it to?
Do you have to then give it Database Role(s) in each database within an instance? db_backupoperator?
Do you have to go through each backup directory and give the Windows Login permissions to that? What are the least permissions you give it?
What, if anything, happens if you change this domain account's password?
Thanx!!
December 30, 2009 at 10:03 am
subs99 (12/30/2009)
Errr... I'm pretty sure he put that post on his blog after reading my question! His answers are, ummm... unhelpful.Look, SQL gives us access control right down to the column-level. Then I hear many? most? DBA's say, "just use sa". Really? Why, exactly, did Microsoft bother to create a db_backupoperator Database Role? Does anyone ever use this thing? Or any of the other security features of SQL?
Or does every admin just add all users in their domain to the Domain Admins group, so everyone has God Rights to everything. Everything *will* work (including the hacking of your data, and the unmitigated spread of viruses, etc...)
I'm not sure where you have worked that DBA's say "just use sa" as I've yet to run into one who says that. The db_backupoperator Database Role is there so you can allow users to run backups of specific databases without needing sa access. This role is rarely, if ever used, because most DBA's schedule backups and don't want users taking backups because it will break the backup chain (unless you use copy only, but how many users know about that?). K. Brian Kelley has a good write up on the Fixed Database Roles over a MSSQLTips where he gives several reasons why this role is usually not used.
Using sa as a job owner isn't, in my opinion, a security risk. In most cases only users with sysadmin privileges can create jobs so they can already do anything on the server. Yes, starting with SQL Server 2005 there are Agent Roles, but I'd bet those are rarely used.
I think you misunderstood what Tibor is saying. He's saying using sa can help avoid problems and be fine security wise because:
...we can change password for sa, disable sa, or even run in Windows Only mode.
And jobs will still run. Thus you can just disable the sa account or change the password as often as you want without affecting the status/runnability of the job(s).
I'm not saying you have to go that way, I'm just saying that I don't really see a security risk involved with this as you still control who can create, view and run jobs and those in the SQLAgent Roles cannot change the owner to sa only sysadmins can.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply