March 6, 2012 at 9:45 am
Guys,
I have normally worked by myself and when it comes to protectio level for the packages I have opted for Encrypt sensitive with User Key.
However when I open someone elses package with that level I can see the little old padlock on the package.
What level of Protection would you recomend for packages to be developed by a team?
Any links to team work best practice within SSIS?
Many Thanks.
M
March 6, 2012 at 10:58 am
Ells (3/6/2012)
Guys,I have normally worked by myself and when it comes to protectio level for the packages I have opted for Encrypt sensitive with User Key.
However when I open someone elses package with that level I can see the little old padlock on the package.
What level of Protection would you recomend for packages to be developed by a team?
Any links to team work best practice within SSIS?
Many Thanks.
M
I would recommend "DontSaveSensitive", which would cause all sensitive information (mostly passwords) not to be saved. At the same time, other developers won't have any hoops to jump through in order to execute the package. Each developer could then use local config files to set variable values, etc.
March 6, 2012 at 12:44 pm
Have been looking into this a bit more.
There appears to be two components to the security.
1. The ability to edit the package
2. The security of passwords
I already use config files for connections but going to SQL server they use windows authentication. It may be that all passwords go to XML config files and we go to dont save sensitive.
Not sure I am happy with this but it does mean that the security of the server will defend the passwords to the other databases.
I am assuming that the protection level does not affect connections that use windows authentication. So ok for SQL Server but will affect Oracle passwords as they are rewuired?
Many thanks
Mark
March 6, 2012 at 2:14 pm
Ells (3/6/2012)
Have been looking into this a bit more.There appears to be two components to the security.
1. The ability to edit the package
2. The security of passwords
I already use config files for connections but going to SQL server they use windows authentication. It may be that all passwords go to XML config files and we go to dont save sensitive.
Not sure I am happy with this but it does mean that the security of the server will defend the passwords to the other databases.
I am assuming that the protection level does not affect connections that use windows authentication. So ok for SQL Server but will affect Oracle passwords as they are rewuired?
Many thanks
Mark
Yes, it would affect the oracle passwords. One possible way to deal with this, is to use variables as the source for connection strings. That way you can populate the Oracle connection string variable with some default (read only) credentials that anyone can use. Or you could use SQL Server as the configuration repository and everybody can connect to that for the oracle credentials.
March 7, 2012 at 12:26 am
Or just store the username and password for the Oracle connection inside the configs.
And use DontSaveSensitive, it gives the least amount of issues.
(or just get rid of Oracle :-D)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 7, 2012 at 1:17 am
Is there anything else that is affected by dont save sensitive?
Like the SQL Server connection string?
By default most of my packages use config files for Server \ Database Names for all types opf servers \ DBs. In addition I have taken the Oracle userid \ password out of the package and into XML config file. Just wanted to know if anything else is going to surprise me when I change protection level.
Cheers
M
March 7, 2012 at 1:22 am
This page explains what is sensitive and what not:
Setting the Protection Level of Packages
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 7, 2012 at 1:37 am
Kind of
"Any variable that is marked as sensitive. The marking of variables is controlled by Integration Services.
Whether Integration Services considers a property sensitive depends on whether the developer of the Integration Services component, such as a connection manager or task, has designated the property as sensitive. Users cannot add properties to, nor can they remove properties from, the list of properties that are considered sensitive."
I am guessing that only a subset of the System variables would be considered sensitive. I cant see that SSIS would grade User variables to see if they may contain sensitive info. Although if they were used to assign a value to the expression of a connection string then you know they may contain sensitive info. Will look again at the connection details.
Cheers
M
🙂
March 7, 2012 at 4:20 am
Each package protection level change to "DontSaveSensitive". And Use the variable to extract connection string information in connection manager. or use package configuration which stored connection string in SQL Server DB
March 7, 2012 at 4:42 am
Ells (3/7/2012)
Kind of"Any variable that is marked as sensitive. The marking of variables is controlled by Integration Services.
Whether Integration Services considers a property sensitive depends on whether the developer of the Integration Services component, such as a connection manager or task, has designated the property as sensitive. Users cannot add properties to, nor can they remove properties from, the list of properties that are considered sensitive."
I am guessing that only a subset of the System variables would be considered sensitive. I cant see that SSIS would grade User variables to see if they may contain sensitive info. Although if they were used to assign a value to the expression of a connection string then you know they may contain sensitive info. Will look again at the connection details.
Cheers
M
🙂
The meaning of what "sensitive" really is may be a little obfuscated. What it boils down to is that when you develop a component for SSIS, you have the ability to mark certain properties as sensitive. That is done at the component's source code level though, so you (as the user of the component) do not have any control or visibility into what is sensitive or not. In most cases it is only used for connection passwords.
SSIS does not scan/interrogate user defined variables to remove sensitive information, so if you use variables for connection strings (assigning a connection manager's connection string property to the variable through an expression) the passwords will be maintained as they are in the variable. "DontSaveSensitive" will not cause these passwords to be removed from the variables.
Hope that makes sense...
March 7, 2012 at 4:51 am
Excellent, very descriptive response
Many thanks
Mark
March 7, 2012 at 6:59 pm
I am assuming that the protection level does not affect connections that use windows authentication. So ok for SQL Server but will affect Oracle passwords as they are rewuired?
You can configure Oracle to use windows connections (called 'externally identified user' in Oracle speak)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply