SQL Server Agent – Error – “Failed to initialize sqlcmd library with error number -2147467259”

Background

Experienced error running SQL Server Agent job that we are developing.

Error

Error Image

Error Message

Executed as user: LAB\mssql. @profileName :- DBA Mail [SQLSTATE 01000] (Message 0) @recipientsTo :- daniel@lab.org [SQLSTATE 01000] (Message 0) @subject :- Privilege Users on HRDB [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050) @body :- [SQLSTATE 01000] (Error 0). The step failed.

Explanation

  1. The error message states “Failed to initialize sqlcmd library with error number -2147467259.
  2. Unfortunately the error message is not precise

 

TroubleShooting

SQL Server Profiler

Trace Definition

Events Selection

Image

Trace Captured

Image

Explanation

  1. Event
    • Event Class :- User Error Message
    • TextData :- The EXECUTE permission was denied on the object ‘sp_DBRoleMembersList’, database ‘master’, schema ‘dbo’.
    • Application Name :- SQLCMD
    • Error :- 229

SQL Server Agent

SQL Server Agent Account

Let us get SQL Server Agent Account

SQL


select 
        tblSDSS.[servicename]
      , tblSDSS.[service_account]
from   sys.dm_server_services tblSDSS

Output

Explanation

  1. We have the service accounts that we are using for the SQL Server Engine and Agent

Remediation

Grant “SQL Server Agent” service account execute permission on targeted object.

SQL Server Agent – Message – “Failed to notify…”

Background

I have been tending to a job that often fails, and wanted to add email notification, for the last couple of weeks.

It is bad that enough that the job itself fails, but worse no email.

 

Monday Morning

Monday Morning is often a good day of the week to make use of another carefree weekend and see where the error lies.

Error Message

Image

 

Textual

Message
The job succeeded. The Job was invoked by Schedule 25 (Every Day). The last step to run was step 12 (Say Completed). NOTE: Failed to notify ‘Daniel Adeniji’ via email.

 

TroubleShooting

SQL Server Agent Error Log

here is what is logged in log file for SQL Server Agent.

Please note we are referencing SQL Server Agent log files, and not SQL Server Engine.

Image

Textual

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail

 

Remediate

Access SQL Server Agent Properties and set SQL Mail…

GUI

Unset

When Unset

Here is how things look before configuring SQL Server Agent Mail Profile

 

Set

Us setting SQL Server Agent Mail Profile

Script

Read SQL Server Agent Properties

Code


declare @useDatabasemail     int
declare @DatabaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @useDatabasemail OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @DatabaseMailProfile OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @AgentMailType OUT
        , @no_output = N'no_output'



--NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
EXEC master.sys.xp_instance_regread 
          'HKEY_LOCAL_MACHINE'
        , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
        , N'Start'
        , @ServiceStartMode OUTPUT



--declare @ServiceAccount nvarchar(512)
EXEC master.sys.xp_instance_regread
           'HKEY_LOCAL_MACHINE'
         , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
         , N'ObjectName'
         , @ServiceAccount OUTPUT


exec master.dbo.xp_instance_regread 
              N'HKEY_LOCAL_MACHINE'
            , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
            , N'AGTGroup'
            , @AgtGroup OUTPUT

select 
          [@use_databasemail] = @useDatabasemail
        , [@DatabaseMailProfile] = @DatabaseMailProfile      
        , [@AgentMailType] = @AgentMailType
        , [@ServiceStartMode] = @ServiceStartMode
        -- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
        , [@ServiceStartModeLiteral] 
                = case  
                        when @ServiceStartMode=2  then 'Auto Start'
                        when @ServiceStartMode=3  then 'Don''t Auto Start'
                  end   
        , [@ServiceAccount] = @ServiceAccount
        --, [@AgtGroup] = @AgtGroup

Output

Set SQL Server Agent Properties

Code


USE [msdb]
GO

set nocount on
set XACT_ABORT on
go

declare @sqlInstance                sysname
declare @databaseMailProfileDesired sysname

declare @FORMAT_MESSAGE_SET         nvarchar(600)
declare @message                    nvarchar(600)

declare @databaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

declare @commit              bit

set @commit =1
set @sqlInstance = cast(SERVERPROPERTY('servername') as sysname)
set @databaseMailProfileDesired = N'DBAMail'

set @FORMAT_MESSAGE_SET = 'On %s, setting database mail profile to %s'

/*
    Access Registry (SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent') 
        a) Read Database Mail Profile
*/
exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @databaseMailProfile OUT
        , @no_output = N'no_output'

print '@DatabaseMailProfile :' + isNull(@DatabaseMailProfile, '')

begin tran
    

    if  
        ( @DatabaseMailProfile is null )
    begin

        exec master.dbo.xp_sprintf      
                  @message output
                , @FORMAT_MESSAGE_SET
                , @sqlInstance
                , @databaseMailProfileDesired

        print @message

        /*
            Set SQL Sql Server Agent Properties
        */
        EXEC msdb.dbo.sp_set_sqlagent_properties 
                  @email_save_in_sent_folder=1
                , @databasemail_profile=@databaseMailProfileDesired
                , @use_databasemail=1
                , @alert_replace_runtime_tokens=1


    end

while (@@TRANCOUNT > 0)
begin

    if (@commit = 1)
    begin

        print 'commit tran'

        commit tran

    end
    else
    begin

        print 'rollback tran'

        rollback tran

    end
end

GO

Output

Reset SQL Server Agent Properties

To revert changes, please run the script below.


USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties
          @email_profile=N'' 
        , @databasemail_profile=N''
        , @use_databasemail  =0
GO


Listening

Listening to …

Jazmine Sullivan – In Love With Another Man
Link

 

SQL Server Agent – Output File Name – Tokenization

Background

I have always been perplexed by the fact that Sql Server Management Studio does not allow us to indicate that we desire timestamp file names for SQL Server Agent Jobs Logging.

SSMS

SQL Server Agent

Job Step Properties

Job Step Properties – Advanced

Initial
Image

Explanation
  1. Output file

 

Usage

Yesterday, I found out that timestamps are actually supported.

General – Image

Advanced – Image

Advanced – Textual

E:\Microsoft\SQLServer\SQLServerAgent\Log\DatabaseBackupFiles_$(ESCAPE_SQUOTE(JOBID))$(ESCAPE_SQUOTE(STEPID))$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

 

Payload

cleanupFilesBak.cmd

Script


@echo off

REM How to use multiple commands in batch using forfiles command
REM https://stackoverflow.com/questions/23318731/how-to-use-multiple-commands-in-batch-using-forfiles-command

setlocal

set "_folderBase=G:\Microsoft\SQLServer\Backup"
set "_SQLInstance=%computername%"
set "_folderTarget=%_folderBase%\%_SQLInstance%\"

set "_fileExt=*.bak"

set "_daysPast=1"

set _appPgm=echo 

REM Added period (.) after folder name for /p argument
if not exist "%_folderTarget%." mkdir "%_folderTarget%"

forfiles /P "%_folderTarget%." /s  /m %_fileExt% /d -%_daysPast% /c ^"cmd /c ^
%_appPgm% ^
  file name is @PATH ^
  file size is @FSIZE ^
  file date is @FDATE ^
^"

endlocal

Output

Explanation

  1. DatabaseBackupFiles_[JobID]_[stepID]_[DateYYYYMMDD]_[timeHHMMSS].txt

 

Tokens

Token Description Version Active
A-DBN Active Database name
(A-SVR) SQL Server Name
(JOBNAME) The name of the Job Not active as of v2014/SP2
(STEPNAME) The name of the step Not active as of v2014/SP2
(DATE) Current date (in YYYYMMDD format).
(INST) Instance name. For a default instance, this token will have the default instance name: MSSQLSERVER.
(JOBID) Job ID
(MACH) Computer name
(SQLDIR) The directory in which SQL Server is installed. By default, this value is C:\Program Files\Microsoft SQL Server\MSSQL.
(SQLLOGDIR) Replacement token for the SQL Server error log folder path – for example, $(ESCAPE_SQUOTE(SQLLOGDIR)).
(STEPID) Step ID.
(SRVR) Name of the computer running SQL Server. If the SQL Server instance is a named instance, this includes the instance name.
(TIME) Current time (in HHMMSS format).
(STRTTM) Current time (in HHMMSS format).
(STRTDT) The date (in YYYYMMDD format) that the job began executing.

 

Dedicated

Dedicated to Ola Hallengren as I found out about this functionality through his script.

And, also to Dave Benham.

Dave has a nice explanation on how to escape commands in batch files and thus span multiple lines.

His explanation is here and it is in response to a Stackoverflow question.  The Question is titled “How to use multiple commands in batch using forfiles command“.

 

Listening

Listening to “Drifting Spirits” @ Teavolve.

Link

References

  1. Docs / SQL / SSMS / Agent
    • Use Tokens in Job Steps
      Link
  2. Thomas Lind
    • SQL Agent job logging with tokens
      Link

 

SQL Server Agent- Execution Error – “Failed to execute IS server package because of error 0x80131904”

 

Background

I have a couple of SSIS packages to deploy from QA to Production.

Deploy the SSIS packages and ran the SQL Server Agent jobs creations, as well.

Ran into errors and one of them is referenced below.

 

Error

Image

Textual

Date        8/9/2017 3:37:21 PM
Log        Job History (DataRefresh_pp_dem_PROD_to_DEV)

Step ID        1
Server        P-D-RED
Job Name        DataRefresh_ppsivr_dem_PROD_to_DEV
Step Name        DataRefresh_dem_PROD_to_DEV
Duration        00:00:01

Message
Executed as user: LAB-DOM\svcProxy. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  3:37:22 PM  Failed to execute IS server package because of error 0x80131904. Server: p-db-red, Package path: \SSISDB\SSISDB\DataRefrehPackage_Ppsivr_Dem_PROD_To_DEV\DataRefresh_Dem.dtsx, Environment reference Id: NULL.  Description: Login failed for user ‘LAB-DOM\svcProxy’.  Source: .Net SqlClient Data Provider  Started:  3:37:22 PM  Finished: 3:37:22 PM  Elapsed:  0.562 seconds.  The package execution failed.  The step failed.

 

Remediation

For us the problem was that the script we used for creating the SQL Server Agent job was hardcoded to look for the SSIS package in the originating SQL Instance’s SSISDB database.

The fix is to change the Server’s text box text to have the deployed SQL Instance name or simply the generic SQL Server Instance Identifier (local); especially if it is the default SQL Instance.

 

JobStepProperties_2A_20170809_0421PM (BrushedUp)

SQL Server Agent Roles

Background

We are evaluating using BMC’s Control-M, our corporate IT Job Management tool.

And, wanted to see what we will have to do to get it to work against SQL Server Instances.

 

SQL Server Agent Roles

In SQL Server, jobs are managed through SQL Server Agent.

PreDefined Roles

SQL Server Agent has predefined security roles.

SQL Server Management Studio ( SSMS )

Jobs are saved in the system database, msdb.

To view the roles, please do the following:

  1. Launch SQL Server Management Studio (SSMS)
    • Connect to the SQL Server Instance
    • Choose System Databases
    • From the list of System Databases, choose msdb
    • Within the msdb database, transverse to Security \ Roles \ Database Roles
    • The SQL Server Agent roles are noted having names that start with SQLAgent

       

 

Permission Set

Let us dig deeper into these roles and see what they afford us, where they are different, and what is the minimum we can get away with.

Concentric

The roles are listed in increasing order of privileges assigned.

More precisely as Microsoft would say it, they are concentric.

Looked up the term concentric and here is how it is defined:

They are of or denoting circles, arcs, or other shapes that share the same center, the larger often completely surrounding the smaller.

And, so we can see that MSFT’s documentation is very useful, and here it is in verbatim:

Link
The SQL Server Agent database role permissions are concentric in relation to one another — more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). 

Tabulated

Role Definition Details
SQLAgentUserRole Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.  a) Have permission on owned jobs
SQLAgentReaderRole SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. a) List all jobs – their properties, schedules, and executionhistory
SQLAgentOperatorRole SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. a) Manage ( enable or disable jobs, edit job steps )

b) They can execute, stop, or start jobs

c) Delete job execution history

 

Which Role?

For system jobs we do not want an external job manager as SQL Server Agent is able to do so sufficiently.

We only want an external job manager for specific jobs.

Let us review the predefined system roles and judge their appropriateness for what we have in mind:

  1. SQLAgentOperatorRole
    • Affords all roles to all jobs
    • Too much for us
  2. SQLAgentReaderRole
    • (+)
      • Able to create and and manage own job
      • Read privileges on all jobs; their steps, schedule, and run history
    • (-)
      • Job Management does not need to view job data nor review job run history
        • A bit much for our targeted need
  3. SQLAgentUserRole
    • Requirements
      • (+)
        • Create own jobs
        • Run owned jobs
          • Existing jobs ownership can be re-assigned
      • (-)
        • Job has be owned
          • We have to review what is the ramification of changing job ownership for each specific job

 

Follow Up

Our follow-up task is to review the impact of changing job ownership for specific jobs.

 

References

  1. Microsoft
    • SQL Server Agent Fixed Database Roles
      Link
    • Implement SQL Server Agent Security
      Link

 

SQL Server Agent Jobs and extraneous contents in logged messages

Background

This morning I needed to dig into a SQL Server Job that was failing, but unfortunately the error was not being exposed\surfaced.

 

Message

Message – Image

Message – Text

Executed as user: SQLADMIN. Jul 11 2017 12:19PM – Transaction Begin …. [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Transaction Began [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Preparing adding records into [UserAcct].[UserActivity] … [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Adding records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Update records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committing Transaction [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committed Transaction [SQLSTATE 01000] (Message 0). The step succeeded.

 

Explanation

  1. If we take a deep look into the messages pasted above, we will notice multiple occurrence of “[SQLSTATE 01000] (Message 0)

 

Q/A

Googled and found a very helpful comment from Tibor Karaszi.

Tibor Karaszi

How do I suppress [SQLSTATE 01000] messages in sp output?

Link

 

Troubleshooting

SQL Profiler

Ran SQL Server Profiler and here is a screenshot:

Image

 

Outline

  1. SQLAgent – Generic Refresher
  2. SQLAgent – Job Manager
  3. SQLAgent – TSQL JobStep ( Job 0x####…. : Step 1)
    • — network protocol: LPC
      set quoted_identifier on
    • set quoted_identifier off
    • select @@microsoftversion
    • select convert(sysname, serverproperty(N’servername’))
    • SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
    • SET TEXTSIZE 1024
    • print ‘hello’

Detail

Hello

Hello

  1. Application Name :- SQLAgent – TSQL jobstep ( Job 0x )
  2. NTUserName :- SQLServerAgent
  3. LoginName :- NT Service\SQLServerAgent
  4. Client Process ID :- 2636

 

Task Manager

Reviewed “Task Manager” and confirmed that the process name for process ID is SQLAgent.exe

Remediate

SQL Server Agent Step

Current Job Step Configuration

Revised Job Step Configuration

Changes

  1. Type
    • Original :- Transact-SQL Script (T-SQL)
    • Revised :- Operating System (CmdExec)
  2. Command
    • Original :- exec [csdemo].Products.UpdateCertificatesUserDataAutoConfirm
    • Revised :- sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d demo -Q “exec [csdemo].[Products].[UpdateCertificatesUserDataAutoConfirm]” -b

Explanation

We changed from the internal Transact SQL Script tooling built into SQL Server Agent unto sqlcmd.exe

Sqlcmd exposes a lot more functionality.

 

Recap

Logged Messages

Logged Messages – Original

Logged Messages – Revised

 

Dedicated

Can’t go anywhere but to Tibor Karaszi, SQL Server MVP.

Like Eli Manning an MVP.