Azure elastic database jobs

Question

Post reply

Azure elastic database jobs

Roust_m

SSCoach

Points: 17408
More actions
June 7, 2019 at 12:50 am

#3650933

Hi,
I am trying to use this approach to automate some tasks:
https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-tsql
It mentions: "The credential needs appropriate permissions, on the databases specified by the target group, to successfully execute the script. "
CREATE MASTER KEY ENCRYPTION BY PASSWORD='password';
CREATE DATABASE SCOPED CREDENTIAL myjobcred WITH IDENTITY = 'jobcred',
SECRET = 'password';
GO
CREATE DATABASE SCOPED CREDENTIAL mymastercred WITH IDENTITY = 'mastercred',
SECRET = 'password';
GO
EXEC jobs.sp_add_target_group 'ServerGroupDev'
GO
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ServerGroupDev',
@target_type = 'SqlServer',
@refresh_credential_name='mymastercred', --credential required to refresh the databases in server
@server_name='myserver.database.windows.net,1433'
GO
EXEC [jobs].sp_add_target_group_member
@target_group_name = N'ServerGroupDev',
@membership_type = N'Include',
@target_type = N'SqlDatabase',
@server_name = N'myserver.database.windows.net,1433',
@database_name =N'MyDB'
GO
EXEC jobs.sp_add_job @job_name='Backup schema', @description='Database schema backup'
GO
DECLARE @ReturnCode INT
DECLARE @job_name sysname = 'Backup schema'
SELECT @ReturnCode = 0
EXEC @ReturnCode = jobs.sp_add_jobstep @job_name=@job_name, @step_name=N'script databases', @credential_name='myjobcred', @target_group_name='ServerGroupDev',
@step_id=1,
--@cmdexec_success_code=0,
--@on_success_action=3,
--@on_success_step_id=0,
--@on_fail_action=2,
--@on_fail_step_id=0,
--@retry_attempts=0,
--@retry_interval=0,
--@os_run_priority=0, @subsystem=N'PowerShell',
@command=N
'#Stop script on any error
$erroractionpreference = "Stop"
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$Srv = ''$(ESCAPE_SQUOTE(SRVR))''
#$Srv = ''OTPDEV\OLB01''
SL SQLSERVER:\SQL\"$Srv"\Databases
$s = new-object (''Microsoft.SqlServer.Management.Smo.Server'') "$Srv"
$dbs=$s.Databases
$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.IncludeIfNotExists = 1
$so.ExtendedProperties = 1
# $so.Permissions = 1
$so.IncludeDatabaseContext = 1
# $so.WithDependencies = 1
$Query = "select name from master.sys.databases (nolock) where database_id > 5"
$DBList = Invoke-Sqlcmd -query $Query
foreach($item in $DBList) {
$DB = $item.name
$File = "01-DB.sql"
$script = $dbs["$DB"].Script($so)
$script = $script -replace "''", "''''"
$Query = "INSERT INTO [Support].[support].[DBScripts]([DBName], [ScriptName], [ScriptDate], [ScriptText]) VALUES (''$DB'',''$File'',''$timestamp'',''$script'')"
Invoke-Sqlcmd -query $Query
}'

EXEC jobs.sp_start_job 'Backup schema'
I get the following errors:
Failed to connect to the target database: Login failed for user 'jobcred'. (Msg 18456, Level 14, State 1, Line 65536)
Failed to determine members of SqlServerTarget (server name 'myserver.database.windows.net,1433', server location 'myserver.database.windows.net,1433'): Login failed for user 'mastercred'. (Msg 18456, Level 14, State 1, Line 65536)
How do I give the credential access to the Azure database?

Thanks.