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.
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.
ASKER
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.
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.
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.
ASKER
How do I fix this? If I log in as SA don't they have admin authority?
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
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
ASKER
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?
it has a log on as Network Service. What does this mean as far as getting administrative rights to the operating system?
ASKER
Please spell out the steps I need to make. This is new to me. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Linda,
Please let me know again what you're trying to do, step by step.
ASKER
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.
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.
ASKER
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?
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?
ASKER
I found that once I saved the package to SQL Server it worked without any problems.
Thank you
Thank you
That will help you understand the difference
http://strictlysql.blogspot.com/2010/01/job-ownersql-agent-account-proxy.html