Azure elastic database jobs

  • Roust_m

    SSCoach

    Points: 17364

    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.

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Andrey

    Mr or Mrs. 500

    Points: 554

    Privet!

     

    Have a look here:

    https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-overview

     

  • Roust_m

    SSCoach

    Points: 17364

    Spasibo!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply