SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 login faild in job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

inbs
Aged Yak Warrior

835 Posts

Posted - 02/15/2013 :  04:41:23  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/15/2013 :  04:51:53  Show Profile  Reply with Quote
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

835 Posts

Posted - 02/15/2013 :  05:51:38  Show Profile  Reply with Quote
is any other solution without configurable item?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/15/2013 :  06:01:33  Show Profile  Reply with Quote
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

835 Posts

Posted - 02/15/2013 :  07:59:25  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/15/2013 :  08:08:52  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000