In our recent articles published on this forum, we have been discussing different methods of executing SQL Server 2012 Integration Services packages deployed by employing the traditional Package Deployment Model, focusing in particular on those stored in the .dtsx file system format. While in a majority of cases the process we have described works as expected, there are some caveats that need to be considered if your tend to store SSIS packages on network-accessible file shares (rather than locally on the same system where packages are running). We will discuss the technical reasons for these considerations and present a couple of methods for addressing them.
The issue we are referring to results from security restrictions that apply when dealing with so called two hop authentication. Before exploring characteristics of this phenomenon, let’s briefly review a more common scenario that involves the authentication mechanism known as impersonation. Its primary purpose is to allow system processes and applications to run tasks on behalf of a user. Impersonation is employed whenever a user starts a new process or application, so their tasks execute in the user’s security context and their security boundaries reflect the user’s privileges. This way, when a user launches a Command Prompt or Windows Explorer, their capabilities are limited by what the user account is allowed to do. However, impersonation has its limitations. While it works fine for processes operating on the same system on which a user is logged on, remote processes (i.e. these created on systems remote to the user) can take advantage of impersonation only when accessing their local resources (resources residing on the same remote computer). In other words, if user X who has logged on to computer A launches a process on computer B, then this process will not be able to access resources on computer C in the security context of user X, as long as impersonation is being used.
In order to eliminate this limitation, you can enhance your authentication process by employing delegation. In simple terms, this grants an intermediary system (computer B in our example) the ability to relay the credentials of user A. While it is possible to implement such relay without any restrictions, this would obviously constitute a potential security vulnerability (in case a computer to which credentials are being forwarded has been compromised), so there are ways to minimize its scope (such as identifying target systems or even individual services running on them that are considered to be trusted). Any seasoned database administrator is likely to be quite familiar with this concept, since delegation frequently comes into play when configuring linked servers or SQL Server Reporting Services.
In its traditional form, delegation is fairly restrictive (in terms of its prerequisites) and relatively cumbersome to configure. First of all, since delegation is the function of the Kerberos protocol, all relevant security principals (computers and users) need to reside in the same or trusted Active Directory forest (with forest-level trusts between them). User accounts whose credentials will be relayed should be considered as non-sensitive (this can be enforced by using the Account is sensitive and cannot be delegated checkbox, accessible on the Account tab of the user’s account Properties dialog box in the Active Directory Users and Computers console). Accounts of intermediary computers involved in delegation must be configured as trusted for delegation (this is done by checking the Trust computer for delegation checkbox on the General tab of the computer’s account Properties dialog box in the Active Directory Users and Computers console). You also need to ensure that each of the endpoints involved in delegation has properly configured Service Principal Name (SPN). Finally, you should also ensure that servers communicate using TCP/IP Net Library, since this is the only one which supports Kerberos based authentication (we will be describing this procedure in more detail in one of our upcoming articles).
Starting with the release of Windows Server 2008 and Windows Vista (and, afterwards, backported to Windows XP Service Pack 3), Microsoft introduced an alternative to the Kerberos-based delegation in the form of Credential Security Service Provider (CredSSP) WinRM authentication provider, which allows delegation of explictly stated user’s credentials from computers configured as CredSSP clients to computers hosting CredSSP server role. With its default settings, you need to enable both in order to take advantage of the underlying functionality. This is accomplished by carrying out the following actions:
- executing Enable-WSManCredSSP cmdlet with the -Role Client switch on the source computer (the one from which the launch of a remote process will be triggered and from which corresponding user credentials will originate), followed by the -DelegateComputer switch referencing target computers to which delegation will be allowed. These computers can be identified individually by their names or collectively through the use of wildcards, allowing you to specify all hosts (*) or all domain members (*.DomainName.local). Note that when using the last of these three options, you need to specify the fully qualified name of the intermediary system (computer B from our example) when connecting to it.
Alternatively, you can implement the same functionality by enabling the CredSSP authentication provider with the winrm command-line utility (which involves executing winrm set winrm/config/client/auth ‘@{CredSSP=”true”}’) or by configuring the Group Policy Computer ConfigurationAdministrative TemplatesWindows ComponentsWindows Remote Management (WinRM)WinRM Client setting named Allow CredSSP authentication. In addition, in order to designate target systems, you could leverage the Computer ConfigurationAdministrative TemplatesSystemCredentials Delegation setting named Allow Delegating Fresh Credentials, which would include one or more of Service Principal Names with the WSMAN prefix (designating WinRM service type) in one of the following formats (corresponding to the -DelegateComputer switch values we discussed earlier):
- WSMAN/ComputerName.DomainName.local
- WSMAN/*
- WSMAN/*.DomainName.local
- executing Enable-WSManCredSSP cmdlet with the -Role Server switch on the intermediary computer (the one where the remote process will be running and to which corresponding user credentials will be delegated). Alternatively, you can implement the same functionality by enabling CredSSP authentication provider with the winrm command-line utility (which involves executing winrm set winrm/config/service/auth ‘@{CredSSP=”true”}’) or by configuring the Group Policy Computer ConfigurationAdministrative TemplatesWindows ComponentsWindows Remote Management (WinRM)WinRM Service setting named Allow CredSSP authentication.
At this point, we are ready to utilize the functionality offered by CredSSP. Since this is a WinRM provider, we will need to implement the first of two options that deliver remote execution capabilities, which we have described in our previous article SQL Server 2012 Integration Services – Using PowerShell to Remotely Execute File System Packages– namely the one that relies on PowerShell Remoting. This will involve specifying credentials to be delegated to the remote computer (CredSSP does not support Windows integrated authentication to transparently leverage security context in which the Powershell session is running), initiating a new remote session, and invoking the command (which in our case will be launching DTExec.exe with the /File switch pointing to a package file residing on a remote file share). This translates into the following script:
$cred = Get-Credential $session = New-PSSession -ComputerName SERVER2.contoso.com -Credential $cred -Authentication CredSSP Invoke-Command -Session $session -ScriptBlock { "DTExec.exe /File \SERVER3.contoso.comDataSSISLab1Package.dtsx" }The first cmdlet will prompt you for the username and password to be delegated (if you intend to connect to a remote computer by using your current credentials, you can eliminate the need to type in your passsword by replacing the it with $cred = Get-Credential $env:USERNAME@$env:USERDNSDOMAIN). In the next step, you establish a remote PowerShell session to the computer SERVER2 (where you already have executed the Enable-WSManCredSSP -Role Client cmdlet), passing along the credentials stored in the $cred variable. Finally, you launch execution of the package stored on a remote file share (residing on SERVER3) in the context of the newly initiated session, by invoking DTExec.exe command line utilty with the /File switch.
This concludes our overview of the role of the CredSSP provider in implementing delegation when remotely executing SSIS packages in multi-server environments. Note that the approach we described is generic enough to be applicable in any other scenario that requires facilitating double-hop authentication when executing PowerShell based scripts.
