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 32, 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.

    I don't understand what the error is, so I can't get past this step.  Any ideas how I can move forward on this anyone?  I've been working on this darn problem for a couple weeks now!!

    Does anyone have any idea how I can get this working?

    Thanks!!!


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

    Friday, October 18, 2013 3:17 AM

Answers

  • Hi,

    First you have to create a credential. Credential is a windows user that will be used by the proxy account to execute the package.

    create credential [dsavovic]
    	with identity = 'comminus\deansavovic';

    Now you can create a proxy that will be linked to the credential.

    declare @proxy_id int;
    
    exec msdb.dbo.sp_add_proxy
    		@proxy_name = 'dsavovic_proxy',
    		@enabled = 1,
    		@description = 'test dsavovic proxy' ,
    		@credential_name = 'dsavovic' ,
    		@credential_id = null ,
    		@proxy_id = @proxy_id OUTPUT
    
    select @proxy_id as proxy_id

    Now you have a proxy named "dsavovic_proxy" that is using credential "dsavovic" (i.e. proxy is using windows user account "comminus\deansavovic").

    Now you have to assign the proxy to a SQL Agent subsystem (i.e.job step type). As you are using SQL Agent to execute SSIS package we will use SSIS subsystem (name of the subsystem is "Dts").

    exec msdb.dbo.sp_grant_proxy_to_subsystem
    		@proxy_name=N'dsavovic_proxy'
    		, @subsystem_name = 'Dts'

    If the user executing the package through SQL Agent job step is not sysadmin you have to assign permissions to that user login to be able to use the proxy account.

    exec msdb.dbo.sp_grant_login_to_proxy 
    		@login_name = 'dsavovic_user' 
    		, @fixed_server_role = null
    		, @msdb_role = null
    		, @proxy_id = null
    		, @proxy_name = 'dsavovic_proxy'

    Now you are ready to execute SSIS type steps under SQL Agent. SSIS steps will be executed under "dsavovic_proxy" proxy using "dsavovic" credential which uses "comminus\dsavovicˇwindows user account.

    All this steps can be done using SQL Server Management Studio GUI.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Friday, October 18, 2013 7:37 AM
  • Hi,

    I hope you didn't execute the code I provided with the values in the example. Those values are domain names and user names from the domain I work on. The domain my SQL Server is member of is "comminus" and the user name I created a proxy with id "deansavovic" (this is my user name at work).

    First you have to determine is you SQL server member of domain or not, and do you want to use domain user name for the proxy or local user name (created on SQL Server machine). And also, you need to see under which user name the SQL Server Agent is running since this is the user name (i.e. login name) that will use the proxy.

    Let's take the following input parameters for the example:

    1. SQL Server is member of the domain and you will be using user name "YourDomain\YourUserName" for proxy
    2. SQL Agent is running under user name "YourDomain\YourSQLAgent" user name and is not member of sysadmin role

    Now execute this to create credential, proxy and assign user rights to SQL Agent to use the proxy.

    create credential [YourCredential]
    	with identity = 'YourDomain\YourUserName';
    declare @proxy_id int;
    
    exec msdb.dbo.sp_add_proxy
    		@proxy_name = 'YourProxy',
    		@enabled = 1,
    		@description = 'your test proxy' ,
    		@credential_name = 'YourCredential' ,
    		@credential_id = null ,
    		@proxy_id = @proxy_id OUTPUT
    
    select @proxy_id as proxy_id
    exec msdb.dbo.sp_grant_proxy_to_subsystem
    		@proxy_name=N'YourProxy'
    		, @subsystem_name = 'Dts'
    exec msdb.dbo.sp_grant_login_to_proxy 
    		@login_name = 'YourDomain\YourSQLAgent' 
    		, @fixed_server_role = null
    		, @msdb_role = null
    		, @proxy_id = null
    		, @proxy_name = 'YourProxy'
    If your SQL Server is not member of domain then change the YourDomain with YourServerName and for YourUserName and YourSQLAgent use appropriate user names from your server (SQL Server).




    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Saturday, October 19, 2013 1:34 PM
  • Hi

    The first step (create credential) is what is wrong in your case. SQL Server will let you create credential with invalid user name but when you try to create a proxy with that credential you will get an error you are getting.

    Your valid windows user name can come from two places:

    1. Active Directory domain user name if your SQL server is a member of domain
    2. Local user if your server is not a member of domain

    Here is how you will get a valid user name:

    1. SQL server is member of domain - contact your system/domain administrator to create a user name you will use for executing your SSIS job steps
    2. SQL Server is not member of domain - open Computer Management (Control Panel -> Administrative Tools -> Computer Management), click Local Users and Groups and click Users. Here you either pick one valid user name or create one.

    When creating credential you reference you user name:

    1. DomainName\username - SQL Server is memeber of domain
    2. MachineName\username - SQL Server is not member of domain

    Make note that you can use point 2 for valid windows user name even if you SQL Server is a member of domain, but then your SSIS job step will not be able to access any network resources.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Saturday, October 19, 2013 4:19 PM
  • Hi,

    The error you are getting means that ExcelDeveloper is not a user on machine Excel-PC.

    Is your SQL Server running on machine named Excel-PC?

    Do you have a user named ExcelDeveloper on your machine Excel-PC? You can check this under Local Users and Groups in Computer Management console.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Sunday, October 20, 2013 2:42 PM
  • Keep your head up, I think you are close to solution of your problem.

    Now you are trying to add credential that does not exist to the proxy. First you have to create the credential for identity Ryan and then create the proxy that references this credential.

    List of users on you machine can be seen in Computer Management. Open run command window (windows button + R) and type this: "compmgmt.msc /s". Without quotation marks.

    This will open Computer Management. Then go to Local users and Groups and then to Users. Here you can see a list of users on you machined and you can add a new one if you want to.

    Pick one user and create credential for it and after that a proxy. How to do this can be found in previous posts. You can use the SSMS gui also.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Monday, October 21, 2013 2:19 PM

All replies

  • Could someone explain the SQL code, or tell me the steps that I need to perform to make this work?  Step by step; the more details the better!!

    I've been working on this for almost 2 weeks.  I must be missing something really simple.  It can't be this difficult.

    Thanks everyone.


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

    Friday, October 18, 2013 3:22 AM
  • Hi,

    First you have to create a credential. Credential is a windows user that will be used by the proxy account to execute the package.

    create credential [dsavovic]
    	with identity = 'comminus\deansavovic';

    Now you can create a proxy that will be linked to the credential.

    declare @proxy_id int;
    
    exec msdb.dbo.sp_add_proxy
    		@proxy_name = 'dsavovic_proxy',
    		@enabled = 1,
    		@description = 'test dsavovic proxy' ,
    		@credential_name = 'dsavovic' ,
    		@credential_id = null ,
    		@proxy_id = @proxy_id OUTPUT
    
    select @proxy_id as proxy_id

    Now you have a proxy named "dsavovic_proxy" that is using credential "dsavovic" (i.e. proxy is using windows user account "comminus\deansavovic").

    Now you have to assign the proxy to a SQL Agent subsystem (i.e.job step type). As you are using SQL Agent to execute SSIS package we will use SSIS subsystem (name of the subsystem is "Dts").

    exec msdb.dbo.sp_grant_proxy_to_subsystem
    		@proxy_name=N'dsavovic_proxy'
    		, @subsystem_name = 'Dts'

    If the user executing the package through SQL Agent job step is not sysadmin you have to assign permissions to that user login to be able to use the proxy account.

    exec msdb.dbo.sp_grant_login_to_proxy 
    		@login_name = 'dsavovic_user' 
    		, @fixed_server_role = null
    		, @msdb_role = null
    		, @proxy_id = null
    		, @proxy_name = 'dsavovic_proxy'

    Now you are ready to execute SSIS type steps under SQL Agent. SSIS steps will be executed under "dsavovic_proxy" proxy using "dsavovic" credential which uses "comminus\dsavovicˇwindows user account.

    All this steps can be done using SQL Server Management Studio GUI.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Friday, October 18, 2013 7:37 AM
  • Thanks so much for trying to help me.  The first part of the code worked fine.

    create credential [dsavovic]
    	with identity = 'comminus\deansavovic';

    The second part of the code, which is below, caused an error.

    exec msdb.dbo.sp_grant_login_to_p.ovic_user' , @fixed_server_role = null , @msdb_role = null , @proxy_id = null , @proxy_name = 'dsavovic_proxy'

    Msg 14529, Level 16, State 1, Procedure sp_add_proxy, Line 45
    Proxy "comminus\deansavovic" is not a valid Windows user.

    (1 row(s) affected)

    What the HECK does that mean??  I don't know what Windows security is.  That's the whole problem here. 

    Personally, I don't really care about security.  If someone wants to read my emails, I say, 'go for it'.  I don't know anything about security.  Security seems to be a major requirement for this project.


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


    • Edited by ryguy72 Saturday, October 19, 2013 2:25 PM
    Saturday, October 19, 2013 1:10 AM
  • Hi,

    I hope you didn't execute the code I provided with the values in the example. Those values are domain names and user names from the domain I work on. The domain my SQL Server is member of is "comminus" and the user name I created a proxy with id "deansavovic" (this is my user name at work).

    First you have to determine is you SQL server member of domain or not, and do you want to use domain user name for the proxy or local user name (created on SQL Server machine). And also, you need to see under which user name the SQL Server Agent is running since this is the user name (i.e. login name) that will use the proxy.

    Let's take the following input parameters for the example:

    1. SQL Server is member of the domain and you will be using user name "YourDomain\YourUserName" for proxy
    2. SQL Agent is running under user name "YourDomain\YourSQLAgent" user name and is not member of sysadmin role

    Now execute this to create credential, proxy and assign user rights to SQL Agent to use the proxy.

    create credential [YourCredential]
    	with identity = 'YourDomain\YourUserName';
    declare @proxy_id int;
    
    exec msdb.dbo.sp_add_proxy
    		@proxy_name = 'YourProxy',
    		@enabled = 1,
    		@description = 'your test proxy' ,
    		@credential_name = 'YourCredential' ,
    		@credential_id = null ,
    		@proxy_id = @proxy_id OUTPUT
    
    select @proxy_id as proxy_id
    exec msdb.dbo.sp_grant_proxy_to_subsystem
    		@proxy_name=N'YourProxy'
    		, @subsystem_name = 'Dts'
    exec msdb.dbo.sp_grant_login_to_proxy 
    		@login_name = 'YourDomain\YourSQLAgent' 
    		, @fixed_server_role = null
    		, @msdb_role = null
    		, @proxy_id = null
    		, @proxy_name = 'YourProxy'
    If your SQL Server is not member of domain then change the YourDomain with YourServerName and for YourUserName and YourSQLAgent use appropriate user names from your server (SQL Server).




    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Saturday, October 19, 2013 1:34 PM
  • If I create a Proxy manually by clicking SQL Server Agent and right-clicking Proxy, I can enter the Proxy Name and Credential Name (click the box and click Browse and then click the name that there) and then check the box for SQL Server Integration Services Package.

    Then, I get this message:

    Proxy (blah, blah, blah) is not a valid Windows User.

    I think that's the whole problem for this thing.  How do I get a valid Windows user name?  It must be looking for something on my system.  What is it???

    I have SQL Server Developer 2012 and Windows 8.


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


    • Edited by ryguy72 Saturday, October 19, 2013 3:52 PM
    Saturday, October 19, 2013 3:48 PM
  • Hi

    The first step (create credential) is what is wrong in your case. SQL Server will let you create credential with invalid user name but when you try to create a proxy with that credential you will get an error you are getting.

    Your valid windows user name can come from two places:

    1. Active Directory domain user name if your SQL server is a member of domain
    2. Local user if your server is not a member of domain

    Here is how you will get a valid user name:

    1. SQL server is member of domain - contact your system/domain administrator to create a user name you will use for executing your SSIS job steps
    2. SQL Server is not member of domain - open Computer Management (Control Panel -> Administrative Tools -> Computer Management), click Local Users and Groups and click Users. Here you either pick one valid user name or create one.

    When creating credential you reference you user name:

    1. DomainName\username - SQL Server is memeber of domain
    2. MachineName\username - SQL Server is not member of domain

    Make note that you can use point 2 for valid windows user name even if you SQL Server is a member of domain, but then your SSIS job step will not be able to access any network resources.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Saturday, October 19, 2013 4:19 PM
  • Let's take an actual real-world example.

    create credential [logintest]
        with identity = 'EXCEL-PC\EXCELDEVELOPER';  --Server Name

    This compiles.

    File > Connect Object Explorer > Server Name

    Ok.  That's fine.

    As soon as I run this SQL, I get an error.

    declare @proxy_id int;

    exec msdb.dbo.sp_add_proxy
            @proxy_name = 'MyProxy',
            @enabled = 1,
            @description = 'Login Test' ,
            @credential_name = 'logintest' ,
            @credential_id = null ,
            @proxy_id = @proxy_id OUTPUT

    select @proxy_id as proxy_id

    Msg 14529, Level 16, State 1, Procedure sp_add_proxy, Line 45
    Proxy "EXCEL-PC\EXCELDEVELOPER" is not a valid Windows user.

    (1 row(s) affected)

    'not a valid Windows user'

    Ok.  Fine.  What the heck is a valid WINDOWS USER?  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.

    Sunday, October 20, 2013 2:09 PM
  • Hi,

    The error you are getting means that ExcelDeveloper is not a user on machine Excel-PC.

    Is your SQL Server running on machine named Excel-PC?

    Do you have a user named ExcelDeveloper on your machine Excel-PC? You can check this under Local Users and Groups in Computer Management console.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Sunday, October 20, 2013 2:42 PM
  • Yes.  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.

    Now, when I go look for 'Ryan' under Select Credential Browse, I don't see it anywhere. 

    If I type 'Ryan' I get the error message below (I can only upload 2 images per post).


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

    Monday, October 21, 2013 4:11 AM
  • Now, I'm right back where I started.  Ugh.


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

    Monday, October 21, 2013 4:12 AM
  • Keep your head up, I think you are close to solution of your problem.

    Now you are trying to add credential that does not exist to the proxy. First you have to create the credential for identity Ryan and then create the proxy that references this credential.

    List of users on you machine can be seen in Computer Management. Open run command window (windows button + R) and type this: "compmgmt.msc /s". Without quotation marks.

    This will open Computer Management. Then go to Local users and Groups and then to Users. Here you can see a list of users on you machined and you can add a new one if you want to.

    Pick one user and create credential for it and after that a proxy. How to do this can be found in previous posts. You can use the SSMS gui also.


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    • Marked as answer by ryguy72 Tuesday, October 22, 2013 4:16 AM
    Monday, October 21, 2013 2:19 PM
  • Nope, still not working.  It's like SQL Server won't recognize the change; it won't refresh.  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. 

    Thanks for the help.  I really appreciate your effort and the time you spent to help me get this working.  I can't spend any more time on this.  This was just for fun.  Now I've got real work to do. 


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


    • Edited by ryguy72 Tuesday, October 22, 2013 11:37 AM
    Tuesday, October 22, 2013 4:16 AM
  • Hello experts.  I finally got this working!!  Hallelujah!!

    Here’s how I did it.  I went through the steps in these two links:

    http://www.bidn.com/blogs/DonnyJohns/ssas/1705/sql-server-agent-proxy-accounts

    http://nirav.extreme-advice.com/2013/05/08/schedule-ssis-package-without-deploying/

     

    Also, and this was the missing link for oh so long . . . you need a dang User account!!  I thought I could use my default, but for some reason, which I never quite figured it out, that never worked for me.  I finally had the forethought to create a brand new account, assign a password, and re-try running the project.  Follow the steps here to create your User Account.

    http://www.sevenforums.com/tutorials/103570-user-accounts-add-remove-groups.html?filter[2]=User%20Accounts

     

     

    After creating the User Account, and adding a new password, run through the steps in the first two links I posted. 

     

     

    Argh!!  Frustrating!!  I was so close, and so very far away too!!

    Well, with some help from the kind people here, as well as applying persistence and perseverance, I got it working.

     

    THANKS EVERYONE!!


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

    Sunday, November 17, 2013 8:48 PM