DB Native versus Win Authentication with SSIS Packages and Deployment
-
Hi there,
I have done a migration of Informatica PowerCenter ETL project to MS SSIS project using Visual Studio 22
Everything is now working fine when I run the packages one by one within the VStudio on my laptop.
The next step would be to deploy my project to the Sql Server SSIS Catalog.
I know the basics from my history few years back: Create Folder and project to the catalog for my (customer's) packages and then start creating the JOB(s) and Steps within of the packages....
But I am very much uncertain of the best practices with the DB Login arrangements between my packages and the job steps.
I did read some chapters of the 'Stairway to Integration Services' here and there was many useful things to know but I did not get a kind of clear picture of the whole "path" I should follow
Overall info
Customer has a Sql 2019 Sql Server / Integration services running. I have tried to set the TargetServerVersion to 2019 but I get errors...this is a different (but important) topic though so I leave it for now
Some errors also came when we test tried the deployment to this server with the guys (yet another Vendor) that administer it
The obvious error was that I had the default setting EncryptSensitiveWithUserKey
I changed that to EncryptSensitiveWithPassword within the project and packages and also set the DelayValidation to True (the firewall settings are not ok yet either from the SSIS/Sql Agent Sql Server). We have not yet tested whether the deployment would run successfully after these changes
There will be a new dedicated Sql 2022 SqlServer for this project that will contain the staging and DW databases some day in the near future.
Well, now to the point
What kind of connections I have at the moment in my packages?
- Native Sql Server connections (project and package level)
- Windows authenticated Sql Server Connections (project and package level)
- Native Oracle connection (Project level)
- Flat file connections (Package level)
What would be the best way to implement the sensitive data (login passwords)?
I put my thoughts / questions that trouble me in here
Regarding the Windows authenticated connections:
- The Sql Agent service account could be configured to run with a domain account that is then given the permissions to all necessary databases
- A proxy with needed credentials could be created and used with the Job Steps
The native ones could be configured / given within the job steps but this is kind of laborious thing to do to many packages
I would create an Environment and reference it from the SSIS project in the Catalog
Then what to do with the package/project configurations in the VStudio?
To allow the usage of Environments wisely (mapping the passwords)
- I think every (native) login password would have to be parametrized...
- What about the ConnectionString as well...password is not shown here
- ProtectionLevel..? DontSaveSensitive or EncryptSensitiveWithPassword
I know this was quite long but I would really appreciate advice with this to make it right immediately. The customer site is a difficult multi-vendor environment and I should state the facts for them well from the the very beginning. it is very time consuming to have anything changed later...
Ville
- This topic was modified 8 months, 3 weeks ago by WilburSmith.
-
A quick response to some of your questions:
- All environment-specific items should be parameterised.
- Don't Save Sensitive, along with sensitive parameters whose values are passed from SSISDB environments, is the way to go for protection levels and passwords.
- Connection strings (not sensitive) and passwords (sensitive) should be configured/mapped separately.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks
DontSaveSensitive - If I set this, do I need to give the passwords every time I load the package to VStudio and run it?
EncryptSensitiveWithPassword - how would this show in SSIS Catalog / JOBs?
Ville
-
I am not a SSIS expert but with regards to authentication methods:
- Windows Authentication will be more secure especially if you can use Managed Service Accounts. The downside can be things like Kerberos double hop problems which may require a lot of time with your network team to resolve.
- Unless SQL Server has a CA certificate installed, SQL Server authentication can be subject to MITM (man-in-the-middle) attacks to obtain the password. Also, even if it is encrypted, the password has to be held somewhere on the client.
-
as Phil said - don't save sensitive parameters. e.g. set packages to "DontSaveSensitive"
while this has a downside in that sensitive parameters are not saved, there are ways around it so that the packages work both within the development environment (e.g. Visual Studio) and the SSIS Catalog
this is done in a somewhat weird way.
1 - Setup Project Parameters - flag them as Sensitive (in SSIS Catalog EVERYTHING should be Project Parameters).
2- setup Package parameters - only for the Sensitive parameters. set these as NOT sensitive.
on the package use either Project parameters or Package variables on the required connection strings/passwords as needed.
then as a first step on the package, execute a c# script step that will retrieve the sensitive parameter value, and if set (which should not be in dev within Visual Studio), use it to populate the corresponding Package variable.
so within DEV the Package variable would contain sensitive info visible to the user - but being dev this should not be an issue (as developers should NOT be going to prod when developing)
c# code sample below
try
{
string value = Dts.Variables["$Project::Source_name_ConnectionString"].GetSensitiveValue().ToString();
if (!string.IsNullOrEmpty(value))
{
Dts.Variables["User::Source_name_ConnectionString"].Value = value;
}
}
catch (Exception ex)
{
Dts.Log(ex.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
throw ex;
}
Dts.TaskResult = (int)ScriptResults.Success; -
Hi,
"1 - Setup Project Parameters - flag them as Sensitive (in SSIS Catalog EVERYTHING should be Project Parameters).
2- setup Package parameters - only for the Sensitive parameters. set these as NOT sensitive."
So...should I upgrade all connections to project connections or what am I missing here? Maybe I am mixing a bit the relationships between connections and parameters...can there be a project parameter referring to a package connection string etc...
I can do that but I kind of have not done it so far to avoid filling the Connection Manger window of all packages. I have only set those connections as project connections that are in use with multiple packages. Actually I would have expected one to be able to drag the project connections only to packages where they are actually used - this I don't think I can do...
Maybe your sophisticated solution with C# (thanks) etc is not my way to go in this project / customer though (long story)...
==>
I'd just like to parametrizise the connection strings and passwords (with the confusion from above here about project or package parameters:))
and then
EncryptSensitiveWithPassword - how would this show in SSIS Catalog / JOBs?
-
its a matter of choice - but I personally do not use project connections, only project parameters.
Connections are all defined within each package that needs them - and the connection string is what is supplied by the project parameters (or in some cases from config tables on a database)
and you got it the other way around on your reply "can there be a project parameter referring to a package connection string" - a connection (being it package or project) can reference a Project(or package) parameter - not the other way around.
regarding your client - even if they are "unsavvy" with C#, this bit of code is just that - nothing fancy - and only required at the very start of the package.
but I do have to say that if a customer is using SSIS and does not know/is unwilling to use C# within the packages they maybe they should be changing to a different ETL tool - plenty of things in SSIS can only be done "the right way" using C# - not using means either rather slow processes, hard to debug, or huge maintenance of packages (and still slow).
regarding "EncryptSensitiveWithPassword - how would this show in SSIS Catalog / JOBs?" - I'll leave that for you to test or someone else that does use it like that to reply to you. I don't know, neither can't test it at the moment to see how it would work.
-
Yep,
My problem too...I can not test Agent Jobs/Steps on my own. That is why I am trying to figure out as much as possible "in theory" before we hit that phase...
-
FWIW - it isn't necessary to set or change the protection level because deploying to the catalog is going to change it anyways.
See here: https://andyleonard.blog/2018/04/deploying-to-the-ssis-catalog-changes-the-protection-level/
Once you deploy the project to the catalog you can configure the project and define the project and package parameters. These will be the default values used by the agent or when called from SSMS.
I do not ever change the connection in a project parameter or package variable. The only thing that will be changed here is either the server name, user name or password. These values are exposed as separate properties and can be changed as needed instead of actually modifying the connection string.
I will use package-level connections only when I have packages that need to be able to change the server name (for example, in a for each loop across multiple servers) or if the connection cannot be done at a project level.
WilburSmith wrote:Yep,
My problem too...I can not test Agent Jobs/Steps on my own. That is why I am trying to figure out as much as possible "in theory" before we hit that phase...
Download SQL Server Developer Edition - install integration services - create a catalog locally and you can test deployments, and setting up agent jobs.
The concern of how to save the project in Visual Studio - save it however works best for your team. It will be change to ServerStorage when deployed to the catalog anyways.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi
Ok thanks
I have now done as follows:
- I have both package and project connections (like I wrote earlier in this thread)
- All connection strings are set via project parameters /expression
- All passwords are set via project parameters /expression
- Protection level is EncryptSensitiveWithPassword - I hope this will not cause any problems in the JOBs
Now I will set the TargetServerVersion to Sql 2019 and we try the redeploy. First I of course backup my version 22 project
Still need to wait for firewall openings (from the SSIS / Sql Agent server) before we can test anything
After I set the 2019, I can not run the packages from my VStudio 22 anymore - is this the way it is or do I miss something again?
Ville
-
Hi
Now I read the stories behind the links and I understood that EncryptSensitiveWithPassword will not cause any trouble with the JOBs and also that admins can export the packages and view the sensitive data - but this is not a problem...
"Do not save sensitive (DontSaveSensitive):
Suppresses the values of sensitive properties in the package when the package is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information."
Same as with EncryptSensitiveWithUserKey...?
This I also didn't know - If I understand correctly, I could use that but still be able to reopen my project in VS and the passwords would still be there....?
-
Hi,
I now have the TEST/PROD environments set as seen below in the picture. Connect Strings and Passwords are all (project) parametrized and then the mappings done in the SSIS Catalogs via Environments.
I tried to Execute one package from the tree seen above (not from the JOBs)
...and chose the Environment variables as the source of the Conn/PW paraneters
==>
I added permissions (read, modify, ...) to myself with the Permissions tab under the environment TEST
What permissions am I missing here?
-
that message is a typical windows authentication double hop issue (which SSIS Catalog does NOT support).
has NOTHING to do with permissions just to be clear.
so... when you execute the package from your local PC SSMS (e.g. ssis catalog, package, run package) SQL grabs your windows credentials, and uses it to start a local instance of dtexec (diff name just to be clear).
these credentials are valid for the current SQL Server instance - but if your package tries to access a remote server, using windows authentication, the credentials are not passed to that server - and instead a local "anonymous logon" is used. This is the error you are getting.
it "may" as well be that you don't have delay validation enabled on your connections - and this "may" mean that the validation is trying to go to a remote server based on the "hardcoded" value on the package from development - if this is the case setting delay validation will likely solve this particular issue.
-
Hi
Well I did set the DelayValidation = True to all connections in VStudio an then redeployed the project - same erros
I don't seem to understand the whole credentials picture here...like I try to show in the picture above
- All ConnectStrings / Passwords are set in the Environment TEST
- The project references that Environment
- The project is then configured to use those environment values
And with the DB's that are connected via Win Authentication use my domain account which is also used when I connect to the SSIS Catalog server when I try to execute a package.
-
let me put it in a different way.
do those packages access any server other than the one where SSIS package is installed and running, and is the connection to those other servers done through windows authentication?
if so that is your issue with YOU starting the package from SSMS or any other means other than a SQL Server Agent job on the SSIS server itself.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply