{"id":688,"date":"2018-08-17T01:06:20","date_gmt":"2018-08-16T23:06:20","guid":{"rendered":"https:\/\/sql.marcus-belz.de\/?p=688"},"modified":"2020-05-09T02:26:03","modified_gmt":"2020-05-09T00:26:03","slug":"ssis-vs-transact-sql-impersonation","status":"publish","type":"post","link":"https:\/\/sql.marcus-belz.de\/?p=688","title":{"rendered":"SSIS vs. SQL \u2013 Impersonation"},"content":{"rendered":"

Overview<\/h2>\n

SQL Server Integration Services<\/strong> (SSIS) is a very powerful graphical tool set belonging to the Microsoft BI Stack along with SQL Server, SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), etc. It supports a broad variety of data migration, integration and transformation tasks. With that, you will find a bunch of good reasons why to utilize this tool set. However, there are a plenty of reasons why not to use SSIS. With Transact SQL (T-SQL) Microsoft provides with respect to transformation tasks an equivalent technique.<\/p>\n

This article belongs to a series of articles<\/a> that are dealing with some important criteria for choosing the right technology(ies) \u2013 SSIS and\/or T-SQL.<\/p>\n

If an SQL Agent job is started, the individual steps in the job are executed out by default in the security context of the service account of the SQL Server Agent.<\/p>\n

When installing the SQL Server Agent, a service account must be specified under which the SQL Server Agent<\/strong> service is to be started. The user can choose between specifying an explicit account and selecting the local system account NT-AUTORITY\\System<\/strong>.<\/p>\n

When selecting a service account, the specified service account has a set of permissions that do very likely not match the requirements for all SQL Server Agent jobs.<\/p>\n

The local system account NT-AUTORITY\\System<\/strong> has extensive permissions to local resources and is a member of the Windows Administrators<\/strong> group. With that, SQL Server Agent Jobs can access resources that are not required or that may even have to be protected against unauthorized access. On the other hand, the system account generally has no or insufficient permissions to file shares, data sources, etc.<\/p>\n

According to the need-to-know principle<\/strong>, the user, under which a job is executed, should only have the necessary authorizations that are necessary for the completion of the task \u2013 here the execution of the SQL Server Agent job.<\/p>\n

Since numerous jobs are regularly configured in the SQL Server Agent, which all have very different tasks to accomplish and require specific resources, it is not possible to provide the service account with the appropriate rights according to the need-to-know principle.<\/p>\n

Microsoft offers a solution to this problem with the configuration of the executing user of a SQL Server Agent job step. The technical term for this is impersonation<\/strong>. Impersonation takes place at step level of SQL agent jobs in which SSIS packages are to be executed by specifying a so-called proxy user<\/strong>. Proxy users are configured in SQL Agent within the folder SQL Server Agent | Proxies | SSIS Package Execution<\/strong> and they reference a credential (Credential<\/strong> object) of an SQL Server instance, which is configured in the folder Security | Credentials<\/strong> of the respective instance. The credential is linked to a database login (SQL Server authentication or Windows authentication), which must be authorized according to the requirements in the database, file shares, etc.<\/p>\n

All artifact types that can be configured as a step support the Run As<\/strong> Property, except the type Transact-SQL Script<\/strong>. ETL processes that were created exclusively on the basis of Transact SQL can therefore not be impersonated via the SQL Server Agent.<\/p>\n

ETL processes that have an SSIS package as the starting point can be impersonated via the SQL Server Agent. Using an SSIS package as the starting point of an ETL process does not necessarily mean that the entire ETL route must be implemented in SSIS. The ETL process can also be implemented on the basis of Transact-SQL. In this case, the scripts, stored procedures, stored functions etc. must be executed via SQL command tasks in an SSIS package.<\/p>\n

Summary<\/h2>\n