March 9, 2005 at 7:10 am
Hi,
MSSQL 2000/SP3
We have an external web client/app (using sql authentication) that would like to extract data from a db table onto their local workstation (ie:C:\temp) ... frquency is done on a ad-hoc basis and user decides on table, so unable to use DTS dumps at scheduled times ...
A developer wrote a stor proc that runs bcp via xp_cmdshell. When this sp is called, is there a way to grant the web client account temp exec rights to xp_cmdshell, then revoke, so as to not leave the door open? Is there another way to run bcp? Any work-arounds or suggestions would be greatly appreciated.
Many thanks. Jeff
March 10, 2005 at 3:58 am
In my opinion, SQL batch job security is a total mess.
If you do not have the SQL Agent proxy account enabled, then only members of sysadmin can run xp_cmdshell. This means for your problem, you could make the web account a member of sysadmin for a short time then remove it. You just have to hope that removing from sysadmin works, and that no-one is able to hack the account while it is sysadmin... Also, all batch jobs from all sysadmin accounts execute with the NT authority of SQL Server (i.e. the service account or local system if you do not use a service account.)
If you do have the proxy account enabled, then you can authorise specific accounts to use xp_cmdshell. However, all batch jobs from non-sysadmin accounts run using the authority of the proxy account. There is a further catch - if you enable the proxy account, then any user can create a SQL job step that uses the Operating System Command subsystem - you cannot restrict this functionality. All jobs set up in this way run inder the proxy account authority.
There are some other oddities - If your job step uses the Advanced tab to specify a job output file, this is managed using the NT authority of SQL Server. The same applies if you click 'Append output to step history'. Consistant it is not.
In spite of all this, we have enabled the proxy account in our shop on the basis it is the lesser of all evils. We need some SQL batch jobs to access application files, and we absolutely must run these jobs outside of the SQL Server service account authority. The only way we can see to get a more workable solution for batch work is to buy a job scheduler, such as BMCs Control-M, etc.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 10, 2005 at 9:34 am
Jeff,
One solution to this kind of problems is to use the "pooling job method". Supposed you have a table that is monitored by a job and all your app has to do is Write a record on it and when the pooling job detects the change it will run either your DTS job or another job that uses xp_cmdshell to export to a share and send som notification to the app that the job was done.
This kind of set up avoids all those security concerns!
HTH
* Noel
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply