1

We have a SSIS package which accesses database columns which are encrypted using Always Encrypted.

This does not work when triggering the SSIS package through a SQL job using a proxy user.

Failed to decrypt a column encryption key using key store provider 'mssql_certificate_store'

We have tried logging in to the server as the domain user and triggering the SSIS package manually and we don't receive this error. So it seems that there is some issue accessing the certs when a proxy user is activating the ssis package.

Code for the setup of the proxy user:

CREATE CREDENTIAL [SSIS Credential] 
    WITH IDENTITY = N'DOMAIN\service_ssis_user', SECRET = N'DomainPassword'

IF NOT EXISTS (SELECT name FROM sysproxies WHERE name = 'SSIS Package')
BEGIN
    EXEC msdb.dbo.sp_add_proxy 
              @proxy_name = N'SSIS Package',
              @credential_name = N'SSIS Credential', @enabled = 1

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
              @proxy_name = N'SSIS Package', @subsystem_id = 11

    EXEC msdb.dbo.sp_grant_login_to_proxy 
              @proxy_name = N'SSIS Package', 
              @login_name = N'DOMAIN\service_ssis_user'
END
GO

The aim is to get the SSIS package running as the domain user and able to access the certificates associated with this user

Update:

The proxy user does not "login" as the user that I have created credentials for, it simply uses the security context of the user to run the command. So it does not load their windows user profile which would happen when logging in directly as the Domain User. And therefor the certs are not accessible when running via proxy. I dont know how to get around this issue however.

frogstrosity
  • 31
  • 7
  • I'd hazard a guess that if you authenticate to the SQL Server as the SSIS Proxy account and try to run the package the same error occurs? When you execute a package 9which I assume is in the SSISDB) via SQL the package runs using the credentials of whomever started the package, not the SQL Server service account or agent. Does the proxy account have access to the encryption key? Should it? – Thom A Jul 02 '19 at 11:04
  • If I understand correctly I have the proxy user set to run as the domain user account, if we authenticate as the domain user it works and we dont get the error. I'm not sure how we would log on as the actual proxy. It is only when the credential for the domain user is used through the proxy that we get the error. I dont know how to verify if the proxy has access to the keys but, again, I understood that the package would be executed as the domain user using the credentials that i created, this domain user does have access to the keys. – frogstrosity Jul 02 '19 at 11:37
  • 1
    How are you executing the package then? Can you include the SQL in your post? From my recollection, `EXECUTE AS` does not work when executing packages from SSISDB; you can't use impersonation. If you want to test if it works as the proxy account you can start SSMS using that users credentials. Hold Shift and Right click the shortcut for SSMS (this functionality is disabled by default in the Start Menu for Windows 10, so use a Desktop shortcut) and selet `Run as different user". Then log in as your proxy account and see if the package works. I suspect, after your latest comment, it will. – Thom A Jul 02 '19 at 11:44
  • It is executed via a sql job with the step's "Run As" option set to the sql credential that I setup (SSIS Credential). Have been running by scheduling that job and also by running that job manually. Unfortunately I'm unable to run SSMS as the user as it would require elevated permissions – frogstrosity Jul 02 '19 at 12:56
  • I think my misunderstanding is: A proxy does not "login" as that user, it simply uses the security context of the user to run the command. So it does not load their profile. And therefor the certs are not loaded Essentially it is different to EXECUTE AS which you mentioned I cannot do. – frogstrosity Jul 02 '19 at 13:43

1 Answers1

1

Windows user profile is not Loaded when a proxy account is used. As a result the certificates associated with the user are not accessible when running via proxy.

frogstrosity
  • 31
  • 7