• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2295
  • Last Modified:

SSIS Security

I have designed an SSIS package. I am the only one who is able to execute the package not other users. I have saved my SSIS package as a file.
I have tried ProtectionLevel: Encrypt sensitivewithPassword and Encrypt sensitive with user key. Both didn't help.
0
MayankSql
Asked:
MayankSql
  • 5
  • 4
1 Solution
 
jantje_20Commented:
EncryptsensitivewithPassword  should do the trick, this saves password information of the connections you use in the package encrypted with the password you supply.
Possibly you "lost" password information in the package while switching between different protectionlevels.
Try setting the password for the connections again, set the protectionlevel to EncryptsensitivewithPassword  and enter the password again.
Other things you could take a look at:
-Are you using registry-based SSIS package configurations?
-File access fails because the user does not have the required permissions
-Are you using integrated security?

If none of the things above solves your problem, try posting the error message you get while running the package
0
 
MayankSqlAuthor Commented:
I have tried "EncryptsensitivewithPassword ", that just prompts user to enter password. So only I can logon but not other user to execute SSIS package.
0
 
MayankSqlAuthor Commented:
-File access fails because the user does not have the required permissions

Can you tell me more about this point? I have a feeling might be because of this reason. My assumptions are based on following:
1) The file location of this package is the location of the folder created at default folder location. C:\Documents and settings\user\MYNAME
2)When I am trying to save this file as Protection Level Storage:Server Storage I get following error:

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability." This error occurs when saving to Xml. (MY_Package)


Will changing the location of .dtsx packages help?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jantje_20Commented:
ServerStorage can only be used when saving to SQL server. Am I right when you want users to execute your package without them giving the chances to edit the package?
In this case I would share the password but make the pachage read-only for all users, except you. You can do this by setting the windows security settings for other users to read & execute. If you do not have any experience changing windows security settings I would recommend you to contact you system admin. Some information about security on Windows XP: http://support.microsoft.com/kb/308419
Hope this helped.
0
 
MayankSqlAuthor Commented:
But my problem is, I want other users to execute the package. My account would be suspended cause I am doing this for a client. So how do I make other account make my package execute?
0
 
jantje_20Commented:
Ok, now I fully understand youir problem. Save the package in a shared map on the pc you are working with, or preferably on a shared location on the network. Save the package with EncryptsensitivewithPassword and make sure you enter a password in the PackagePassword field by clicking the button with the three dots, it's right just one row above the ProtectionLevel setting. This is the password other users have to enter when starting the package. Share the password with the other users.
0
 
MayankSqlAuthor Commented:
i am getting Error:
Cannot Communicate with the debug host process. Failed to obtain child process active object.
0
 
jantje_20Commented:
You have to give me some more information. Is the user running the package on the same computer, or using another computer? Have you tried running it on another computer yourself? Have you tried installing the latest servicepack? What connections are you using in the package?
Check this two things:
- Is visual studio properly installed? Maybe DTSdebughost.exe is corrupted, or not available for other users on the computer
- Check if connections to the database are using Integrated Windows security or a database login. Windows security might fail for another user executing the package
0
 
MayankSqlAuthor Commented:
Thanks a lot for your time and Patience:
The client is in WA so I do remote desktop. They have Service pack 1.
When I go to Management studio, I can execute the package after I connect to Integration services through object explorer.
When I try to add the package to a job I am not able to execute the pacakage. Also When I go into Business intelligence studio, I can create a package but not run it from there.

The client had two logins say Old_A and New_B. Those packages were created through Old_A login.
He says that he swapped the logins which I am not sure what he means because I am assuming he
deleted old login Old_A

Sql Server is set to Windows Authentication mode.


The package should run fine in anyother computer, because it was running fine before the stupid guy did break the logins. He deleted or did something to old_A login.
But the thing is even after creating a completely new package with New_B login, I am not able to execute the pacakge from management studion and I get the message

"Cannot Communicate with the debug host process. Failed to obtain child process active object."
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now