SQL Server – Running SQL Server Agent Jobs with least privilege accounts – TroubleShooting – Day 1

 

Background

Here are some of the errors that we ran into while configuring our SQL Server Agent Jobs to run with an Account that has streamlined permission sets.

 

Errors

ActiveX Scripting

Error – Script does not destroy all the objects

Error Image

ScriptDoesNotDestroyAllTheObjects

 

Error Text

 
The command script does not destroy all the objects that it creates. Revise the command script
 

 

Remediation

Look through your code and please make sure that you have corresponding release statements for all your create objects.

We had a lone createActiveXObject and we forgot the corresponding set object to Nothing.

Error – Error authenticating proxy …. The user name or password is incorrect

Error Image

userOrPasswordIsIncorrect - 20170113 - 0737PM

 

Error Text

 
Unable to start execution of step 1 (reason: Error authenticating proxy … system error: The user or password is incorrect)
 

Remediation

Specify valid user credentials for your credential

USE [master]
GO

ALTER CREDENTIAL [credentialBISSQL]
WITH
        IDENTITY = N'HRDBMirr\BISDBSQLSvc'
      , SECRET = N'786544'
GO

 

 

Error – Proxy is not allowed for subsystem “ActiveScripting”

Error Image

grantloginisnotallowed_2017013_0422pm

 

Error Text


Proxy (37) is not allowed for subsystem "ActiveScripting" and user "LABDB\DBSQLSvc". 
Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy. (.Net SqlClient Data Provider) 

 

Remediation

In our case we already granted our proxy access to the ActiveScripting subsystem.

Additionally, we needed to grant access our login access to the Proxy, as well.


USE msdb ;  
GO  

declare @loginName     sysname
declare @proxy	       sysname
declare @commit	       bit

set @loginName = 'LABDB\BISDBSQLSvc'
set @proxy = 'proxyBISSQL'

 
EXEC dbo.sp_grant_login_to_proxy  
          @login_name = @loginName
	, @proxy_name = @proxy

;

 

 

Error – ActiveScripting

Error Image

permissiondeniedonline89_20170113_0436pm

Error Text


Executed as user.  Error code: 0.  Error Source = Microsoft VBScript runtime error.  Error Description: Permission Denied.  Error on line 89.
 

Remediation

While running our ActiveXObject Script, the system ran into a couple of potholes.

In our case, it was File System permissions.

File System – Permission – Original

Here is our original File System Permission set

fspermissions_before_20170113_0451am

 

File System – Permission – Revised

fspermissions_after_20170114_0457am

Explanation
  1. As the folder is a log folder, we granted write full permissions to it.
  2. A more careful Admin will likely only grant create, modify, list folder contents, and Read

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s