Sql Server Agent – Step – “SQL Server Integration Services Package” – Error – “Errors were detected in the command line arguments…”

Background

Trying to edit the parameters on a SQL Server Agent Job, but ran into a hard wall.

 

Error Message

Error

Image

Textual

TITLE: Microsoft SQL Server Management Studio

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

 

Advanced Information

Upon clicking the “Advanced Information” we will click the message pasted below.

Image

Textual

===================================

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

——————————
Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Save(JobStepData data, Boolean isSwitching)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.OnGatherUiInformation(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnGatherUiInformation(RunType runType)

Scenario

This problem is reproducible this way.

  1. Start Sql Server Management Studio ( SSMS )
  2. Connect to SQL Server Instance
  3. Access SQL Server Agent \ Jobs \ [Job Name]
  4. Edit a Job Step whose type is “SQL Server Integration Services Package
  5. There are two tabs, Package and Configuration
  6. Access the “Configuration” Tab
  7. Within the “Configuration” Tab there are three Tabs.  The Tabs are “Parameters”, “Connection Managers”, and Advanced
  8. Select the “ExcelConnectionManager_ConnectionString” parameter
    • Click on “ellipse” button
    • The “Edit Literal Value for Execution” window appears
    • Access the “value” textbox and make cosmetic changes
    • Click the OK button
  9. The error, “Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)“, aforementioned appears

 

Quick Points

  1. Other Parameters can be modified
    • This error only occurs when we tried amending the Excel File Connection String
    • We were able to modify other parameters; such as SQL Server and SMTP Server
  2. Connection Manager
    • All Connections ( Excel, SQL Server, and SMTP) can be successfully modified via the Connection Manager Tab

 

Honorable Mention

  1. Cody Konior
    • Agent job errors when running an SSIS package
      Published On :- 2015-August-2nd
      Link

 

SQL Server Versions

Here are the versions that exhibit this issue.

SQL Server Engine

Tabulated

Version Version Literal Version#
SQL Server v2014
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) – 12.0.5540.0 (X64) 12.0.5540.0

 

SQL Server Client

SQL Server Management Studio ( SSMS )

Tabulated

Version Version Literal Version#
SQL Server v2017
v 17.2 14.0.17177.0
SQL Server v2014
SQL Server 2014 SP2 CU10 12.0.5571.0

 

Image

SQL Server v2017

SQL Server v2014

SSIS – “Could not load package because of error 0xC00160AE ” – “Access is Denied”

Error

Error Message

Could not load package “\MSDB\ImportData” because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer “LAB-DB” failed with the following error: “Access is denied.”

Error Image

 

Remediation

Component Services

Global

Objective

Add SQL Server Agent Proxy Account to the local “Distributed COM Users” group.

This allows the proxy account to launch and activate all COM Objects on the local machine.

Image

Specific Objects

Steps

  1. Launch “Component Services” and navigate to each “Microsoft SQL Server Integration Services” component.
  2. Right click on each such component and grant permission to each Integration Service Component.
    • Access the Services Tab
      • Group box :- “Launch and Activation” permissions
        • Access the “Launch and Activation” permissions group box
        • Ensure the “Customize” option is chosen
        • Click the “Edit” button
        • Grant your proxy account “Local Launch” and “Local Activation” access
      • Group box :- Access permissions
        • Access the “Access” permissions group box
        • Ensure the “Customize” option is chosen
        • Click the “Edit” button
        • Grant your proxy account “Local Access
    • Restart relevant Sql Server Integration Services

Images

Microsoft SQL Server Integration Services
Security
Security – Launch and Activation Permission

 

Security – Access Permission

 

Services Applet

SQL Server

msdb

Objective

Please make sure that proxy account has access to the msdb database and that it has been granted membership in the db_ssisoperation role.

Syntax


USE [msdb]
GO
if user_id('account') is null
begin

    CREATE USER [proxyAccount] FOR LOGIN [proxyAccount]

end

ALTER ROLE [db_ssisoperator] 
   ADD MEMBER [proxyAccount]
GO

References

  1. Microsoft
    • Docs
      • Docs / SQL / SQL Server / Integration Services / Service
        • Integration Services Service (SSIS Service)
          Link
    • Technet
      • Integration Services > Integration Services Features and Tasks > Security
        • Integration Services Roles (SSIS Service)
          Link
  2. StackExchange
    • How do I grant the privilege of connecting to SSIS?
      Link

Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Permissions

Introduction

Quick follow-up to our last post, here is how to retrieve permission set for our SSIS Catalog ( SSISDB).

BTW, here is a link to our last post.

 

SQL



use [SSISDB]
go

; with ctePermissionType
(
	  [permissionTypeID]
	, [permissionName]
)
as
(

	select 1, 'Read'

	union
	select 2, 'Modify'

	union
	select 3, 'Execute'

	union
	select 4, 'ManagePermissions'

	union

	select 100, 'CreateObjects'

	union

	select 101, 'ReadObjects'

	union
	select 102, 'ModifyObjects'

	union
	select 103, 'ExecuteObjects'

	union
	select 104, 'ManageObjectPermissions'

)
, ctePermission
as
(

	SELECT DISTINCT 
	      [catalog]
			= cat.[name]

		, [principal]
			= pris.[name]

		, [isDeny]
			= pers.[is_deny]

		, [permisionType]
			= pris.[type] 

		, ctePT.[permissionTypeID]

		, ctePT.permissionName

		, [granted]
			= case
				when pers.is_deny = 1 then 'No'
				else 'Yes'
			 end

	FROM [catalog].[folders] cat

	INNER JOIN [SSISDB].[catalog].[explicit_object_permissions] pers  

		on cat.folder_id = pers.[object_id]

	INNER JOIN [SSISDB].[internal].[get_database_principals]() pris  
		ON pers.principal_id = pris.principal_id  

	INNER JOIN ctePermissionType ctePT
		ON pers.permission_type = ctePT.permissionTypeID

)


select 
		  ctePerm.[catalog]
		, ctePerm.[principal]

		, [read]
			= isNull
				(
						ctePerm.[Read]
					, 'No'
				)

		, [modify]
			= isNull
				(
						ctePerm.[Modify]
					, 'No'
				)

		, [execute]
			= isNull
				(
					ctePerm.[Execute]
					, 'No'
				)

		, [managePermissions]
			= isNull
				(
					ctePerm.[ManagePermissions]
					, 'No'
				)

		, [createObjects]
			= isNull
				(
					ctePerm.[CreateObjects]
					, 'No'
				)

		, [readObjects]
			= isNull
				(
					ctePerm.[ReadObjects]
					, 'No'
				)

		, [modifyObjects]
			= isNull
				(
					ctePerm.[ModifyObjects]
					, 'No'
				)

		, [executeObjects]
			= isNull
				(
					  ctePerm.[ExecuteObjects]
					, 'No'
				)

		, [manageObjectPermissions]
			= isNull
				(
					  ctePerm.[ManageObjectPermissions]
					, 'No'
				)

from 
	(

		select
			  ctePerm.[catalog]
			, ctePerm.[principal]
			, ctePerm.[granted]
			, ctePerm.[permissionName]

		from   ctePermission ctePerm

	)
	ctePerm

pivot (

		 max 
		 (
			[granted]
		 )       

		for ctePerm.[permissionName] in 
		(
			  [Read]
			, [Modify]
			, [Execute]
			, [ManagePermissions]

			, [CreateObjects]
			, [ReadObjects]
			, [ModifyObjects]
			, [ExecuteObjects]
			, [ManageObjectPermissions]
		)
	
	)       

     as [ctePerm]

order by
		  ctePerm.[catalog]
		, ctePerm.[principal]








Output

 

Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Parameters

Introduction

Microsoft continues to build out Sql Server Integration Services (SSIS).

There is support for various package repositories; inclusive are Flat files, MSDB, and SSIS DB.

SSIS Catalog / SSISDB

If you go with SSISDB, it is easy to review the package configuration data.

 

SQL


use [SSISDB]
go

SELECT 
		  [folder] 
			= folders.folder_id

		, [folderName]
			 = folders.[name]

		, [projectName] 
			= projects.[name]

		, [packageName]
			= [packages].[name]

		, [objectType]
			 = case

				when ([paras].[object_type] = 20) then 'Project'
				when ([paras].[object_type] = 30) then 'Package'
				else cast
						(
							[paras].[object_type] 
								as varchar(30)
						)

			   end	

		, [parameterName]
			= [paras].parameter_name

		, [parameterDataType]
			= [paras].data_type

		, [designeDefaultValue]
			= [paras].[design_default_value]

		, [defaultValue]
			= [paras].[default_value]

		, [valueSet]
			= case
				when ([paras].[value_set] =1) then 'Yes'
				else 'No'
		      end

		, [projectDeployTime]
			= convert(varchar(30), [projects].[last_deployed_time], 100)

FROM   [catalog].[folders] folders

INNER JOIN [catalog].[projects] projects 

	ON projects.[folder_id]=folders.[folder_id]

INNER JOIN [catalog].[packages] packages 
	ON packages.[project_id]=projects.[project_id]

INNER JOIN [catalog].[object_parameters] paras 
	ON  ( paras.[object_name]=packages.[name] )
	AND ( paras.[project_id]=packages.[project_id])

 

 

Output

Acknowledgment

Nothing here original.

Trained SQL Server Profiler and catch the SQL it is using.

SQL Server – Integration Services – Error – “Could not load package because of error 0x80070057. The path is not valid.”

 

Background

Our Business Intelligence person came to me with what turned out to be another good error last night.

Error

Error Image

jobHistory-20170125-0721AM

 

Error Textual

Could not load package because of error 0x80070057.  The path is not valid.

The path could not be loaded.

 

TroubleShooting

SQL Server Profiler

Launched the pre-eminent SQL Server troubleshooting tool for lazy Admins, SQL Server Profiler.

Configuration

Configured it to include:

  1. Events
    • User Error Message
  2. Attributes
    • DatabaseName
    • Error

 

Events

 

SSP-20170124-1105PM (cleaned-up)

 

Findings

  1. Execute permission was denied on the object ‘sp_ssis_getpackage’

 

Remediation

As the job’s step is using a proxy account, we added the proxy account to the db_ssisoperator role ( in the msdb database ).

 
set nocount on;
go
 
use [msdb]
go
 
declare @rolename sysname
declare @username sysname
 
set @rolename = 'db_ssisoperator'
set @username = 'domain\SSISProxy'
 
/*
    Create user, if it does not exist
*/
if suser_id(@username) is null
begin
 
    create user [domain\SSISProxy]
        from login [domain\SSISProxy];
 
end
 
/*
 
    IS_ROLEMEMBER
        <a href="https://msdn.microsoft.com/en-us/library/ee677633.aspx">https://msdn.microsoft.com/en-us/library/ee677633.aspx</a>
 
    sp_addrolemember
        <a href="https://msdn.microsoft.com/en-us/library/ms187750.aspx">https://msdn.microsoft.com/en-us/library/ms187750.aspx</a>
 
*/
if ( IS_ROLEMEMBER ( @rolename, @username)  = 0)
begin
 
    print 'Grant ' + @username + ' access to role ' + @rolename
 
    exec sp_addrolemember
              @rolename   = @rolename
            , @membername = @username
            ;
 
end
 
go

 

 

SQL Server – Integration Services – Network Flow and Rules

Background

Wanted to cover the Network Ports that are used by Microsoft’s Integration Services.

Network Trace

Wireshark

Port Mapper ( Port 135 )

Network Flow

rpc-20160106-1111pm-cleanedup

 

Explanation

  1. From Ephemeral Port ( 57916) connect to Server Port 135
  2. Request from client to server to issue RemoteCreateInstance
  3. Authenticate User
    • via NTLMSSP_AUTH
    • Pass in username

 

Integration Services

Network Flow

ssis-20170106-1122pm-brushed-up

 

Explanation

  1. From Ephemeral Port ( 57917) connect to SQL Server Integration Services Component
  2. This is important has it depends on how thru Component Services the Integration Services Component’s endpoint is configured

 

Component – Microsoft SQL Server Integration Services [NN.MM]

Using Component Services, let us review the Component’s endpoint configuration

 

Configuration

Here are our choices:

  1. Disable Protocol sequence
  2. Use default endpoints
  3. Use static endpoint
  4. Use intranet range of dynamic endpoints
  5. Use internet range of dynamic endpoints

 

Digging Deeper
  1. Disable Protocol sequence
    • Disable Network
  2. Use default endpoints
    • Use ephemeral ports
  3. Use static endpoint
    • Use static endpoint
  4. Use intranet range of dynamic endpoints
    • Use endpoint’s defined for Intranet
  5. Use internet range of dynamic endpoints
    • Use endpoint’s defined for Internet

 

Our Choice

To streamline our conversation with the Firewall team, we chose to use a static endpoint

dcomendpointconfigurations-usestaticport

 

 

NetLogonSAMAccount

Network Flow

rpcnetlogon_20170107_1206am-brushed-up

 

Explanation

This area covers the Network Authentication.

We did not have to make special care in our environment and so I can not cover in details.

But, please keep it mind when connecting between hosts that are not in the same Active Directory Domain, etc.

 

Component – Windows Management & Instrumentation ( WMI )

Network Flow

wmi-20170106-1144pm-brushed-up

 

Explanation

  1. From Ephemeral Port ( 57919) we connect to the port we dedicated to WMI
  2. This is important has it depends on how thru Component Services the WMI Component’s endpoint is configured
Configuration

Using Component Services, we will configure Windows Management and Instrumentation to listen on a specific port

wmi-configure-staticport

 

Network Listening Ports

Resource Monitor

On newer MS Windows Oses, you will be well served to remote connect to the Integration Services host, and run Resource Monitor

MsDtsSrvr.exe

msdtssrvr-20170107-0617am

Explanation

We can see that MsDtsSrvr.exe is:

  1. listening on Network Port 50000
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

RPCC – svchost (winmgmt)

svchost-rpcss-20170107-0640am

 

Explanation

We can see that svchost.exe ( RPCSS ) is:

  1. listening on Network Port 135
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

Unlike Integration Service which has its own process, RPCSS is being processed by a svchost.exe process.

 

Windows Management & Instrumentation – svchost (winmgmt)

 

svchost-exe-winmgmt-20170107-0630am

 

Explanation

We can see that svchost.exe ( winmgmt ) is:

  1. listening on Network Port 50090
  2. We have a record each for IPv4 and IPv6
  3. The internal MS Windows Firewall is allowing access to the Port

Unlike Integration Service which has its own process, winmgmt is being processed by a svchost.exe process.

 

Tabulated View

Objective Filter
 RPC Port Mapper Port 135
 Microsoft Integration Services Ephemeral Ports
Static Port
Intranet range of dynamic endpoints
Internet range of dynamic endpoints
 Network Authentication Ephemeral Ports
 Windows Management and Instrumentation ( WMI ) Ephemeral Ports
Static Ports
Intranet range of dynamic endpoints
Internet range of dynamic endpoints