DTS package not running under user other then sa
-
Hi,
I am writing a asp.net app where a user can select a parameter, send it to a stored procedure which runs a dts package based on the parameter. The app uses a different user (web_user) to connect to sql. It has db_datareader, db_datawriter and public access. web_user has permission to execute the stored procedure. Here is the flow:
user selects value from drop down list on web page and clicks submit --->
this is sent to a stored procedure via sqlparameter --->
stored procedure runs dts based on the value user selected (this does not work)
Here is the stored procedure:
CREATE PROCEDURE [web_IMPORT_DATA]
@RegionID VARCHAR(10)
AS
/*
Since I am calling from a db other then master I have to name db
and owner.
*/
IF @RegionID = 'MW'
EXEC master.dbo.xp_cmdshell 'dtsrun /S JUPITER /N Import_MW /E '
IF @RegionID = 'NE'
EXEC master.dbo.xp_cmdshell 'dtsrun /S JUPITER /N Import_NE /E '
IF @RegionID = 'SO'
EXEC master.dbo.xp_cmdshell 'dtsrun /S JUPITER /N Import_SO /E '
SELECT 'DONE! ' + @RegionID
GO
JUPITER is the sql server and web_user has permission to execute xp_cmdshell. Also, I logged into sqlquery as web_user and <password> and did the following:
EXEC web_IMPORT_DATA 'NE'
All I get is DONE! NE.
But if I run it after logging into sqlquery as sa and run the same thing then I get the data imported. Any help will be appriciated.
-
Not 100% sure, but it might be the thing that you in your code for executing the dts-package use "/E" (trusted connection).
When you are logged in with a user with system administrator-right you will return back to SQL Server with the account that SQL Server runs under. Wich of course have to correct rights to login again. If not a member of System Administrator role, you wont be able to return with trusted connection to SQL Server - IF you don't use Proxy Account.
You have (at least) three different ways to solve this.
1. Easy, dirty and not the way to go = set the web_user as system administrator in SQL Server.
2. Change the stored procedure to execute the dts-package specifying the user and password (/U... /P...). Not very nice to put the password in a script tho. If you go for this (easy fast) solution I would suggest you to encrypt your sproc.
3. Setup SQL Server to use a proxy acccount. When a user want to access something outside SQL Server, and the user is not a member of system administrator role, Proxy Account will be used. Proxy Account can be either a local user on your server, or a member of your domain.
I guess that you want to have this solved rather quickly? If not, I'm currently writing an article for SqlServerCentral about the use of Proxy Account...
Good luck!
robbac
___the truth is out there___ -
If I do the user and password like so:
EXEC master.dbo.xp_cmdshell 'dtsrun /S JUPITER /N Import_MW /U web_user /P *** '
where *** is the password this does not seem to work for me. Am I doing the syntex wrong? I cant find any examples. Also, I didnt mention it before but 'public' can execute xp_cmdshell. So web_user should be able to execute it. And, this is the error I get:
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 997 from GetProxyAccount on line 472
-
Hi!
I don't think the error is about executing xp_cmdshell. If that would be the problem, then you should get an access denied instead. The error indicates that it's looking for ProxyAccount. Is the dts-package using anything on disk, ie importing a file or writing to a file? If it is - then you need to set ut a proxy account, or else SQL Server won't let you outside SQL Server unless the user is a member of the server role system administrator...
robbac
___the truth is out there___ -
I have tried adding a proxy account SQLProxy1 (nt domain account) and have changed the sqlServerAgent settings so it starts with SQLProxy1 and restarted the db. I have also added the nt group to the db users and gave it 'public ' access. Still getting the same error when I do the following:
EXEC master.dbo.xp_cmdshell 'dtsrun /S JUPITER /N Commission_Import_MW_new /U ATI\SQLProxy1 /P SQLabcdef'
I have used the domain admin account and password also (with the correct info) and still get the same error as my previous post.
Thanx in advance for all your help.
-
Can you pls check the web user is a part of MSDB database. And it's cumbersome to create a user in MSDB for each of the web user.
Since you call the dts package from the procedure, I hope there's no trouble in executing the package on the dbo account.
Thanks,
Ganesh
-
Hi!
Just wanne check, did you set ProxyAccount1 as startup account for SQL Server Agent or as a Proxy Account in SQL Server agent?
Proxy account is addes in:
Right Click on SQL Server Agent (in EM) and select Properties, change to tab Job System. Uncheck "Only Users with SysAdmin privileges..." and fill in settings for the Proxy Account.
robbac
___the truth is out there___ -
I did not do that part. I will let you know what happens when I do this. I am waiting for the NetAdmin for his confirmation.
Thank you all for your help.
-
YEAH!!!!!! it worked. Thank you Robbac. I spent of and on about 1 week on that issue. Again thank you.
-
I'm glad that this help. Just fun to be of any assistance.
Have a nice weekend.
robbac
___the truth is out there___ -
Robbac?
Hey - thanks for your posts so far - very helpful.
I still have a problem however.
If I make my SQL Server user a member of sys_Admins, all works fine. However, adding myself as a proxy does not work at all.
I'm not runnning my DTS package from a job - I'm running it from an SP. It seems like the "Proxy User" applies only to SQLServerAgent jobs - you know - run on a schedule : not to SP jobs.
Any ideas ?
-
Here's more:
I also tried using my NT Account to no avail - and I'm the dbo!!!
EXEC Master.dbo.xp_sqlagent_proxy_account N'SET',
N'_MyDomain_', N'BillyWilly', N'_MyPassword_'
Nothing.
However, if I make my SQL Account (DB_Generic is the name) a member of sys_admins - works like a charm - UGH !!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply