March 21, 2014 at 3:12 am
Hi all i have been following this forum since very long time and it is really helpful to learn and improve. This is my first question here
I have an ETL which connects to a production server to extract data, i am using a service account in the connection string(hard coding it) package works fine. but when orther users tries to open it the package fails. The other main issue is, the connection string is used in many tasks of the package and when different user tries to open the package service account credentials are not being validated and when these validations fail for 3 times, the production server is locking the service account, which is a big problem.
how can i overcome this, instead of using hard coded credentials i can use a configuration table to the get these details, in addintion to this do i need to consider anything else to not face the above issue? your help will be appreciated
March 21, 2014 at 3:22 am
What is the protection level of the package?
Generally you would store connection information indeed in a configuration table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2014 at 3:30 am
Koen Verbeeck (3/21/2014)
What is the protection level of the package?Generally you would store connection information indeed in a configuration table.
Hi Koen,
Protection level is : EncryptSensitiveWithUserKey
will this make any difference? sorry but i am a bit new to SSIS 🙂
March 21, 2014 at 3:32 am
anilraj0309 (3/21/2014)
Koen Verbeeck (3/21/2014)
What is the protection level of the package?Generally you would store connection information indeed in a configuration table.
Hi Koen,
Protection level is : EncryptSensitiveWithUserKey
will this make any difference? sorry but i am a bit new to SSIS 🙂
Yes, basically it says sensitive data within the package (such as connection strings) is encrypted using your account information. This results in you being able to run the package, but other users not.
You can either change to DontSaveSensitive and use configurations, or leave everything hardcoded and change to EncryptSensitiveWithPassword.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2014 at 3:42 am
Yes, basically it says sensitive data within the package (such as connection strings) is encrypted using your account information. This results in you being able to run the package, but other users not.
You can either change to DontSaveSensitive and use configurations, or leave everything hardcoded and change to EncryptSensitiveWithPassword.
Thank you for your inputs, i will try this now.:-)
March 25, 2014 at 12:49 pm
Yeah, Change it to Don'tSaveSensitive and use Config files.
Check this for PackageProtection levels ..
http://technet.microsoft.com/en-us/library/ms141747.aspx
--
SQLBuddy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply