none
Permissions issue when accessing other servers from a Stored Procedure RRS feed

  • Question

  • I have a web page button that kicks off a stored procedure.  The SP runs, except for when it is accessing a remote files server via a powershell call.  It works for Server Admins, but not for non-admins, even though they have been given full rights to the directories on the file server and to the database.  What other settings am I missing here?
    Thursday, August 17, 2017 3:30 PM

All replies

  • Are you invoking Powershell via xp_cmdshell in the stored procedure? In that case, you need to create a xp_cmdshell proxy account to specify the OS security context for non-sysadmin users (sysadmin role members use the SQL Server service account).

    See https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, August 17, 2017 3:37 PM
    Moderator
  • I added a local user account and added it to the local administrators group.  I ran the sp_xp_cmdshell_proxy_account command on this login and the xp_cmdshell still fails for the user.  Do I need a different account type or group membership?
    Thursday, August 17, 2017 7:05 PM
  • You mentioned file server so I think you'll need a domain account with permissions to the remote share. A local account won't be honored by the file server.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, August 18, 2017 1:32 AM
    Moderator
  • I added my domain account, which works, and the xp_cmdshell still fails. I made the user a sysadmin on SQL Server and it works. I don't want to leave that in place, so any additional ideas would be appreciated. 
    • Edited by mileman Friday, August 18, 2017 4:25 PM
    Friday, August 18, 2017 4:02 PM
  • I added my domain account, which works, and the xp_cmdshell still fails. I made the user a sysadmin on SQL Server and it works.

    Do you mean you specified your domain account as the xp_cmdshell proxy account and the proc failed? Are you able to execute the script interactively on the SQL box?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, August 18, 2017 4:44 PM
    Moderator
  • I am able to run it using my credentials on the SQL box or by using the button on the web page.
    Friday, August 18, 2017 6:59 PM
  • try with linked server .

    Mssql installation on Centos

    Saturday, August 19, 2017 2:03 PM
  • I am able to run it using my credentials on the SQL box or by using the button on the web page.

    So you are successfully running the Powershell script from the command-line on the SQL Box. Is the domain account used by the web page also your domain account, and is it a sysadmin role member? What is the exact error xp_cmdshell fails with when run by a non-sysadmin role member?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, August 19, 2017 3:25 PM
    Moderator
  • Yes, I am successfully running the powershell from the SQL Box.  The web page passes the authenticated user through to the SQL Box.  My login has the sysadmin role, but the ones that fail are not in the sysadmin role.  I do not know how to view the error that generates.  I only know that the function is not completed.  How can I record the error that the xp_cmdshell generates?
    Monday, August 21, 2017 7:03 PM
  • When you say "the web page passes the authenticated user through to the SQL Box", you mean the SQL connection is made under the end user's account? 

    To test the PS script interactively, logon using the proxy account and run the script to see the error.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, August 25, 2017 11:27 AM
    Moderator
  • If I log in directly, I don't get an error.  It will run successfully.  It only fails when the website calls it.

    Greg

    Tuesday, September 5, 2017 1:41 PM