Sql Server – Integration Services Catalogs – Permission Management Using SSMS

Background

Let us quickly review how we can use SQL Server Management Studio (SSMS) to manage the Integration Services Catalogs.

 

Lineage

Here is our journey so far …

  1. Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Parameters
    Link
  2. Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Permissions
    Link

 

 

SQL Server Management Studio ( SSMS )

Outline

Here are the steps:

  1. Launch SQL Server Management Studio ( SSMS)
  2. Connect to the SQL Server Instance
  3. Under the SQL Server Instance, if an Integration Service Catalog has previously been created, look for the Integration Services Catalog Node
  4. Under the Integration Services Catalog Node, the database will appear
  5. Access the database, review the list of folders listed
    • Under each folder, Projects and Environments are listed
  6. We will be granting permissions at the Folder level
  7. Return to the Folder, right click on the Folder and from the dropdown menu select “Folder Properties”
  8. The Folder Properties window appears
    • In the Folder Properties window there are two pages, the General and Permissions
  9. In the Folder Properties window access the Permissions page
  10. The permissions available at the Folder Level appears
    • The permissions available includes Read, Modify, Manage Permissions, Create Objects, Modify Objects, Execute Objects, Read Objects, and Manage Object Permissions
    • Delegation of Tasks
      • For developers, the most common permissions to grant are the read permissions ( Read & Read Objects )
      • For Operation DBAs, all permissions outside of execute permissions can be granted
      • Ffr the Proxy Service account that scheduled jobs will run under, please grant the Read and Execute permissions

 

Image

Object Explorer

Folder Properties

Folder Properties – Permissions

 

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

SQL Server – Integration Services – Connectivity – Issue – “Class Not Registered” / Address by installing ssms /w same version as SSIS Server

Background

This is another post along our series on how to address an inability to connect to SQL Server Integration Services ( SSIS ) from a remote client.

Errors

Here is the error message.

Error – “Class Not registered”

Error Message

Image

ClassNotRegistered-cutomized-_thumb2

 

Textual

 
Failed to retrieve data for this request.
 
(Microsoft.SqlServer.Management.Sdk.Sfc)
 
For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476">http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476</a>
 
------------------------------
 
Connecting to the Integration Services service on the computer "LABDB" failed with the following error: "Class not registered".
 
This error can occur when you try to connect to a SQL Server 2005
 
Integration Services service from the current version of the SQL Server
 
tools. Instead, add folders to the service configuration file to let
 
the local Integration Services service manage packages on the SQL
 
Server 2005 instance.
 

 

Diagnostic

In our environment, we thankfully observed that users running SSMS from a SQL Server 2014 install can connect, but not so with users running the v2016 version.

Things are starting to make sense as DCOM is version specific.

Remediation

If Server is running v2016 or later, Download & Install SQL Server Management Studio [version]

Starting from v2016, SSMS can be downloaded and installed as a standalone product.

 

If Server is running v2014 or earlier and No current client install, download & Install SQL Server Management Studio [version]

If Server is on v2014 or earlier and you do not have a current SQL Server install, you should be able to download SSMS versioned for the server and install it.

 

Download

We want v2014 and so we go here.

File Details

download-details

Choice

From the list above, as we want the full SQL Server Express Advanced Services, we choose “MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe“.

The file name translates to:

  • MgmtStudio
    • Management Studio
  • 64Bit
    • Our OS is 64-bit
  • ENU
    • English

If Server is running v2014 or earlier and Current client install that you will like kept, download & Install SQL Server Express Advanced Services

 

Download

We want v2014 and so we go here.

File Details

download-details

 

Choice

From the list above, as we want the full SQL Server Express Advanced Services, we choose “ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe“.

The file name translates to:

  • Express
    • Express Engine
  • Tools
    • The SQL Server Tools are inclusive
  • 64Bit
    • Our OS is 64-bit
  • ENU
    • English

 

Install

ScreenShots

Installation Type

Choices:

  1. Choose to “Perform a new installation of SQL Server

 

SQLServer-InstallationType

 

 

License Terms

Choices:

  1. Choose to “Accept the License Terms

 

SQLServer-LicenseType

 

Feature Selection -01

Choices:

  1. Checked “Management Tools – Basic” & “Management Tools – Complete

 

FeatureSelection

 

Feature Selection –02

Choices:

  1. Checked
    • Instance Feature
      • Database Engine Services
    • Shared Features
      • Management Tools – Basic & Management Tools – Complete

 

SQLServer-FeatureSelection-20170105-1057AM

 

Instance Configuration

Choices:

  1. Named Instance
    • Give the instance a name
      • In our case, we name it v2014
        • This signifies to us that it is a SQL Server Version 2014

SQLServer-InstanceConfiguration

 

Explanation:

  1. Notice that the targeted folders will reflect the name given to the Instance

 

 

Server Configuration

Choices:

  1. Services
    • Please change Startup Type from Automatic to Manual
      • In our case, we do not intend to use the local services, just the client.
      • And, so it is likely OK to change the “Startup Type” from Automatic to Manual or Disabled

sqlserver-serverconfiguration

 

 

Database Engine Configuration

Choices:

  1. We will ignore as we do not really care about the Database Engine features.

 

databaseengineconfiguration-brushed-up

 

 

Installation Progress

 

We just have to wait for the Install to progress & complete.

 

Validate

Validate by running the Version specific Management Studio

 

Microsoft Connect Items

  1. SSMS 2016 error connecting to Integration Services on a SQL Server 2008 R2 instance – by G Britton
    • ID :- 2779968
    • Opened By :- G Britton
    • Date Published :- 2016-Jun-3rd
    • Link