i have simple package, that transfer data from One Server To my sql server. i use with datasource (in the ole db source ) with sql user and password. in the package i choose the defualt protectionlevel: EncryptSensitiveWithUserKey
when i run it in visual studio it's run well, but in the job it faild and i got this error:
Description: Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information. End Error Error: 2013-02-15 11:36:22.10 Code: 0xC0202009 Source: Package1 Connection manager "Priority" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'BILogin'.....................
EncryptSensitiveWithUserKey will cause sensitive info including the password to get encrypted based on your userkey (login). so any other login which access the package will not be able to get password stored in it.
the solution is to add password as a configurable item and pass value through it.
Thats the recommended approach. Can I ask why you dont need to use configurations? This will really add flexibility and enables easy package migration over environments
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
i decided not to worke with environments (dev,prod) , because in my company all the time they asked changes and they want it immedtitaly. so to work with sevrial env. make the work complicated.
configurations doesnt need multiple environments. You just need to store the values inside separate config file (or table). You can use this even when you've single environment I was just telling an additional advantage it will have when you're working over multiple environments Its not a requirement by itself.
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
login faild in job
Posted - 02/15/2013 : 04:41:23
