none
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account RRS feed

  • Question

  • I'm trying to run an SSIS project, but I can't get the ProxyAccount working.

    I'm getting the following error:

    Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.

    I believe this is the key.

    http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

    That error message is exactly the same as the one I'm getting!  However, when I go through the steps in that link, I get an error on Step #2. 

    Step #1 works fine.  I ran it EXACTLY as it appears in the link.  When I run Step #2, I get this.

    Msg 14529, Level 16, State 1, Procedure sp_add_proxy, Line 45
    Proxy "ARSHADALI-LAP\SSISProxyDemoUser" is not a valid Windows user.
    Msg 14262, Level 16, State 1, Procedure sp_verify_proxy_identifiers, Line 51
    The specified @proxy_name ('SSISProxyDemo') does not exist.

    The computer name is 'Excel-PC'.

    I played around with this a bit more tonight.  I really though I was getting close, but then I encountered another impasse. 

    Under Control Panel > User Accounts, I can see my user account name.

    QuestionIf I type 'Ryan' I get the error message below.
    You cannot vote on your own post

    I've added several users and none show in SQL Server (I can only see them in Control Panel > User Accounts).  I also got some error messages about not being able to create Local Users and Groups using MMC.  Maybe it's a Windows 8 issue. 

    There's definitely something wrong here.  In one view I see two users, in a second view I see no users, and in a third view, I see 4 users; every single user is different.  This will never work if there are no relationships between all these users. Any thoughts?  Anyone???


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Monday, October 28, 2013 3:36 PM
    Saturday, October 26, 2013 2:17 AM

All replies

  • http://social.msdn.microsoft.com/Forums/sqlserver/en-US/39621314-dec7-42aa-9e30-e62ea058947d/error-running-job-from-job-agent-non-sysadmin-have-been-denied?forum=sqlintegrationservices must be related

    I think for step 1 there was sufficient security in place to run it.


    Arthur My Blog

    Monday, October 28, 2013 3:12 PM
    Moderator
  • I concur.  What about the rest?  This must have something to do with security or permissioning.  I can't seem to figure it out.  :( 

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, October 28, 2013 3:37 PM
  • Yes, to run a package a proxy must be a member of the dtsoperator role

    Arthur My Blog

    Monday, October 28, 2013 3:53 PM
    Moderator
  • Hi ryguy72,

    Just an addition to Arthur's answer, we need to assign the SQL Server Agent Proxy account to the db_ssisoperator role on the MSDB in the Database Engine. For more information, please see:
    http://msdn.microsoft.com/en-us/library/ms141053(v=sql.110).aspx

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Wednesday, October 30, 2013 10:00 AM
    Moderator
  • Thanks for the info, Mike.  I understand the concept, I think, but I don't understand the exact steps to go through to get this working.  I've been at it for over two week, and it's still not working.  I'm just doing this for fun; it has nothing to do with my job.  I thought it would be a fun exercise to get SQL Server to run an SSIS script based on a timer, but I never got it working. 

    I created a batch file and fired off the process using Windows Task Manager.  That worked fine.  Getting SQL Server to do the same has been somewhat troublesome.

    I guess it has to do with the credentials on SQL Server, matching with what's in Windows.  I've read many articles on this, and tried all kinds of things, but I just can't seem to get the combination right.

    Can you list out the EXACT steps to go through to make this work?

    Thanks!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 30, 2013 3:48 PM
  • Hi ryguy72,

    Thank you for your posting.

    SQL Server Agent proxies use credentials to store information about Windows user accounts. Based on the current information, the issue occurs because you have not created a credential successfully for the SQL Server Agent proxy account. To create a credential, please connect to the Database Engine, expand the Security and Server Roles node, and right click on the Credentials folder to create a new credential against your Windows user Ryan. You should get it to work by following the steps in this good blog:
    http://www.bidn.com/blogs/DonnyJohns/ssas/1705/sql-server-agent-proxy-accounts

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 31, 2013 1:44 AM
    Moderator
  • Thanks for the help Mike.  I just tried all of the steps listed on that link.  I re-ran my job and under Job Properties - View Job History, I got this.

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

    11/05/2013 07:30:00,CallPackage,Error,,EXCEL-PC\EXCELDEVELOPER,CallPackage,,,The job failed.  The Job was invoked by Schedule 1012 (Export).  The last step to run was step 1 (Export).,00:00:02,0,0,,,,0

    11/05/2013 07:30:01,CallPackage,Error,1,EXCEL-PC\EXCELDEVELOPER,CallPackage,Export,,Executed as user: NT Service\SQLAgent$EXCELDEVELOPER. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  7:30:01 AM  Error: 2013-11-05 07:30:01.87     Code: 0xC0011007     Source: {B5E5DB93-E0D1-4E4F-BB43-5B71E7DACEFA}      Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.  End Error  Error: 2013-11-05 07:30:01.87     Code: 0xC0011002     Source: {B5E5DB93-E0D1-4E4F-BB43-5B71E7DACEFA}      Description: Failed to open package file "C:\Users\Ryan\Desktop\Package.dtsx" due to error 0x80070005 "Access is denied.".  This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format.  End Error  Could not load package "C:\Users\Ryan\Desktop\Package.dtsx" because of error 0xC0011002.  Description: Failed to open package file "C:\Users\Ryan\Desktop\Package.dtsx" due to error 0x80070005 "Access is denied.".  This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file name when calling LoadPackage or the specified XML file has an incorrect format.  Source: {B5E5DB93-E0D1-4E4F-BB43-5B71E7DACEFA}  Started:  7:30:01 AM  Finished: 7:30:01 AM  Elapsed:  0.469 seconds.  The package could not be found.  The step failed.,00:00:00,0,0,,,,0

    If I double-click the SSIS job, named 'Package.dtsx', it works just fine.  The file is located on my desktop; I should not need any special privileges, or rights, or whatever to run a job from here, right. 


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, November 5, 2013 12:53 PM
  • It almost looks like the Service account for the SQL Agent doesn't have permissions to read Package.dtsx

    But, the package is on my Desktop.  I thought everything on the Desktop is accessible to everything and from everything.  Should I place the Package.dtsx in a the same folder as where SQL Server sits?  Would that do it???


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, November 5, 2013 6:10 PM
  • I just looked at this link:

    http://dba.stackexchange.com/questions/29798/access-denied-when-trying-to-run-an-agent-job

    The guy says, '@KeithTate you were absolutely right. I was connecting to SQL as a login in the sysadmin group. I gave the rights to the service account to the actual package file and it worked great. Thanks!'

    What does that mean???


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, November 5, 2013 6:14 PM
  • I think it has to do with permissions on the file.  Here's a couple screen shots of my file (below).  It seems like it has full rights/permissions.  I still can't understand what the problem is here.  It seems so simple to do this, but it's not simple at all.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, November 6, 2013 12:15 AM
  • Hi ryguy72,

    Thank you for the posting.

    From your description, the job runs under a SQL Server Agent Proxy which is created based on the local Windows account Excel-PC\Ryan, and this account has full right on the desktop directory where the package is stored. Based on the current information, the issue should not be caused by the factor that the proxy account or SQL Server Agent Service Account doesn’t have sufficient permissions on the desktop. It may be related to the ProtectionLevel setting of the package. Please try to redeploy the package and set the ProtectionLevel of the package to “Do not save sensitive” or “Rely on server storage for encryption” and check the issue again.

    References:

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Wednesday, November 6, 2013 1:30 AM
    Moderator
  • Thanks for the follow up Mike.  That's a good idea.  Maybe the protection is on the file, not on the process.  Well, I just created a new project and saved it as Protection Level = 'Do not save sensitive'.  That didn't work.  I can't find any way to save it as 'Rely on server storage for encryption'.  I have VS 2010.  Maybe they didn't have that property or control back then.

    Although, I just came across this:

    http://www.sqlserverdb.com/2011/07/security-for-ssis-packages.html

    It seems like that option was around in 2005.  Maybe they got rid of it by 2010.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, November 6, 2013 3:55 AM
  • So, is there any way to troubleshoot this?  I've been working on this for a little over 4 weeks (seriously).  When I ran the batch solution it took about 2-3 minutes to setup and run.  This SQL Server solutions is just wayyyyyyyy to complex.  It shouldn't be, but it is.

    UGH!!!


    Any other ideas???


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, November 7, 2013 2:54 AM
  • What is the issue right now?

    Based on what I see you used "'Do not save sensitive'." protection option incorrectly.

    Under the 'Do not save sensitive'. you need then to provide the credentials for the package to connect to sources either via a config file (exposing a conn string) or at command line to DTEXEC.exe


    Arthur My Blog

    Thursday, November 7, 2013 3:16 PM
    Moderator
  • Ahhh!!  Well, there you go.  I saved the file as 'Do not save sensitive' but I didn't do the other step you described, or at least I don't think I did it.

    How do I do the second part?  Please post explicit instructions.  How do I 'provide the credentials for the package'?

    Thanks!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, November 7, 2013 3:18 PM
    1. Create configuraton file with connection string
    2. Manualy edit this configuration file: add the password to the connection string in case you connect with SQL Server authentication
    3. done!

    Arthur My Blog

    Thursday, November 7, 2013 3:25 PM
    Moderator
  • How do I create a configuration file?

    I have Visual Studio 2010 and SQL Server Developer 2012.

    This doesn't work for me:

    http://msdn.microsoft.com/en-us/library/cc895212.aspx

    This doesn't seem to be much better.

    http://sqlmag.com/visual-studio/exploring-visual-studio-2010s-database-tools


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, November 8, 2013 12:14 AM
  • Ok, I think I see how this works.  I use Windows Authentication, so I don't login to SQL Server using a password.  Anyway, I clicked Solution Explorer > right-clicked Integration Services Project > then clicked Convert to Package Deployment Model.  I saved the project; 'Package.dtsx'.  I put this on nmy desktop, and set up another SQL Server Job and then pointed the Schedule to this projects and it fired off a few minutes later.

    I'm still getting exactly the same results as before.  Maybe I need to set a password and login using SQL Server Authentication, rather than windows Authentication (and no password).


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, November 8, 2013 2:34 AM
  • Nope, that didn't work either.  I added security; I logged in under SQL Server Authentication and entered my password and ran the job on a Schedule -- nothing.  This is AFTER I set everything up under SSIS and did a Convert Package to Deployment Model and Project Level = 'Don'tSaveSensitive'.  Still, nothing works.

    I don't get it.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Friday, November 8, 2013 5:07 PM
    Friday, November 8, 2013 3:04 AM