Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Agent database role and xps Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 8:39 PM
Points: 2, Visits: 103
Hello,

I recently performed a server migration and I'm left with some SQL Agent issues. I restored an msdb from one server to another, I ran sp_help_revlogin on the original box and applied the logins to the new server.

When I did this the SQL Agent seemed 100% fine because I'm a sysadmin. So I can use SSMS to do everything I need without issue. When a member of a security group that has SQLAgentReader access tried to open the job activity monitor, though, she received a EXECUTE PERMISSIONS DENIED on 'xp_sqlagent_enum_jobs'. I granted the execute permissions and sure enough she next needed execute access to xp_sqlagent_is_starting.

Granting her individual execute permissions is not a satisfactory solution because this one security group is not the only one I need to ensure works. I need to be able to use the SQL Agent database roles as designed.

I have a script to create this login and all associated permissions (well tested and is a known good) but the problem does not go away when the login is dropped and created.

I have read about msdb ownership issues and this is not the case. The owner was sa and just to be doubly sure I ran the change command to sa anyways.

I have read about the master key being an issue but in all cases when I have read this it is complaining about sysadmins receiving these errors which I do not have that problem.

I would very much appreciate anyone's assistance here.

Thank you,
Derik Hammer
www.sqlhammer.com
Post #1340802
Posted Monday, August 6, 2012 1:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:55 AM
Points: 1,293, Visits: 1,863
SQLHammer (8/6/2012)
Hello,

I recently performed a server migration and I'm left with some SQL Agent issues. I restored an msdb from one server to another, I ran sp_help_revlogin on the original box and applied the logins to the new server.

When I did this the SQL Agent seemed 100% fine because I'm a sysadmin. So I can use SSMS to do everything I need without issue. When a member of a security group that has SQLAgentReader access tried to open the job activity monitor, though, she received a EXECUTE PERMISSIONS DENIED on 'xp_sqlagent_enum_jobs'. I granted the execute permissions and sure enough she next needed execute access to xp_sqlagent_is_starting.

Granting her individual execute permissions is not a satisfactory solution because this one security group is not the only one I need to ensure works. I need to be able to use the SQL Agent database roles as designed.

I have a script to create this login and all associated permissions (well tested and is a known good) but the problem does not go away when the login is dropped and created.

I have read about msdb ownership issues and this is not the case. The owner was sa and just to be doubly sure I ran the change command to sa anyways.

I have read about the master key being an issue but in all cases when I have read this it is complaining about sysadmins receiving these errors which I do not have that problem.

I would very much appreciate anyone's assistance here.

Thank you,
Derik Hammer
www.sqlhammer.com


Do you have identical records when you run this?
SELECT 'master', *
FROM master.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
UNION
SELECT 'msdb', *
FROM msdb.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
ORDER BY 1

if they are different follow this resolution:
http://support.microsoft.com/kb/2000274


Alex S
Post #1340872
Posted Tuesday, August 7, 2012 7:13 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 10, Visits: 138
They are not identical the cert_serial_number, sid, string_sid, expiry_date, start_date, and thumbprint are different.

I will look into that link you sent me and let you know how it goes.

Thank you,
Derik Hammer
www.sqlhammer.com


Derik Hammer
@SQLHammer
www.sqlhammer.com
Post #1341228
Posted Thursday, August 9, 2012 12:16 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 10, Visits: 138
I attempted the fix that was suggested but unfortunately it did not solve my problem.

I used this script:

use msdb
go
-- Backup the Agent certificate from the remote server to a file
BACKUP CERTIFICATE [##MS_AgentSigningCertificate##] TO FILE = '\\server\Shared\Backups\MS_AgentSigningCertificate.cer'
go
use master
go
-- re-create the agent certificate on master
-- Note: Because we are making these changes using a regular user and not as part of setup, the name
-- cannot include the ## token.
-- Creating a regular certificate in this case should be the equivalent as we only need it to derive a SID

CREATE CERTIFICATE [MS_AgentSigningCertificate] FROM FILE = '\\server\Shared\Backups\MS_AgentSigningCertificate.cer'
go
-- Recreate the user mapped to the cert and grant the same permissions that the regular certificate needs.
CREATE USER [MS_AgentSigningCertificate] FROM CERTIFICATE [MS_AgentSigningCertificate]
go
GRANT EXECUTE TO [MS_AgentSigningCertificate]
go

What I am failing to understand is how I would get the SQL Agent to use the [MS_AgentSigningCertificate] user instead of the still available [##MS_AgentSigningCertificate##] user.


Derik Hammer
@SQLHammer
www.sqlhammer.com
Post #1342917
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse