[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Running an SSIS package in SQL 2008 or 2005

Posted on 2011-10-23
12
Medium Priority
?
337 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:LenTompkins
  • 7
  • 5
12 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37018691
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
 

Author Comment

by:LenTompkins
ID: 37019145
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
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37019171
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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 

Author Comment

by:LenTompkins
ID: 37019218
How do I fix this? If I log in as SA don't they have admin authority?  
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37019245
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
 

Author Comment

by:LenTompkins
ID: 37019717
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
 

Author Comment

by:LenTompkins
ID: 37019722
Please spell out the steps I need to make.   This is new to me. Thanks
0
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 1500 total points
ID: 37019744
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
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37019756
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
 

Author Comment

by:LenTompkins
ID: 37019986
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
 

Author Comment

by:LenTompkins
ID: 37037924
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
 

Author Closing Comment

by:LenTompkins
ID: 37080234
I found that once I saved the package to SQL Server it worked without any problems.
Thank you
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question