Getting Failed to determine members of SqlServerTarget (server name 'data-engineering-server-rnd.database.windows.net', server location 'data-engineering-server-rnd.database.windows.net'): Login failed for user 'sqladmin'. error with elastic job

Question

I am trying to run a job inside my server.

Steps I followed

Created a SQL Server Agent on server 1.

Ran following queries and also the Elastic job agent is hosted in server1 only and the database is ElasticJobs.

• CREATE MASTER KEY
• CREATE DATABASE SCOPED CREDENTIAL refresh_credential WITH IDENTITY = 'sqladmin', SECRET = 'abcd1234'
• EXEC jobs.sp_add_target_group_member @target_group_name = 'DBLocal', @target_type = 'Sqlserver', @refresh_credential_name = 'refresh_credential', @server_name = 'server1';
• EXEC jobs.sp_add_job @job_name = 'AddNewTablel', @description = 'Create Table Test';
• Add job step for create table EXEC jobs.sp_add_jobstep @job_name = 'AddNewTablel', @command = N**'CREATE TABLE [dbo].[Employee] ([Id] [int] NOT NULL, Name varchar(200), Address varchar(200));'**, @credential_name = 'refresh_credential', @target_group_name = 'DBLocal';
• EXEC jobs.sp_start_job 'AddNewTablel'

Now after doing all these I am getting this error.

Failed to determine members of SqlServerTarget (server name 'data-engineering-server-rnd.database.windows.net', server location 'data-engineering-server-rnd.database.windows.net'): Login failed for user 'sqladmin'.

I was also getting error that my ip is not listed and hence cannot access the server but I am still getting this error after adding my ip to the firewall as well.

Can someone please help with this. Am I missing some authentication thing here?

Answer 1

This is resolved since I was using an invalid user. I added my server level user and it worked for me.