March 16, 2011 at 12:28 pm
I have an SSIS package that runs fine in BIDS. It was a DTS in SQL 2000 (working fine) then when the server was upgraded to 2008 I migrated it to SSIS.
The package goes out to another server (SQL 2000) and using a a login I created on that server grabs data from a view. Now I'm getting an error of "Login Failed for [myloginOnThatServer]".
Could it really be something with that login or is it the way I'm running the job? I tried creating a proxy etc. and received essentially the same error. I'm running out of ideas.
Thanks a lot, Mike
March 16, 2011 at 1:16 pm
Chances are it has to do with the ProtectionLevel of the SSIS package. When you run it through BIDS it is running as you so has access to your user-key. When you run it from the server it runs as someone other than you so fails to decrypt the password you saved in the connection object as it does not have access to your user-key. Check out this thread: http://www.sqlservercentral.com/Forums/Topic1076292-364-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 1:23 pm
Does it work from BIDS and not SQL Agent?
Does this package access fileshares?
Was the account you created, done so in SQL Server or was it a Local Account on the server?
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 16, 2011 at 3:39 pm
Thanks for the replies. I changed it to DontSaveSensitive and got the same result. Do I have to do anything in BIDS other than save the package for a change to be reflected?
Does it work from BIDS and not SQL Agent?
-Correct. While I have been testing from Mgmt Studio just trying to run the job, ultimately it is run from a stored proc called from an Access db. I tried it that way also with same result.
Does this package access fileshares?
-No
Was the account you created, done so in SQL Server or was it a Local Account on the server?
-I created it in SQL Server
How should it be set up on the General tab of the Steps page in the job properties? Right now I have it Run as: SQL Server Agent Service Account, with Log On to the Server as: Windows Auth.
I tried with a proxy and got the same result. Not sure if I had it and the cred set up correctly though, but same result makes me think I did.
March 16, 2011 at 3:53 pm
Is the package stored in msdb or on the filesystem?
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 16, 2011 at 4:24 pm
I saved it to the file system.
I just discovered, though, that the connection manager is failing in BIDS with the same login error. Sorry about that, I'm new to BIDS.
March 16, 2011 at 4:39 pm
you may want to reset the SQL account password for that user and then try again.
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 16, 2011 at 5:31 pm
I changed the password but same result. I have that login set up as a user in the msdb in the target database. Any certain roles or securable settings there maybe?
BTW, when I test the connection in BIDS it succeeds, but fails when I run the package in BIDS.
SSIS package "CamsImportToDOTTime2011.dtsx" starting.
Information: 0x4004300A at DTSTask_DTSDataPumpTask_1, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at CamsImportToDOTTime2011, Connection manager "Microsoft OLE DB Provider for SQL Server": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'DOTTimeReadOnly'.".
Error: 0xC020801C at DTSTask_DTSDataPumpTask_1, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at DTSTask_DTSDataPumpTask_1, SSIS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at DTSTask_DTSDataPumpTask_1, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at DTSTask_DTSDataPumpTask_1: There were errors during task validation.
SSIS package "CamsImportToDOTTime2011.dtsx" finished: Failure.
The program '[11992] CamsImportToDOTTime2011.dtsx: DTS' has exited with code 0 (0x0).
March 16, 2011 at 6:00 pm
So I changed the protection level of the package to "EncryptAllWithPassword" and entered a password and it ran successfully in BIDS. But of course failed with the same error when I tried to run the job manually or from my application.
March 16, 2011 at 9:23 pm
You need to provide the password when running the package from the command line.
From MSDN article "How To Run a Package" http://msdn.microsoft.com/en-us/library/ms138023.aspx:
If the package encryption level is EncryptSensitiveWithPassword or EncryptAllWithPassword, use the Decrypt option to provide the password. If you do not include a password, dtexec will prompt you for the password.
See the /De[crypt] switch on the util documentation: http://msdn.microsoft.com/en-us/library/ms162810.aspx
If you use the "Sql Server Integration Services" step type in SQL Server Agent it iwll prompt you for the password which is a nice feature because it will obfuscate it for you as opposed to just using a CmdExec step type where the package password will appear in plain text.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 17, 2011 at 10:47 am
I added the decrypt option but it doesnt seem to save it because I close it, run it, it fails, I go back in and it's not there. Also when I didn't have it in I never was prompted for the password.
When I save it in BIDS do I have to do anything else to update it in SQL Server?
March 17, 2011 at 10:57 am
Are you now able to successfully execute the package and get desired results from BIDS?
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 17, 2011 at 11:23 am
Yes it runs error-free in BIDS.
March 17, 2011 at 11:33 am
miquel deblanco (3/17/2011)
Yes it runs error-free in BIDS.
Thanks, I just wanted to confirm where we stood on that at this point.
Recap
Can you get the package to run using the dtutil tool for SSIS?
The account in SQL Server - is it mapped to a Domain account?
What Roles/Groups does this account currently belong to in SQL Server?
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 17, 2011 at 12:04 pm
I didnt think I could run a package using dtutil. It looks like it's just for copying/moving etc. on the local server? Just so you know I'm working from a client machine, not on the server and don't currently have access to it's file system.
I assume you mean the "login failed for" account thats on the other server. It is not a domain account. The server roles are sysadmin and serveradmin.
Thanks again.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply