April 3, 2015 at 5:57 am
I started having this issue yesterday, after applying a change to bring my server(s) in compliance with the DISA STIGs. One of the requirements of the STIG is "SQL Server must not grant users direct access control to the Connect SQL permission." To comply with this, and keep my work down, I explicitly granted the Connect SQL to the Public role.
Once that was done, I revoked it from all user accounts, which then led to the problem with Agent jobs owned by users failing with the error in the title.
Here's what I noticed:
The jobs that fail, are owned by various domain users (IE: domain\User)
Jobs that work are owned by the SysAdmin account (SA)
Re-granting the Connect SQL privilege to the users, fixes the problem.
The users who own Agent jobs, also have the appropriate role membership(s) in MSDB to be able to modify / execute their jobs.
Is it possible that because they were (after the change) getting the right to Connect SQL through the public role, this wasn't being "passed" to the SQL Agent, and thus causing the failure? If so, then I should be able to document this need, grant the Agent job owners Connect SQL directly, and everyone else gets it through public and be good.
Servers by the way are SQL 2008 R2 SP2.
Thanks all,
Jason
April 3, 2015 at 6:06 am
jasona.work (4/3/2015)
I started having this issue yesterday, after applying a change to bring my server(s) in compliance with the DISA STIGs. One of the requirements of the STIG is "SQL Server must not grant users direct access control to the Connect SQL permission." To comply with this, and keep my work down, I explicitly granted the Connect SQL to the Public role.Once that was done, I revoked it from all user accounts, which then led to the problem with Agent jobs owned by users failing with the error in the title.
Here's what I noticed:
The jobs that fail, are owned by various domain users (IE: domain\User)
Jobs that work are owned by the SysAdmin account (SA)
Re-granting the Connect SQL privilege to the users, fixes the problem.
The users who own Agent jobs, also have the appropriate role membership(s) in MSDB to be able to modify / execute their jobs.
Is it possible that because they were (after the change) getting the right to Connect SQL through the public role, this wasn't being "passed" to the SQL Agent, and thus causing the failure? If so, then I should be able to document this need, grant the Agent job owners Connect SQL directly, and everyone else gets it through public and be good.
Servers by the way are SQL 2008 R2 SP2.
Thanks all,
Jason
Quick thought, not certain that you are doing your self any favour with this approach as this is in direct violation of DISA STIG V-41398"SQL Server default account public must be removed from each database."
😎
April 3, 2015 at 6:13 am
Eirikur Eiriksson (4/3/2015)
jasona.work (4/3/2015)
I started having this issue yesterday, after applying a change to bring my server(s) in compliance with the DISA STIGs. One of the requirements of the STIG is "SQL Server must not grant users direct access control to the Connect SQL permission." To comply with this, and keep my work down, I explicitly granted the Connect SQL to the Public role.Once that was done, I revoked it from all user accounts, which then led to the problem with Agent jobs owned by users failing with the error in the title.
Here's what I noticed:
The jobs that fail, are owned by various domain users (IE: domain\User)
Jobs that work are owned by the SysAdmin account (SA)
Re-granting the Connect SQL privilege to the users, fixes the problem.
The users who own Agent jobs, also have the appropriate role membership(s) in MSDB to be able to modify / execute their jobs.
Is it possible that because they were (after the change) getting the right to Connect SQL through the public role, this wasn't being "passed" to the SQL Agent, and thus causing the failure? If so, then I should be able to document this need, grant the Agent job owners Connect SQL directly, and everyone else gets it through public and be good.
Servers by the way are SQL 2008 R2 SP2.
Thanks all,
Jason
Quick thought, not certain that you are doing your self any favour with this approach as this is in direct violation of DISA STIG V-41398"SQL Server default account public must be removed from each database."
😎
That one actually seems to be missing from the most recent SQL STIG guide I grabbed.
April 3, 2015 at 6:31 am
jasona.work (4/3/2015)
That one actually seems to be missing from the most recent SQL STIG guide I grabbed.
I use this Microsoft SQL Server 2012 Database Security Technical Implementation Guide[/url]
😎
Apart from the STIG, recommend removing public and certainly not using it for any kind of access control, if it cannot be removed (for whatever reasons) then deny everything on it.
April 3, 2015 at 7:05 am
Looks like that site might be showing an older version of the STIG then.
The date on the one you linked to is 6-23-2014, the date on the one I downloaded direct is 1-23-2015.
Which, looking in the change history of the STIG for SQL2012, the check you mentioned was removed at that time.
April 3, 2015 at 7:33 am
jasona.work (4/3/2015)
Looks like that site might be showing an older version of the STIG then.The date on the one you linked to is 6-23-2014, the date on the one I downloaded direct is 1-23-2015.
Which, looking in the change history of the STIG for SQL2012, the check you mentioned was removed at that time.
The STIGs are one thing, common sense is another;-) Reiterating the advice, don't use "public" for this!
😎
April 3, 2015 at 9:04 am
Eirikur Eiriksson (4/3/2015)
jasona.work (4/3/2015)
Looks like that site might be showing an older version of the STIG then.The date on the one you linked to is 6-23-2014, the date on the one I downloaded direct is 1-23-2015.
Which, looking in the change history of the STIG for SQL2012, the check you mentioned was removed at that time.
The STIGs are one thing, common sense is another;-) Reiterating the advice, don't use "public" for this!
😎
No arguement on the common sense bit, but IA people don't tend to have any...
:hehe:
As for the don't use public, you're thinking the database role, or at least that's what I got from the STIG you linked to. I'm talking about using the server role, and only to grant the ability to connect sql. Most of my servers are currently SQL2008 R2, so I don't have the option to create a custom server role for this purpose.
April 3, 2015 at 9:41 am
jasona.work (4/3/2015)
Eirikur Eiriksson (4/3/2015)
jasona.work (4/3/2015)
Looks like that site might be showing an older version of the STIG then.The date on the one you linked to is 6-23-2014, the date on the one I downloaded direct is 1-23-2015.
Which, looking in the change history of the STIG for SQL2012, the check you mentioned was removed at that time.
The STIGs are one thing, common sense is another;-) Reiterating the advice, don't use "public" for this!
😎
No arguement on the common sense bit, but IA people don't tend to have any...
:hehe:
As for the don't use public, you're thinking the database role, or at least that's what I got from the STIG you linked to. I'm talking about using the server role, and only to grant the ability to connect sql. Most of my servers are currently SQL2008 R2, so I don't have the option to create a custom server role for this purpose.
IA = Intelligence Absent?:-D
Get what you're saying with the public, my thought is that the ability to connect is the first and the most important hurdle in gaining access to the data, just doesn't fit my books if you like.
😎
April 3, 2015 at 10:49 am
Eirikur Eiriksson (4/3/2015)
jasona.work (4/3/2015)
Eirikur Eiriksson (4/3/2015)
jasona.work (4/3/2015)
Looks like that site might be showing an older version of the STIG then.The date on the one you linked to is 6-23-2014, the date on the one I downloaded direct is 1-23-2015.
Which, looking in the change history of the STIG for SQL2012, the check you mentioned was removed at that time.
The STIGs are one thing, common sense is another;-) Reiterating the advice, don't use "public" for this!
😎
No arguement on the common sense bit, but IA people don't tend to have any...
:hehe:
As for the don't use public, you're thinking the database role, or at least that's what I got from the STIG you linked to. I'm talking about using the server role, and only to grant the ability to connect sql. Most of my servers are currently SQL2008 R2, so I don't have the option to create a custom server role for this purpose.
IA = Intelligence Absent?:-D
Get what you're saying with the public, my thought is that the ability to connect is the first and the most important hurdle in gaining access to the data, just doesn't fit my books if you like.
😎
I *like* that definition of IA!
:w00t:
That being said, any suggestions on my problem?
Ever run into anything like that?
I've got a couple possible solutions to this, depending on the customer and server.
Some, I can switch the job owner over to the SA account.
Others, I would have to direct-grant the owner the connect sql privilege, then document it in our server plan plus get IA to sign off on it.
Ideally, I'd like a "one size fits all" solution, to make it easier to maintain, but if I can't, I can't...
Thanks.
Jason
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply