none
server principal...is not able to access the database...under the current security context RRS feed

  • Question

  • Hi we run std 2017.  I'm switching a non expiring userid away from executing scheduled powershell->sp_start_job->ssis over to scheduled powershell->proc->ssis.

    under my creds no problem running the ps script from the server where the ps script resides.  Under the non expiring userid (run from further upstream but executing this same ps script on the same ps server) there is a problem so I assumed if I recreate a similar proc with execute as 'domain\user' I can recreate the problem on ssms (if its a sql problem).  i'm trying to impersonate the non expiring userid and working my way backward.  i'm executing the proc on ssms.

    The userid is already added to the two db's (TEST and SSISDB) mapped to the login and grant execute on all 4 procs in the respective db's to the user was run.  I assumed chaining would stop the userid when the db and schema changed from TEST.dbo to SSISDB.catalog.

    I get the error on ssms shown below.  I'm not even sure if this is a valid way to impersonate.  I have admin rights on the sql server.  I have no idea what this error means.  But would have thought with a user mapped to the login, the user would have access to the db TEST.

    The server principal "domain\user" is not able to access the database "TEST" under the current security context.

    USE [TEST]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[runssispackage2]
    	-- Add the parameters for the stored procedure here
    	@folder varchar(1000),
    	@project varchar(1000),
    	@package varchar(1000)--,
    	with execute as 'domain\user'
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    	declare @execution_id bigint
        exec ssisdb.catalog.create_execution 
    	     --there are other params but these dont have a default
             @folder 
            ,@project 
            ,@package 
            ,@execution_id = @execution_id output
         EXEC [SSISDB].[catalog].[set_execution_parameter_value]  
              @execution_id 
             ,@object_type = 50						-- System parameter , 50 is used for various types of calls
             ,@parameter_name = N'SYNCHRONIZED' 
             ,@parameter_value = 1 
         exec ssisdb.catalog.start_execution @execution_id
         IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id) 
          RAISERROR('The package failed. Check the SSIS catalog logs for more information', 16, 1) 
    
    
    END
    GO

      

    Wednesday, November 20, 2019 5:56 PM

Answers

  • There two things here:

    1. Running the stored procedure itself.
    2. Running the packages.

    For running the procedure there are a couple of options. The simplest is undoubtedly to add the service account for the job scheduler to ssis_admin, but that may not be feasible from a security perspective. For the latter, the best is probably a combination of impersonation and procedure signing. I will not go into details here, but refer you to my article Packaging Permissions in Stored Procedures, which is quite long. I don't discuss SSISDB anywhere, but the appendix includes a chapter of how to let users start a specific job in msdb. This scenario appears to be analoguous to the SSISDB case to me.

    No matter how do this, you will still have the problem of running the packages. Any impersonation you perform inside this SQL Server instance is entirely invalid outside of it. So either whatever user that runs these packages and connects to the other instances must have a login on these servers, or the packages must have login+password in their connection strings.

    You may want to consult the SSIS forum for better tips on how to handle the SSIS packages.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, November 22, 2019 10:19 PM

All replies

  • I think i'm halfway there. the userid (when scripted as a create) in test had a default schema of the domain\userid itself instead of dbo.  I deleted the schema, proc, then the user, then recreated it with default schema dbo and my new error is the same except for ssisdb.  I think in ssisdb i'll have to somehow revoke execute instead of deleting procs but i'll see what I can do. 
    • Edited by db042190 Wednesday, November 20, 2019 6:38 PM more
    Wednesday, November 20, 2019 6:29 PM
  • I cant shake the same error related to ssisdb. I got the user created with default schema dbo and gave back exec on the 3 catalog procs.  I got the error again so I thought maybe I should try making the default schema in ssisdb catalog.  but sql stopped me implying there were some grants or denies outstanding.  at this point I've granted exec on the 3 procs, granted select , delete etc on schema catalog and even granted exec on schema catalog.  But I am getting this error ...

    Msg 916, Level 14, State 1, Procedure dbo.runssispackage2, Line 17 [Batch Start Line 4]

    The server principal "domain\user" is not able to access the database "SSISDB" under the current security context.

     
    Wednesday, November 20, 2019 7:24 PM
  • I granted select etc and exec on schema internal after seeing a lot of references to that schema in the catalog procs. still getting the same error.  I see a lot off as caller in the catalog procs as well.  i'm tempted now to recreate the original proc to execute as myself, keep exec permissions on just the top proc for the user and see what happens. 
    Wednesday, November 20, 2019 7:35 PM
  • You may have a permission issue. Take a look at the part of Permissions of SSIS Catalog.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, November 20, 2019 8:13 PM
  • When you say EXECUTE AS in a stored procedure, you are impersonating a database user. When you impersonate a database user, you are sandboxed into the current database and cannot access things outside the database. This is because the security context is not trusted outside of it.

    This is a general principle with impersonation: when you impersonate a principal in one environment, this impersonation must not be trusted elsewhere, since else this could permit for security elevation. That is, a user who have permission to create and impersonate users in a database, should not be able to create a user for a sysadmin login and take over the server.

    If I understand it correctly, you have a Powershell job. I think that in that case, the best is to specify a login to execute the job step as. For this, you need to define a proxy under the SQL Agent node in SSMS.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, November 20, 2019 10:34 PM
  • Hi db042190,

    Here are some similar threads might be helpful to you:

    The server principal “loginname” is not able to access the database “SSISDB” under the current security context

    SSIS catalog access control tips

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 21, 2019 9:32 AM
  • WOW! Thx Erland. I'm anxious to get to Guoxiong's and Amelia's links but my initial reactions to what you've said are as follows:

    1) i'm purposely moving away from sql agent because i'm forced into an asynchronous mode when starting jobs from scheduling software that is external to sql.  We need synchronous behavior in order for scheduling predecessor/successor features to work.  Yes, the scheduling software (might be unix based) is plumbed to a windows server (scheduling software runs an agent there) where the powershell scripts reside.

    2) I was trying my own proc instead of sql agent so that I can achieve synchronous behavior when caling "ssis stuff".  Now it sounds like there would be two choices in that regard.  Keep in mind that I see at least 2 schemas (and what appear to be dmv's or sys. tables) involved in running the catalog stuff.  One would be to keep my proc in TEST and give the user every possible permission I can find by chasing thru the calls etc that these catalog items do.  Or put my proc on SSISDB (I wonder if that is ill advised), perhaps put a more powerful "execute as" in it, give my user permission to execute my proc, cross my fingers and go.  My concern there is what if I want my raiserror msgs logged somewhere that isn't on the ssis db?  I'll have to reread the rules of engagement but perhaps the execute as, if it has admin can log those msgs to the sql log itself.

    3) there is an option I haven't tried where after a special install presented by Olaf, I can actually run what I think is called sql server powershell provider on those windows servers where the scripts reside.  to me those look synchronous.  they are referenced at https://social.technet.microsoft.com/Forums/en-US/773a4747-de92-4eea-bec7-e9ff565c1a79/can-i-run-the-ssis-powershell-provider-from-outside-of-sql?forum=sqlintegrationservices .   

    4) I didn't totally understand what you said about the proxy but it sounds like if sql agent is out of the picture, then that approach is off the table. 

     
    • Edited by db042190 Thursday, November 21, 2019 1:25 PM additional concern
    Thursday, November 21, 2019 12:53 PM
  • thx Guoxiong. The error referenced in your link (see below) sounds identical but its impossible that my user granted or revoked anything.   I'm concerned that this user is stuck there due to some sort of bug in sql.

    If the principal has granted or denied permissions to other principals, revoke the permissions given by the grantor, before the principal can be removed. Otherwise, an error message is returned when the system tries to remove the principal. The trigger removes all permission records where the database principal is a grantee.

    It is recommended that the trigger is not disabled because it ensures that are no orphaned permission records after a database principal is dropped from the SSISDB database.

     
    Thursday, November 21, 2019 1:00 PM
  • thx Amelia. Moving my proc to ssisdb is all I can think of too. And then perhaps some sort of combo between execute as and grant exec to the user on the proc. I am concerned about using what sounds like an ms db (ssisdb) for development use. I don't know how that would play out on an upgrade and whether the typical dba would stop and think about the possibility that it contains custom stuff.
    Thursday, November 21, 2019 1:11 PM
  • well, this is nuts, kind of going in circles.  I created the proc in ssisdb first with an "execute as" being myself (windows domain\iserid).  Then went to execute it on ssms and basically got an error saying that running as an entity that also has sql login capabilities is a no no.  and I think it hinted that I should use an integrated userid which honestly I know nothing about at the moment.

    so I changed the "execute as" to my user, ran and got an error saying that either the pkg doesn't exist or the user doesn't have permissions to execute it. 

    so I gave the user ssis_admin (per one of the links above) and now i'm back to the sql login error. 

    HELP!

    Along the way I did remove the execute as and just executed the proc from ssms.  There was no problem running that way.  But all that verified was that my params and everything non security related is in order.  I still need to run one of the 2 ways shown above.

     
    • Edited by db042190 Thursday, November 21, 2019 1:58 PM clarity
    Thursday, November 21, 2019 1:54 PM
  • So what you really want is to run this job from your third-party scheduling software? OK, then focus on that and don't dabble with Agent or adding EXECUTE AS to the procedure. Both of these comes with problems what you may not get with your third-party software.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, November 21, 2019 3:42 PM
  • thx Erland. Agent isn't in the picture anymore.  But a proc is.  And all that really matters that is related to the 3rd party is the userid being used there. 

    I think what u r saying is remove the "execute as" from the proc, keep ssis_admin on userid, keep proc in ssisdb, grant execute on proc to userid and  (this hurts the most)  give the userid permission on all the reads and writes it will need to do on about 22 servers involved in some of the pkgs. 

    I guess the part that hurts the most could hurt less if the userid is part of a group with all those permissions.   and I guess the ssis_admin and grant execute could be on the group instead of the specific userid. 

    Pls let me know cuz I hate to ask my dba's for this unless it has a good chance of working.

    Thursday, November 21, 2019 5:04 PM
  • OK, one problem is that there are so many posts in the thread that it is difficult to understand what you want to do. But if I get this right: you have a stored procedure in database A that needs to call procedures in SSISDB? And this procedure is called from your third-party scheduler?

    The simplest option appears to grant the account the scheduler runs under (or impersonates if it is capable of that) CONNECT permissions in SSISDB and EXEC permissions on the stored procedures.

    I will have to admit that I know very little about SSIS and SSISDB, so I don't what the security model is inside SSISDB, if there is anything special at all.

    There may be alternatives, but before I send you there, I would like to get a better understanding of what  you are trying to achieve.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, November 21, 2019 10:59 PM
  • you seem to have stated it clearly.  I have to summarize the plumbing in order to explain to you what i'm doing.

    I.    the 3rd party scheduler uses a windows server where one of its uniquely named agents was installed specifically for my app (see more below).

    II.   On that server are also the powershell.exe (ps) with some extra sql cmdlets installed

    III.  On that server are also some ps scripts that execute my proc (dbo.runssispackage whose params include a package name) on my sql server using the impersonate userid you mentioned whose creds are passed from the scheduler presumably via kerberos.

    IV.   the scheduler is basically made up of defined apps which are made up of defined jobs.  each job has associated with it a) what to execute (in my app it is powershell.exe including the path to powershell) , b) what param(s) to pass to the thing being executed (that would be the ps script command file paths and names).  the windows server is already known to the scheduler via the agent name which is specified as an attribute of the app/job.

    V.    My proc calls a sequence of ssisdb procs in order to execute the package name that was passed.  One of the important calls it makes in that sequence is to set a flag on the "ssis command being built" instructing things to run synchronously. 

    My understanding is that 1) whomever executes those ssis procs needs minimally membership in ssis_admin for the procs to act on their target ssis objects and maybe that membership also grants execute on the procs as well 2) membership in ssis_admin is only shown/selectable on ssisdb (not other dbs) under security/users...membership 3) anyone with sysadmin doesn't need that membership assigned because sysadmin includes that membership.

    As you can imagine, the package itself touches a number of other sql servers for read and write purposes as data is moved around and summarized.  In an effort not to bug our dbas with the read/write permissions on the other sql servers till I knew for sure this was all going to work, I went looking for something associated with procs that would act like a proxy having my own credentials.  I started playing with the various flavors of "execute as".  That's how my proc ended up in ssisdb but I suspect it doesn't even need to be there now that I look back. 






    • Edited by db042190 Friday, November 22, 2019 2:13 PM clarity
    Friday, November 22, 2019 1:47 PM
  • There two things here:

    1. Running the stored procedure itself.
    2. Running the packages.

    For running the procedure there are a couple of options. The simplest is undoubtedly to add the service account for the job scheduler to ssis_admin, but that may not be feasible from a security perspective. For the latter, the best is probably a combination of impersonation and procedure signing. I will not go into details here, but refer you to my article Packaging Permissions in Stored Procedures, which is quite long. I don't discuss SSISDB anywhere, but the appendix includes a chapter of how to let users start a specific job in msdb. This scenario appears to be analoguous to the SSISDB case to me.

    No matter how do this, you will still have the problem of running the packages. Any impersonation you perform inside this SQL Server instance is entirely invalid outside of it. So either whatever user that runs these packages and connects to the other instances must have a login on these servers, or the packages must have login+password in their connection strings.

    You may want to consult the SSIS forum for better tips on how to handle the SSIS packages.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, November 22, 2019 10:19 PM
  • thx Erland. I'll mark your post as an answer but to me sql is inconsistent. 

    I can start jobs and achieve what amounts to impersonation by giving exec on sp_start_job to the service acct running pkgs with no problem. But I cant when I run procs.  At least not without logins being added in the 2nd scenario.


    • Edited by db042190 Monday, November 25, 2019 12:48 PM grammar
    Monday, November 25, 2019 12:47 PM
  • I think the model that an impersonation is never trusted outside the context where the impersonation is made is quite consistent - not only in SQL Server, but also in Windows.

    But I am not a big fan of the EXECUTE AS clause in stored procedures. It's simple to do as long as you are inside the database, but it can break auditing and row-level security.

    As for Agent, that is a very old design, and I don't think it is the best one. But since so many people use it, I guess it is difficult for Microsoft to make changes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, November 25, 2019 1:54 PM