Running an SSIS package in SQL 2008 or 2005

I am trying to create a job to run an SSIS Package .  I am getting an Error Authenticvatin proxy Linda-PC\Linda unknown User Name or bad Password.  
 I first logged in as SA and I created a new Login called JobExecuter.  On Server Roles I have SysAdmin and Public checked.  On User mapping: I selected the Database I am reading Data from and gave it DataReader and public.  On the database I am writing to, I selected db_datareader and db_datawriter as well as public.  On msdb I have SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentOperatorRole  and public checked.
When I created a Credential called SSISJobExecuter the Identity is Linda-PC\Linda with a password.  I think this is where my problem lies.  I wanted it to be Linda-PC\JobExecuter, but that would not take.   I am getting the User or Built in Security Principal cannot be found.
Then I created a Proxy called JobExecute and tied it to subsystem SQL Server Integration ServicesPackage.
Then I created the SSIS package and made sure that JobExecuter was the userID and gave a password for the destinationConnection OLDB and the sourceConnectionOLDB.  I tested the package in Visual Studio and it works.
I then created a new job and in step1 I said the type was SQL Server Integration Service Package and run as JobExecute.  The package source is FileSystem and I selected the location of the dtsx package file.
If anyone can straighten me out I would appreciate it.  Once I get this fixed
I need to create another copy on SQL Server 2005.  Is there anything else
that is different from 2008?
Thanks in advance.
LenTompkinsAsked:
Who is Participating?
 
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
The link was only to show you how to restart and check the services running account.
Anyway back to the subject....

You need to change that NETWORK SERVICES account to use whatever account that has admin rights on the other server.

You can't change the service account if you don't' have admin rights on the operating system (windows).
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Change the service account that runs the SQL AGENT as whoever has access. then restart the SQL AGENT SERVICE.

That will help you understand the difference
http://strictlysql.blogspot.com/2010/01/job-ownersql-agent-account-proxy.html
0
 
LenTompkinsAuthor Commented:
I gather from what I read in the blog that I need to change the job step proxy account to use a
non-administrator proxy account.  Unfortunately I can see the area to enter data ,but it would not
let me change it.  Do you have to stop the SQL Server Agent first?

If not the owner of the job is SA and I am signed in as SA as well as the job owner is SA.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Jason Yousef, MSSr. BI  DeveloperCommented:
Eventually you'll need to stop it ,before or after when you restart the service, but not letting you change it means you don't have an admin right on that machine or not running the services.msc console as an admin.
0
 
LenTompkinsAuthor Commented:
How do I fix this? If I log in as SA don't they have admin authority?  
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
SA is an admin on the SQL Server

You need to have admin rights on the operating system (Windows)
click on start, type services.msc  or go to control panel-admin....-services


like in this example
http://www.jasonn.com/turning_off_unnecessary_services_on_windows_xp
0
 
LenTompkinsAuthor Commented:
Thank you for the link to turning off unnecessary services, but when I looked at SQL Server Agent
it has a log on as Network Service. What does this mean as far as getting administrative rights to the operating system?
0
 
LenTompkinsAuthor Commented:
Please spell out the steps I need to make.   This is new to me. Thanks
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Please spell out the steps I need to make.   This is new to me. Thanks

Linda,
Please let me know again what you're trying to do, step by step.
0
 
LenTompkinsAuthor Commented:
I need to create a job that will run an SSIS package.  I gave you the steps I used to give the SQL
login ' JobExecuter'  the SQL authority to run the jobs.  I gather I am getting errors because there
is not a Windows Login called JobExecuter  that has administrative rights on the system.  Obviously
login 'SA' also does not have administrator rights on the system.  How do I give SA the administrative
rights on the Server? or do I just need to change the Credentials for SSIS JobExecuter to Network
Services?  I realize I am jumping around, but I'm not sure how everything ties together.
Thanks for assisting me.  
0
 
LenTompkinsAuthor Commented:
I have made a little progress.  I found out which login was administrator and I logged in using that
ID (Len).  I then opened SQL Server Management Studio as SA and I was able to change the Object
name from Network Services account to the username Len in my credentials SSISJobExecuter.
I ran the job again and this time I am getting the following error:  
Failed to decrypt protected XML node.  Key not valid for use in specified state.  You may not be authorized to access this information.
I'm wondering if the password that I supplied when I modified the Credentials was the wrong password.  Len is set up as a user that uses Windows authentication so I used the windows password.  Is this correct?  
Any other thoughts of where to go from here?
0
 
LenTompkinsAuthor Commented:
I found that once I saved the package to SQL Server it worked without any problems.
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.