May 10, 2011 at 9:53 am
Hey Steve, I'm the Admin to the server and I added my user specifically with all the user rights to run the jobs. Is there something else I should check from what you are suggesting? How can I give the SQL Agent permission to access the file if it doesn't have it already, I have already tried the whole proxy/credential route and it did not work for me at all. That is why I'm at this point 🙁
May 10, 2011 at 9:55 am
stormsentinelcammy (5/10/2011)
Hey Steve, I'm the Admin to the server and I added my user specifically with all the user rights to run the jobs. Is there something else I should check from what you are suggesting?
It's not your account. When you run the job, it's the same as asking the person next to you to go execute this job. If they don't have permissions to open the package, they can't run it.
In this case, the person next to you is either the SQL Agent service account or the proxy account. This has nothing to do with your account.
May 10, 2011 at 10:01 am
Hey Ken, that was how I had it previously, but the last two pictures that I uploaded, I show the file in the server where I'm running the job from. The package is in the server and I'm trying to call the package from the server where it resides in, when I run the job through the file system it runs fine as I show in the picture I have attached with this post. Is there a reason that even though I can run this job on the server that I can't run the job through the file system in the SQL Agent even though I have all the permissions and all the rights? If there are some possible permissions or rights that I need to make sure that I have checked off or have please let me know so that I can make sure that I have those assigned to my user account. Thanks
May 10, 2011 at 10:06 am
Thanks for the quick response steve, what process should I go through to make sure that the SQL Agent service account or the proxy account has the correct permissions to allow the job to run? I'm not trying to ask the same question over and over again, I just want to make sure that I do what I need to do to make sure that this process works.
May 10, 2011 at 10:11 am
The process should be putting packages in a standard location, then assigning windows permissions to the accounts that run the package. If this is the SQL Agent service account, there should be a group for that agent account. It is SQLServerAgentUser$xxxxx and that is where you should assign rights. Go to the folder with the packages, add that account with read, and potentially write packages.
May 10, 2011 at 10:13 am
I thought that I provided you sufficient information on how to do this but perhaps that is not the case.
Microsoft Press 70-290 goes into great detail on this subject matter.
I have the book but it is in archives.
Perhaps you may want to try advanced goggle and hopefully you will be able to find a better explanation then what has been provided to you.
Best of luck.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2011 at 10:19 am
Could you please give me a path to do this i.e. "Go to the server>SSMS>Right click on the server> select on permissions in "Server properties" If you could give me some process methodology like that, it would help me out so much more. I hope I'm not asking for much, I do understand what you are saying, I just don't know where I would be able to find what you are speaking of. Thanks again for your help.
May 10, 2011 at 10:21 am
I think you might have welsh, but to be honest, unless I see it being done with examples it might not apply totally to my situation. Plus I'm just wondering if things have gone off because of permissions or things as such that negate other things that I might be trying to do.
May 10, 2011 at 10:33 am
stormsentinelcammy (5/10/2011)
Could you please give me a path to do this i.e. "Go to the server>SSMS>Right click on the server> select on permissions in "Server properties" If you could give me some process methodology like that, it would help me out so much more. I hope I'm not asking for much, I do understand what you are saying, I just don't know where I would be able to find what you are speaking of. Thanks again for your help.
From the server
Open windows explorer -> navigate to the directory you have your package (I'm assuming its local to the server) -> Right click on the directory name -> Properties -> Security Tab -> Add -> SQLServer2008AgentUser$ServerName$InstanceName -> Grant it the permissions it needs.
You can look it up the group by hitting the Advanced button on the Add screen -> Confirm the location is your server name (if not change it by hitting the locations button), then type SQL in the name field (if grayed out just hit find now for a full list and scroll).
Hope that helps.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 10, 2011 at 10:34 am
Have you logged onto the SQL Server using the SQL Server Agent Credentials?
If not Logon.
Can you navigate to the path of the file? Is it visible?
Can you open the file?
Try that and let us know.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2011 at 11:08 am
Thank you so much guys, I will try the last two things and get back to you in a few minutes.
May 10, 2011 at 11:27 am
Hey welsh, I can see the files and open the files when I'm logged on as myself, although I do not know how I would log on to SQL Server using the SQL Server Agent Credentials; if you're referring to just adding credentials and proxy and running the job using the proxy, then I have done that many times and no dice with that. Maybe I'm getting the message wrong. Could you give me steps to follow to make sure that I'm following exactly what you want me to try.
May 10, 2011 at 11:30 am
Thanks for the help Ken, I followed what you proposed, I was able to add the user and give it full control so that I don't have to worry about anything, when I ran the job I got the error below. I believe that I might be moving in the right track. I have attached the picture to this post. I have also added my SQLAgent Folder Settings photo with the post as well.
May 10, 2011 at 11:50 am
It is possible that you may have an issue with package level security.
Please refer to the following article. I will give you an introduction but to resolve your issue it will probably take a lot more effort. If you save the package with the ProtectionLevel package property
as do not save sensitive information and subsequently deploy and run the package as a job and you do not get an error, then this is probably a package level security issue.
But it is hard to say at this point.
What is the package level security set to?
http://bibits.co/post/2010/10/24/SSIS-Packages-e28093-Security-and-Deployment-Issues.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 10, 2011 at 12:04 pm
I have the package level security set to "Dontsavesensitive", before I had it set at default "Encryptsensitivewithuserkey". Yes welsh, I have already crossed lots of bridges in trying to solve this :), but thanks for pointing that out too. At least we can strike out one more possibility off the ledger 🙂
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply