March 12, 2013 at 4:51 pm
I get this error when I attempt to run the Package using the Domain Account, SQLServerAgent.
Message
Executed as user: MyDomain\SQLServerAgent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 01:29:42 PM Could not load package "\Dev\AS400\CYP_Staging_Load_Truncate_Nightly" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E4D (Login failed for user 'MyDomain\SQLServerAgent'.). The SQL statement that was issued has failed. Source: Started: 01:29:42 PM Finished: 01:29:42 PM Elapsed: 0.046 seconds. The package could not be loaded. The step failed.
The package is stored in MSDB.
I added the Account to sysadmin and made it a local admin.
It works with my login.
It is totally weird.
I granted permissions to the Temp Folder for the SQLServerAgent but no dice.
Any ideas?
Thanks.
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/
March 12, 2013 at 4:53 pm
Does this package perform any tasks on the filesystem (read files, check file existence, delete files, etc)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2013 at 5:01 pm
No File System Operations.
It pulls Data from Oracle and loads into into aSQL Server Staging area.
It is not just one package but all ETL packages that I have tested.
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/
March 12, 2013 at 5:08 pm
Try changing the sql agent service account using configuration manager. Even just reset it back to the same service account currently in place.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2013 at 5:10 pm
SQLRNNR (3/12/2013)
Try changing the sql agent service account using configuration manager. Even just reset it back to the same service account currently in place.
I tried that a few times already. Unfortunately no luck.
Thanks!
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/
March 12, 2013 at 11:48 pm
Are all of the packages performing tasks related to Oracle or something else?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 6:29 am
AS400 DB2. I have an AS400 login so that is not the issue.
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/
March 13, 2013 at 11:58 am
Did you check the 'protection level' on the package? I had faced a similar error earlier and it was resolved after I changed the protection level from 'EncryptAllWithPassword' to 'DontSaveSensitive'.
March 13, 2013 at 12:03 pm
Seemita Das (3/13/2013)
Did you check the 'protection level' on the package? I had faced a similar error earlier and it was resolved after I changed the protection level from 'EncryptAllWithPassword' to 'DontSaveSensitive'.
I'm doing that but that is not relative to my problem.
The only password that it has to save is the Login to the AS400 which is not a Windows Account.
The Job that Executes the SSIS Package which is stored in MSDB succeeds when I have the SQL Agent running using my credentials.
If I switch it to a Domain Account SQLServerAgent it succeeds.
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/
March 13, 2013 at 12:09 pm
Welsh Corgi (3/13/2013)
Seemita Das (3/13/2013)
Did you check the 'protection level' on the package? I had faced a similar error earlier and it was resolved after I changed the protection level from 'EncryptAllWithPassword' to 'DontSaveSensitive'.I'm doing that but that is not relative to my problem.
The only password that it has to save is the Login to the AS400 which is not a Windows Account.
The Job that Executes the SSIS Package which is stored in MSDB succeeds when I have the SQL Agent running using my credentials.
If I switch it to a Domain Account SQLServerAgent it succeeds.
Have you tried adding the Agent Service Account as a user on the AS400?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 13, 2013 at 12:39 pm
When you installed SQL Server 2008 - did you install with the default Service SID?
If so, then your domain account running SQL Server agent does not have access to SQL Server. The service account needs to be manually added and granted the appropriate rights in SQL Server.
My guess is that when you changed SQL Server to run under your domain account, it works because your domain account is sysadmin in SQL Server. When you changed it back to the normal service account - it does not have appropriate rights.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2013 at 2:03 pm
Jeffrey Williams 3188 (3/13/2013)
When you installed SQL Server 2008 - did you install with the default Service SID?If so, then your domain account running SQL Server agent does not have access to SQL Server. The service account needs to be manually added and granted the appropriate rights in SQL Server.
My guess is that when you changed SQL Server to run under your domain account, it works because your domain account is sysadmin in SQL Server. When you changed it back to the normal service account - it does not have appropriate rights.
Before I posted this I added the SQLServerAgent Domain Account to sysadmin and local admin on the Server.
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/
March 13, 2013 at 9:37 pm
March 14, 2013 at 6:17 am
sankar276 (3/13/2013)
Sorry but that issue in the article that you provided is totally unrelated to the issue that I'm having.
Thanks.
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/
March 14, 2013 at 5:09 pm
SQLRNNR (3/13/2013)
Welsh Corgi (3/13/2013)
Seemita Das (3/13/2013)
Did you check the 'protection level' on the package? I had faced a similar error earlier and it was resolved after I changed the protection level from 'EncryptAllWithPassword' to 'DontSaveSensitive'.I'm doing that but that is not relative to my problem.
The only password that it has to save is the Login to the AS400 which is not a Windows Account.
The Job that Executes the SSIS Package which is stored in MSDB succeeds when I have the SQL Agent running using my credentials.
If I switch it to a Domain Account SQLServerAgent it succeeds.
Have you tried adding the Agent Service Account as a user on the AS400?
I didn't see the answer to this question so am re-asking it.
We know it works with your account but not the service account. Have you tried adding the service account as a user on the AS400?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply