Execute A Stored Procedure Using A Proxy Account
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 5 Replies
ADVERTISEMENT
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 2 Replies
View Related
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account in SQL Sever 2005.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 1 Replies
View Related
Mar 2, 2004
Hi all, i hope you can help me.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Can anyone help?
View 2 Replies
View Related
May 8, 2007
Hi Experts,
Is it possible to connect to SQL Server just using the SQL server agent's proxy account and access data ( without SQL management studio or SQL / Window authentication ). The proxy account is active only for SSIS package execution subsystem.
Thanks in advance,
DBLearner.
View 1 Replies
View Related
Aug 3, 2005
I'm trying to set a proxy account for the SQL agent. The user is Local
administrator on the SQL Server when I try to set the account I get a message back that says
"The system cannot find the path specified."
I get the same error with TSQL too.
EXEC master..xp_sqlagent_proxy_account
'SET', N'MY_DOMAIN', N'Myadm', N'MyPassword'
My system :
Windows Server 2003 SP1
SQL Server 2000 SP4
I am worried it might be interfering with someing.
Any ideas what paths it could be after?
View 5 Replies
View Related
Jan 14, 2015
I have a frustrating problem where I am using the Ola Hallengren jobs to backup to a network share. (This isn't something specific to his scripts).
For various reasons the SQL Server account can not be granted access to the share so I thought I would use a proxy account which does have access (this has been fully tested). I am using a CmdExec proxy.
The problem comes now that when I run the job it still thinks access is denied when running the xp_create_subdir command.
When I recreated this problem locally on my machine, as soon as I add the SQL Server account access to the share the backups work, so why isn't the job using the proxy account?
View 1 Replies
View Related
Oct 30, 2006
I am trying to run SSIS packages under SQL Server Agent 2005 and I keep getting a package failed error in the event viewer.
I've heard that I need to set up a proxy account. I have found the following code and need a little explanation on what all the parts mean since I am very new to this:
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomainmyWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name=' devlogin', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
Let's say for the sake of argument my domain is called CompanyInc and I log into windows with my name Philip_Jaques and my password is badpassw0rd. Would I modify the above code this way to create my proxy?
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'CompanyIncPhilip_Jaques', secret = 'badpassw0rd'
Use msdb
Sp_add_proxy @proxy_name='MyProxy', @credential_name='MyCredential'
Sp_grant_login_to_proxy @login_name='Philip_Jaques', @proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='SSIS'
Also, when I create this proxy account where in SQL Server 2005 can I go to view it and its properties? And assuming I get the proxy account set up correctly, how do I get my current jobs to start using it so they will successfully run?
Thanks in advance for your help and advice!
View 2 Replies
View Related
Feb 14, 2008
There is one thing that€™s confusing me in creating a proxy account.
I am trying to get an SSIS package configured as a SQL Server job and execute it from a non-sysadmin login. But when I execute it gives the error message:
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
I know that we have to create a proxy account for this to happen and creating of proxy account prompts me to choose a credential, and that is where I do not understand the logic. From MS website I can find the following, but it is confusing to me
This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
ref: http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760
I tried reading all the related articles, but still the process of creating the credential is confusing to me, can someone throw some light on the logic of proxy/credential here?
Thanks
Satya
View 23 Replies
View Related
Jul 31, 2006
Hi,
because my package does not run with SQL-Server-Agent, but without problems if started by "hand", I created a new credential which contains the information needed for the package. I did this as described on: http://msdn2.microsoft.com/en-us/library/ms190703.aspx .
After that i tried to create an proxy account, but when I chose the created credential, Management Studio says "Der Proxy "[name_of_credential] ist kein gültiger Windows-Benutzer(Microsoft SQL Server, Fehler: 14529)". This means something like: "This Proxy is not a valid windows-user. Error: 14529".
Any hints how to use a credential that is not a windows-user?
Regards,
Jan Wagner
View 7 Replies
View Related
Feb 15, 2006
Hi,
I have an SSISS package running in a job step, the job is owned and has to be run by a non-sysdamin SQL login. I have created a new credential (windows autrhentication, sysadmin priviledges) , mapped it to proxies 'SSISS' and 'Operating systems (CmdExec)' and have assigned the job step to run as that credential. Yet I'm getting the following error message:
Unable to start execution of step 1 (reason: Error authenticating proxy DomainUserName, system error: Logon failure: unknown user name or bad password.). The step failed.
Does anyone know why this is?
Thanks
View 4 Replies
View Related
Jan 2, 2008
Hello,
I'm having trouble setting up PROXY account. I follow the documentation, and Under Credentials, I can only select Operator. Is there a way to select any account under Security?
Your help is appreciated!
View 3 Replies
View Related
Apr 26, 2007
Hi experts,
Is there any potential security threat using Proxy accounts in SQL Server 2005 ? If any , Please give URLs for reference.
Thanks,
DBLearner
View 3 Replies
View Related
Feb 5, 2008
Hello,
I am trying to create a job that runs a SSIS package. I am getting the following error:
Message
Unable to start execution of step 1 (reason: Error authenticating proxy domainsckeels, system error: Logon failure: unknown user name or bad password.). The step failed.
Our DBA ran the following to create the credentials and proxy account.
USE MSDB
CREATE CREDENTIAL [sckeels] WITH IDENTITY = 'domainsckeels', secret = 'sckeels_credentials'
GO
Sp_add_proxy @proxy_name='ssis_users', @credential_name='sckeels'
GO
Sp_grant_login_to_proxy @login_name='domainsckeels', @proxy_name='ssis_users'
GO
Sp_grant_proxy_to_subsystem @proxy_name='ssis_users', @subsystem_name='SSIS'
Any help with this would be appreciated.
Steven
View 7 Replies
View Related
Jul 12, 2007
I am trying to create a Proxy account to use for Job Scheduling, but when ever I add Logins to the Principals in the Proxy Properties and click OK, when I check again, they are not saved. Is there a reason why this happens? Is there an alternate way to add principals? Thanks.
View 2 Replies
View Related
Aug 15, 2007
Hi, I have the following setup:
- Win 2K Server, SP4
- SQL Srv 2K, SP4
- A 'DEV' domain, with an 'Administrator' account with all possible rights on the system
I need to configure a CmdExec proxy account in order to allow non-sys-admin users to execute the master.dbo.xp_CmdShell procedure.
When attempting to do this via QA as follows:
EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
N'DEV', -- agent_domain_name
N'Administrator', -- agent_username
N'password' -- agent_password
...I get the following error:
"The system cannot find the path specified."
When attempting to do this via QA as follows (note: only change is adding domain to agent_username arg):
EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
N'DEV', -- agent_domain_name
N'DEVAdministrator', -- agent_username
N'password' -- agent_password
...I get the following error:
"Error executing extended stored procedure: Specified user can not login"
I have tried this through Enterprise Manager and get identical results, of course.
I have also tried all of the following:
- different OS user accounts, including local system accounts with local admin rights;
- assigning the OS account to a SQL login with System Admin role/rights;
- specifically assigning the above SQL login with EXEC rights on the master.dbo.xp_CmdShell procedure;
- verifying local security policy settings, as per the following link: http://support.microsoft.com/?id=283811;
- pulling out my hair and banging my head against the wall.
Can anyone H E L P ? ! ! !
Thanks,
Joe
View 6 Replies
View Related
Oct 4, 2007
Re: SQL Server 2005
Does the xp_cmdshell proxy account need admin-level permissions on the server?
The reason I ask this is because I keep getting "Access is Denied" errors when trying to run this command as a non-admin:
master..xp_cmdshell dtexec 'some package'
The 'some package' has an "execute process task" which calls a batch file on the server.
If the proxy account is NOT a local admin, the "execute process tasks" fails with an "Access is Denied" error.
If the proxy account is a local admin, it executes fine.
We have given "Everyone" FULL CONTROL of all the folders that are affected by the batch file, and it still does not work.
I am out of ideas at this point. It just does not work unless it's an admin.
Are we missing something here?
View 7 Replies
View Related
Jun 16, 2015
I running SSIS package job without sql agent , it is working fine.when i am running through sql agent not running.
created Proxy accountÂ
job failed and give above error.
Server is cluster and taking data from desktop.
server is in one domain and desktop in another domain.
View 3 Replies
View Related
Oct 17, 2007
I want to know if I can debug stored procedures remotely with the SA account.
I can not have Active Directory in my system right now.
Thanks
View 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
May 2, 2008
Hi All,
I am here again for usual help, i am working to transfer data through ftp using proxy server, becuase in our dw environment the production server is not allowed to use IE, thus i am using proxy server. i have installed ftp client called Coreftp and i have configured it, it is working fine when i use command line, but when i use the same Argument on the Execute Process Task it is failing, here is the error:
[Execute Process Task] Error: In Executing "C:Program FilesCoreFTPcoreftp.exe" "coreftp.exe -d ftp://usernameassword@ftp.companyname.com/companyName_2008-04-30.zip -p \fileserver-core.company.corpFTP_FolderFTP_File_Extraction -B -s" at "", The process exit code was "30" while the expected was "0".
for your surprise with the same argument it works on the command line, but not in this task, can anyone help with this issue, i will be happy if you able to respond me as soon as possible.
Thank you in advance,
sami
View 9 Replies
View Related
May 29, 2008
Hi There
We executed windows scheduled batch failes that execute as DOMAINuser1 to dtsexec packages.
I moved these packages to run in sql jobs.
The agent runs as local system. In order to get the packages to run since they are encryoted with DOMAINuser1 key.
I created a DOMAINuser1 credential and a proxy.
I execute the SSIS package job steps under this proxy.
One day it all works the next day they fail randomly with the "unable to decrypt with user key" error.
I am very familiar with this error, however it makes no sense, thses packges are not being touched, no one is altering them and perhaps saving them with different user keys. They are all encrypted with the DOMAINuser1 key. One day everything works the next day they fail.
A master package will run and in the middle of executing other package it suddenly gets this error, like the proxy suddenly stops working.
I have found nothing in the event logs?
Any ideas ?
View 6 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
May 16, 2008
Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated.
Thank you,
Dave
View 6 Replies
View Related
Apr 14, 2004
Hi,
Can anybody please tell me if it is possible to execute an url from stored procedure.
thanks in advance,
preeth
View 1 Replies
View Related
Aug 10, 2000
Hello:
I have created a stored procedure that will backup and restore a database.
I would like to be able to execute this sp using public rights - any ideas?
Thanks
View 1 Replies
View Related
Jun 14, 2008
How can I use in stored procedure the faction ‘’in’’ to select values, using execute and the values must not be specified before in the stored procedure
View 6 Replies
View Related
Jul 23, 2005
How do you execute a stored procedure in MS SQL Server?I can design and execute them from MS Access dev front end, but cannot seemto find how to run them in the Enterprise Manager.TIA.~ Duane Phillips.
View 2 Replies
View Related
Mar 29, 2006
I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!
View 3 Replies
View Related
Oct 27, 2006
I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job? This is all run from a web page and NOT the Query Window.
View 1 Replies
View Related
Oct 17, 2007
Please anyone help me with my question
How do I execute a stored Procedure using SSIS ?
I have a stored procedure in SQL SERVER 2005 database that I need to execute using a SSIS package using Execute SQL Task from Toolbox in Visual studio 2005
Thanks,
George
View 2 Replies
View Related
Jan 22, 2008
Hi,
I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
please can someone point me in the right direction
View 13 Replies
View Related
Aug 24, 2004
hi to all,
i have a stored procedure and i want to run a program from it..
i think that i need use api functions but how can i do that..
if there is a nother ways please tell me..
thanx..
View 3 Replies
View Related
