I am trying to write up a stored procedure to allow users to execute jobs in MSDB that are owned by another user. The job-owning user is a service account (set up as non-interactive) has elevated permissions which I do not want the business users to have themselves.
The problem is that I can't seem to get the execute as permissions to work. I've tried it with and without the impersonate logic, but keep getting "Cannot execute as the server principal because the principal does not exist, this type of principal cannot be impersonated, or you do not have permissions."
The login being impersonated isn't orphaned. It's a windows account, so the SIDs match everywhere. Sometimes the jobs are owned by sa, sometimes it's owned by the SQL Server Agent service account, sometimes they are owned by a separate service account that doesn't run the SQL server services. It doesn't matter, the error still pops up.
The code is posted below. Can anyone point out what I'm missing?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ApprovedJobs](
[MyJobID] [int] IDENTITY(1,1) NOT NULL,
[SQLJobID] [uniqueidentifier] NULL,
[SQLJobName] [nvarchar](128) NULL,
[DateEntered] [datetime] NULL,
[ServiceAcctName] [varchar](50) NULL,
CONSTRAINT [PK_ApprovedJobs_MyJobID] PRIMARY KEY CLUSTERED
(
[MyJobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ApprovedJobs] ADD CONSTRAINT [DF_ApprovedJobs_DateEntered] DEFAULT (getdate()) FOR [DateEntered]
GO
INSERT INTO dbo.ApprovedJobs (SQLJobID, SQLJobName, DateEntered, ServiceAcctName)
SELECT job_id, [name], GETDATE(), 'MyServiceAcctName'
FROM msdb.dbo.sysjobs
WHERE [name] IN ('Test1','Test2','Test3');
CREATE OR ALTER PROCEDURE dbo.RunJob (@jobname NVARCHAR(128))
AS
BEGIN
DECLARE @SQLJobName NVARCHAR(128) = @jobname, @MaxAgentStartDt DATETIME;
DECLARE @JobChecks TABLE (JobEnabled BIT, JobRunning TINYINT, JobExists BIT);
DECLARE @RunSQL VARCHAR(100),
@ServiceAcct VARCHAR(50) = (SELECT DISTINCT ServiceAcctName FROM dbo.ApprovedJobs WHERE SQLJobName = @SQLJobName);
SET @RunSQL = 'EXECUTE AS LOGIN = ''' + @ServiceAcct + ''';';
EXEC (@RunSQL);
SET @MaxAgentStartDt = (SELECT MAX(agent_start_date) FROM msdb.dbo.syssessions);
INSERT INTO @JobChecks (JobEnabled, JobExists, JobRunning)
SELECT DISTINCT sj.enabled, CASE WHEN sj.name IS NULL THEN 0 ELSE 1 END AS JobExists,
CASE WHEN act.job_id IS NOT NULL THEN 1 ELSE 0 END AS JobRunning
FROM dbo.ApprovedJobs daj
LEFT OUTER JOIN msdb.dbo.sysjobs sj
ON daj.SQLJobID = sj.job_id
LEFT OUTER JOIN msdb.dbo.sysjobactivity act
ON sj.job_id = act.job_id
AND act.run_requested_date IS NOT NULL AND act.stop_execution_date IS NULL
LEFT OUTER JOIN msdb.dbo.syssessions sess
ON sess.session_id = act.session_id
AND sess.agent_start_date = @MaxAgentStartDt
WHERE daj.SQLJobName = @SQLJobName;
IF (SELECT COUNT(JobExists) FROM @JobChecks) IS NULL OR (SELECT COUNT(JobExists) FROM @JobChecks) = 0
INSERT INTO @JobChecks (JobExists, JobEnabled, JobRunning)
VALUES (0,0,0);
IF (SELECT JobExists FROM @JobChecks) = 1 AND (SELECT JobEnabled FROM @JobChecks) = 1 AND (SELECT JobRunning FROM @JobChecks) = 0
BEGIN
EXEC msdb..sp_start_job @job_name = @SQLJobName;
REVERT;
PRINT 'Your job ' + @SQLJobName + ' has been started.';
END
ELSE IF (SELECT JobExists FROM @JobChecks) = 1 AND (SELECT JobEnabled FROM @JobChecks) = 0
BEGIN
PRINT 'The job ' + @SQLJobName + ' is currently disabled. Please reach out to your team to verify this is the correct status and, if not, open a Database Administration Engagement request ticket to resolve the issue.';
END
ELSE IF (SELECT JobExists FROM @JobChecks) = 0
PRINT 'The job ' + @SQLJobName + ' either does not exist on this server or you do not have permission to run it. Please open a Database Administration Engagement request ticket with details on what you need completed.';
ELSE IF (SELECT JobExists FROM @JobChecks) = 1 AND (SELECT JobEnabled FROM @JobChecks) = 1 AND (SELECT JobRunning FROM @JobChecks) = 1
BEGIN
PRINT 'The job ' + @SQLJobName + ' is already running and cannot be started at this time.';
END
END;
GO
October 31, 2023 at 11:58 am
Of course, after I posted this, I realized that the user running the proc can't grant impersonate on themselves, so I'm ripping that part of the code out. But I still need a way to give them permission to execute the stored procedure as another elevated user without permanently having impersonate permissions on an elevated account or being able to do anything but this one thing. Execute jobs from a list that they don't own without giving them permission on anything else.
Thoughts would be appreciated.
October 31, 2023 at 5:20 pm
I would think you would want to use "EXEC AS" on the proc itself rather than inside the proc.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 31, 2023 at 5:41 pm
One approach I can think of to this is to have the end users insert their job request into a table. Then you have a SQL job that runs every 5 minutes (or whatever makes sense in your environment) that reads from that table to determine what job to start. Basically you build up a job scheduler job that runs as the SQL Agent account (or whatever account makes sense for your environment).
Impersonation is something I use VERY sparingly in SQL and even then, it is never in a stored procedure - it is just for DBA's to test user permissions. It is FAR too easy, especially in your above example, to have SQL injection happen and that limited user suddenly has sysadmin permissions... Kind of like cursors or while loops. They have their place, but most of the time I don't actually need them except for some administrative tasks.
That being said, the error you got tells you exactly what is wrong - the end user doesn't have permissions to impersonate that elevated user. And I would be VERY hesitant to grant that permission as it would apply outside your SP as well.
I like the job table approach as there is little risk of the user impersonating someone above them and you can do input sanitation on the job table you create by making sure that the data entered matches a job name. You can do that in the stored procedure and then throw an error back to the calling application and you can pretty much eliminate SQL injection even with dynamic SQL in the new job. I say "pretty much" because if someone with the right knowledge goes and writes directly to the table outside of the app, they can still do SQL injection. So there is risk, but no more risk than your original approach, plus you can have the table as an audit table too for keeping track of request times and actual start times as well as who requested it. I'd probably have the table with columns like JobName VARCHAR(255) NOT NULL, RequestTime DATETIME NOT NULL DEFAULT = GETDATE(), RequestedBy NVARCHAR(255) NOT NULL, StartTime DATETIME NULL, EndTime DATETIME NULL, Completed BIT NOT NULL DEFAULT = 0. Then I'd have a clustered index on the Completed, JobName, and RequestTime, which should result in a unique key and if it isn't unique, I would want it to fail the duplicate row anyway as I don't want 2 jobs to be requested simultaneously (in most cases...). BUT how you design the table is up to you as it will impact how you design the job too. You could have the job be asynchronous and it would pull in all the not complete jobs and then run everything as quickly as possible, or you may want it synchronous so that things run in the order they were requested. Really depends on if you have jobs that are dependent on each other.
That's just how I would design it. Others may have different ideas or not like this approach. MAYBE you want an "async" bit column to determine which jobs can be run at the same time and which ones need to wait for the previous job to complete. Maybe start time and end time isn't a useful metric to you as that is already tracked in the job history. Maybe you don't care who requested the job or maybe you want a second table of who is allowed to request jobs or maybe you are going to handle that with stored procedure permissions... I don't know your full requirements; I'm just saying how I'd tackle that problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 31, 2023 at 5:55 pm
Another approach would be to sign the stored procedure and create a certificate user in MSDB with appropriate permissions. It can get a bit involved but the following may help:
Packaging Permissions in Stored Procedures (sommarskog.se)
October 31, 2023 at 7:15 pm
I would think you would want to use "EXEC AS" on the proc itself rather than inside the proc.
Sadly, without giving IMPERSONATE to the users, I cannot let them us EXEC AS and I don't want them to know what account they are impersonating. That's a large security hole.
October 31, 2023 at 7:17 pm
One approach I can think of to this is to have the end users insert their job request into a table. Then you have a SQL job that runs every 5 minutes (or whatever makes sense in your environment) that reads from that table to determine what job to start. Basically you build up a job scheduler job that runs as the SQL Agent account (or whatever account makes sense for your environment).
It's a thought, but the issue I'm trying to resolve is a new security policy that is tightening down permissions in all environments. And the policy didn't take into account SQL Agent's peculiarities regarding who can run a job and when. These users need to ad-hoc run jobs for testing and they don't have permissions to insert data into tables, which would require setting up new permissions. Either way, I need a work around for the problem presented.
October 31, 2023 at 7:18 pm
Another approach would be to sign the stored procedure and create a certificate user in MSDB with appropriate permissions. It can get a bit involved but the following may help:
Hmmm. Thanks. I'll take a look at this.
Might not hurt to ask about the new security policy and let them know that you are having issues complying to the new security policy due to how SQL Server works. Rather than trying to find a way to work around the security policy, could you work with the team who is pressuring you to implement it to find a compromise?
The certificate approach may work, but you also may want to reach out to the security policy team to see if that approach would be acceptable to them or if that too would be seen as circumventing security policies in the same way that granting impersonation to the account wasn't allowed...
Alternately, maybe I am dumb here, but could you take an approach like this:
https://dba.stackexchange.com/questions/10449/grant-permissions-to-run-a-sql-server-job
basically, SP exists in database that user has access to and you would have in that SP the following code:
CREATE procedure [dbo].[myJobToRun]
with execute as owner
as
exec [msdb].[dbo].[sp_start_job] @job_name = 'myJobToRunName'
modified to suit your requirements? The "EXECUTE AS OWNER" should get around the need to try to impersonate inside the stored procedure and granting impersonation permissions and such.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 1, 2023 at 12:28 pm
The syntax I've used in the past for this type of thing is below. Because TRUNCATE needs higher permissions than DELETE, we had to create a special user for procedures that cleared down tables.
CREATE PROCEDURE [dbo].[usp_TruncateMyTable]
WITH EXECUTE AS '<user name>'
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE dbo.MyTable;
END;
November 1, 2023 at 12:28 pm
Removed duplicate post.
November 1, 2023 at 2:49 pm
OKAY. Now the WITH EXECUTE AS <user> / OWNER is the syntax I could not for the life of me find. I might try that.
The security is being implemented across the board and it's actually well thought out except for a few cases of "DUH," which includes the SQL Agent Jobs. The thing is, the policy has to cover Oracle, Mongo, MySQL, etc. Hence the few "DUH" moments that we've run into.
I haven't read up on the certificate thing yet, but that is yet another Maintenance Item that I would have to document and get my team on board with. I'm also using this whole thing as proof of concept for the security team. Once I get it working, I present it to them to get their buy-in before we actually implement.
The hope by corporate is we get every individual AOR jobs moved to ESP instead of SQL, which solves the security issue. Unfortunately, they're running into an issue that I can neither understand nor explain since I don't know ESP from boo. So my workaround is a temporary "let's ease the pain until ESP is ready" kind of thing. If, of course, security signs off on it.
November 1, 2023 at 4:03 pm
AOR = Area of Responsibility. Think of it like departments or smaller chunks of a business division. I am separating out their jobs from the jobs owned by the DBAs with this comment.
ESP will allow the AOR teams to have more control over when they can run the jobs or push updates via the system without getting permissions in SQL Server that are overly broad. Whereas in SQL they would need SQLOperatorRole to do certain things, ESP has a finer segregation that only allows them to touch their jobs no matter what permissions they have and they don't have to be job owner to run the jobs.
Or so I am told.
November 1, 2023 at 5:55 pm
YAY! WITH EXECUTE AS OWNER solved my issue. No extra permissions required, no impersonation permission granted to the user group. Thank you all so much! I appreciate you.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply