I want to refresh my tables in the same database with my queries using with Elastic Job Agent. I need to create credential first to connect database. How can I arrange the credentials in this case? Every documentation they have either 2 database or 2 server.
I found 2 sources but I couldn't understand the concept. Can you please explain it to me in a clear way?
Source 1 : Microsoft document
--Connect to the new job database specified when creating the Elastic Job agent
-- Create a database master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';
-- Create two database scoped credentials.
-- The credential to connect to the Azure SQL logical server, to execute jobs
CREATE DATABASE SCOPED CREDENTIAL job_credential WITH IDENTITY = 'job_credential',
SECRET = '<EnterStrongPasswordHere>';
GO
-- The credential to connect to the Azure SQL logical server, to refresh the database metadata in server
CREATE DATABASE SCOPED CREDENTIAL refresh_credential WITH IDENTITY = 'refresh_credential',
SECRET = '<EnterStrongPasswordHere>';
GO
Source 2: link
--In the master database
CREATE LOGIN mastercredential WITH PASSWORD='YourPassword1';
CREATE LOGIN jobcredential WITH PASSWORD='YourPassword2';
CREATE USER mastercredential FROM LOGIN mastercredential;
--In the job database
CREATE USER mastercredential FROM LOGIN mastercredential;
--In the target database
CREATE USER jobcredential FROM LOGIN jobcredential;
-- In the job database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword3';
CREATE DATABASE SCOPED CREDENTIAL mastercredential
WITH IDENTITY = 'mastercredential',
SECRET = 'YourPassword1';
CREATE DATABASE SCOPED CREDENTIAL jobcredential
WITH IDENTITY = 'jobcredential',
SECRET = 'YourPassword2';