Elastic Job sp_add_jobstep returns "Cannot reference the credential because it does not exist or you do not have permission"

Question

I'm pretty new to Azure (and SQL for that matter). I've been trying to configure Elastic Jobs Agent with a few specific jobs that would run queries against some of my databases on the server.

For right now I am targeting a test database where I want to execute a simple select query. However, I can't create the job step because of the "can't reference the credential" error.

I'm not sure why the error is popping up. I have followed Use T-SQL to create and manage Elastic Database Jobs article and I created all of the credentials and logins as described there.

The one exception here is that the masterkey already exists so I didn't create that and I also did not create a separate server for my agent host DB as suggested in some of the tutorials. My agent host DB sits on the same server where my target databases are but I would not think that would be an issue.

I have successfully created a target group and a target group member which is the specific database on this server that I want to query. I have also created the job I want to use.

The problem happens when I try to run this

DECLARE @step_id1 INT, @job_version1 INT;

EXEC jobs.sp_add_jobstep 
@job_name = N'Job1',                    
@step_id = @step_id1 OUTPUT,             
@step_name = N'Step1',                    
@command = N'select * from table',        
@credential_name = N'agentjobuser',       
@target_group_name = N'TestTarget' 

I am at a loss here, I have no idea why it's saying that the credential doesn't exist. I am using the sql server admin login so I should definitely have the permissions for it.

Answer 1

I tried to repro this and got the same error.

• When SQL server username is given as credential name parameter in the sp_add_jobstep, the same error is reproduced.

Cannot reference the credential 'user', because it does not exist or you do not have permission.

• Database scoped credential name which is created for SQL server user is given as value for the parameter @credential_name. It is executed successfully without error.