Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 login faild in job

Author  Topic 

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-02-15 : 04:41:23
hi,

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'.....................

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 04:51:53
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.

see

http://blogs.msdn.com/b/runeetv/archive/2009/12/22/ssis-package-using-sql-authentication-and-dontsavesensitive-as-protectionlevel.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-02-15 : 05:51:38
is any other solution without configurable item?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 06:01:33
quote:
Originally posted by inbs

is any other solution without configurable item?


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/

Go to Top of Page

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-02-15 : 07:59:25
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.

what do you think?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 08:08:52
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/

Go to Top of Page
   

- Advertisement -