2

I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user.

I found this article which detailed creating a certificate and signing the module so that a privileged login could execute it.

When I try to run any command impersonating the login, I receive the following error:

Cannot execute as the server principal because the principal "CrossDbCertLogin" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Here's a script to reproduce the problem:

create certificate CrossDatabaseCert
ENCRYPTION BY PASSWORD = 'passA'
WITH SUBJECT = 'Certificate for Cross-Database Database Impersonation'
GO


CREATE Login CrossDbCertLogin FROM CERTIFICATE CrossDatabaseCert
GO

backup certificate CrossDatabaseCErt to file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(
    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passA',
    ENCRYPTION BY PASSWORD = 'passB'
)
go

use msdb
create certificate CrossDatabaseCert from file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passB',
    ENCRYPTION BY PASSWORD = 'passA'
)
Go

EXECUTE AS LOGIN=N'CrossDbCertLogin';
SELECT 1

Eventually I add the certificate to the stored procedure, but it seems the impersonation isn't even functioning so I'm trying to get that working first.

What am I doing wrong?

EDIT: A little more info on my procedure.

When I change it to "EXECUTE AS OWNER" (in the same form as the example I followed), I receive the following errors.

Msg 27123, Level 16, State 1, Procedure create_execution, Line 39 The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Msg 27123, Level 16, State 1, Procedure set_execution_parameter_value, Line 34 The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Msg 27123, Level 16, State 1, Procedure start_execution, Line 32 The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Inside my procedure I call three internal SSIS Catalog Procedures: Create_Execution, Set_Execution_Parameter_Value, and Start_Execution.

When I change the procedure to execute as the SQL Agent Service account, I get the following error:

Msg 15199, Level 16, State 1, Procedure prepare_execution, Line 34 The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

EDIT2: I ended up using a SQLCLR SProc to call my original proc, which solved all the issues.

  • Are you starting the SSIS package from a SQL Agent job defined in the msdb database? – RLF Oct 17 '14 at 18:03
  • @RLF No, my procedure lives in the msdb database. I'm calling the internal SSIS Catalog procedures to run the package. – Mansfield Oct 17 '14 at 18:11
2
+50

Two thoughts, both revolving around Impersonation:

  1. From the description of the issue as well as the error messages, they all deal with errors related to attempts to Impersonate / EXECUTE AS. This attempt to switch the execution context could be the entire problem. Why are you using EXECUTE AS LOGIN = N'CrossDbCertLogin'? That login (and the associated certificate, etc.) exists as a proxy to get implied permissions, not to execute anything directly. If you look more closely at the example you followed from sqlxdetails.com, you will notice that there is never a call to EXECUTE AS LOGIN = N'HighPrivCertLogin'.

    Hence, I would start by:

    1. No more attempts to EXECUTE AS LOGIN = N'CrossDbCertLogin'
    2. Your stored procedure that calls the three SSIS Catalog Procedures should either have no EXECUTE AS clause or EXECUTE AS CALLER (which is the default if not specified)
    3. Grant execute permission on each SSIS stored procedure to the certificate-based user in the [SSISDB] database. These are the implied permissions that your restricted login will pick up from the link between the proc being signed with the certificate that maps to a user that does have the necessary permissions:

      USE [SSISDB];
      GRANT EXECUTE ON [Create_Execution] TO [CrossDbCertUser];
      GRANT EXECUTE ON [Set_Execution_Parameter_Value] TO [CrossDbCertUser];
      GRANT EXECUTE ON [Start_Execution] TO [CrossDbCertUser];
      

      Of course, your example code doesn't show the creation of a local database user in either [msdb] or [SSISDB], but I assume these users were created. If not, then at the very least you need to create the user in [SSISDB] since that is where the database-level permission is needed (i.e. to execute the 3 SSIS procs). But it probably couldn't hurt to also create the certificate-based user in [msdb] as well.

  2. If, for some reason (maybe SSIS, being an external process, cannot use an impersonated authentication token?) the execution context itself needs to be both privileged and impersonatable (yes, that is a perfectly cromulent word ;-), then the current approach won't work as you are trying to:

    • impersonate logins that cannot have an execution context (i.e. certificate- and asymmetric key- based logins)
    • impersonate via the EXECUTE AS clause of the CREATE PROCEDURE clause which cannot be reverted (I don't know why anyone would want to revert from that, but it is an error message you are getting when attempting this).

    So, here are two things to try (again, assuming option # 1 above doesn't do the trick):

    1. Create a SQLCLR stored procedure that does nothing more than connect via the connection string of "trusted_connection = true;" (which will connect to the default instance, presumably the instance you are currently on) and execute your stored procedure in [msdb] that runs the three SSIS procedures. This would work because unless you explicitly code for using Impersonation, it will by default make the external connection as the Windows/Active Directory account that is running the SQL Server process (i.e. the "Log On As" account in Services) and that account should be able to EXEC those SSIS procedures. This option wouldn't require any certificates or logins because you would GRANT EXECUTE on this SQLCLR stored procedure to that restricted login and this stored procedure makes a new, independent connection as a privileged login. This SQLCLR procedure has a single purpose such that logging in as the privileged login doesn't pose a security threat. The other key piece is: due to the connection being made by non-impersonated context, the authentication token can be passed to an external machine. But yes, the Assembly will need a PERMISSION_SET of EXTERNAL_ACCESS, and that should be accomplished by signing the Assembly and then creating an asymmetric key from the assembly and then creating a login based on that asymmetric key and then granting the EXTERNAL_ACCESS ASSEMBLY permission to that login.

      If assistance is needed with the database side of the Assembly / Asymmetric Key steps, I wrote an article, Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (free registration required), that contains a step-by-step example of doing this; it just doesn't show the creation of the private key in Visual Studio.

      or

    2. Create a queue table that your restricted login has permissions to INSERT into. Then create a SQL Agent Job that checks the queue table and if a "New" record is found, updates the status to "In Process", calls the stored procedure in [msdb] that calls the three SSIS procedures, and then finished, updates the queue record again to "Completed". In this method SQL Agent is running the stored procedure via a new connection from a non-impersonated context; hence, the authentication token can be passed to an external machine.

  • I tried what you recommended (removing the execute as), yet the impersonation doesn't do anything (I get errors related to the permissions on my restricted account). I'm going to try the SQLCLR option now. – Mansfield Oct 24 '14 at 12:46
  • @Mansfield : I will update my answer to reflect this (forgot to mention it last night as I was tired and heading to bed), but you need to do the final step of giving the intended permission(s) to the cert USER in the SSISDB database. Sorry for leaving that out. – Solomon Rutzky Oct 24 '14 at 13:33
  • @Mansfield : Answer has been updated. Please see new item # 3 in Option # 1. Even if you end up going with the SQLCLR method, you have already done the majority of the certificate-based method steps so would be good to know for future reference if that was the final missing piece :-) . – Solomon Rutzky Oct 24 '14 at 13:46
  • Thanks for all your help. I granted the appropriate permissions, but I'm still getting the following error:Msg 916, Level 14, State 1, Procedure get_user_info, Line 2 The server principal "RestrictedAccount" is not able to access the database "SSISDB" under the current security context. – Mansfield Oct 24 '14 at 14:06
  • @Mansfield : I wonder if you can GRANT that permissions to the certificate-based Login (i.e. CONNECT). – Solomon Rutzky Oct 24 '14 at 14:17
3

In Vedran's code which you reference, he also signs the stored procedure in the other database. I do not see in your code that you have taken that final step.

Erland Sommarskog has an extensive discussion at http://www.sommarskog.se/grantperm.html of this problem. This includes a discussion of, in his words, "the Problematic EXECUTE AS".

In the following link, which is for starting SQL Agent jobs, you can see yet another approach to delegating rights to a login which has no particular rights. See: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

If that appeals to you, you can create your own stored procedure to allow your user to start the SSIS process.

10/20/2014 Edit: "Cannot execute as the server principal because ..."

First of all, if you are a sysadmin, then the problem is not your rights, but the context you are trying to run. (I believe that you are a sysadmin.) So it could be something like:

  1. If the server principle you want to use is a Windows Group it cannot be impersonated.
  2. Have you recently restored a system database (e.g. msdb) from another server? Then the account you see in msdb may not match an account on the current server. (You might see the same name in database_principals, but the sid could be different.) If so, try using exec sp_change_users_login 'AutoFix','principalname'.
  3. If the owner of a database (in master.sys.databases)is not the same sid as the database's dbo user, then it cannot be impersonated. (This is unlikely in msdb since sa is the normal owner of msdb.)
  4. If the login was dropped, even if the user remains in the database, you may not be able to impersonate the account.

And there are likely other flavors as well.

  • I have signed the proc in MSDB as well.I receive the same error message when I call the procedure. – Mansfield Oct 17 '14 at 18:26
  • @Mansfield - Offered some updates on impersonation problems. – RLF Oct 20 '14 at 20:47
  • Thanks for adding the new info. I've verified none of the things are the issue (Except for #1 I am trying to impersonate a service account - not sure if that still applies). I've added more detail to the question. – Mansfield Oct 21 '14 at 18:54
  • @Mansfield - Your edit indicates that the calls to create_execution etc are being run from a SQL Server login instead of a Windows Login. Check on the owner of the database (per point 3) to ensure that it is a Windows account. Regarding the REVERT: whatever database you were in when you chose your context, you need to go back to that database to return REVERT successfully. (In the sample code which I linked above, the EXECUTE AS and REVERT are of course in the same database, since the code is in a stored procedure.) – RLF Oct 21 '14 at 23:10
  • I've checked the owner (it's SA). I'm not sure I understand the revert message: the proc is in MSDB, and the user executing connects to the MSDB databases. I'm not explicitly calling REVERT anywhere. Is the problem with calling the procedures in the SSISDB databases from inside the proc? – Mansfield Oct 22 '14 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.