DB Native versus Win Authentication with SSIS Packages and Deployment

Question

Post reply

DB Native versus Win Authentication with SSIS Packages and Deployment

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

WilburSmith Hall of Fame Points: 3225 More actions · Answer 1

Hi

Like I tried to write in my earlier message, yes, several different SqlServers (and one Oracle)

With native users and Win Authentication are bot widely in use

Like said, I am connected to the SSIS Catalog Sql Server as myself (Win Auth) and I the Win Auth sources/targets are also available (read) with my credentials...

When I right click a package from the Catalog project and choose Execute - is it trying to use mycredentials or possiblythe credentials of the Sql Server service...?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

WilburSmith wrote:

When I right click a package from the Catalog project and choose Execute - is it trying to use mycredentials or possiblythe credentials of the Sql Server service...?

You can check the answer for yourself by looking at the All Executions report.

But 'your credentials' is the answer.

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

WilburSmith Hall of Fame Points: 3225 More actions · Answer 3

Jep ok

It says 'Caller' - I guess that is the account it runs then...

Well, the ConnectString states that the connection is Win Auth, so why the ''..ANONYMOUS LOGON' error then...?

frederico_fonseca SSCoach Points: 16155 More actions · Answer 4

and as I said on my prior post - it will not work like that as it is a double hop situation - only way for this execution to work is if you setup a SQL Agent job to run the packages - and even that can have other issues depending on how your server is setup (service account being a local or domain account) and if you use a proxy to run the packages or not.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

WilburSmith wrote:

Well, the ConnectString states that the connection is Win Auth, so why the ''..ANONYMOUS LOGON' error then...?

It's a Kerberos thing. Get yourself a big cup of tea and read this link for more info.

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

WilburSmith Hall of Fame Points: 3225 More actions · Answer 6

Okay thanks

I'll skip this 'Execute package' from the Catalog project then

Service account is domain account

I need to either create SSIS Proxy or then ask that they have the service domain account permitted to the databases in question

frederico_fonseca SSCoach Points: 16155 More actions · Answer 7

in addition to what Phil supplied - which may mislead you into thinking you can set it up to bypass this particular issue.

SSIS catalog DOES NOT allow for double hop - so if you execute on server A (through SSMS on your own PC) and the package needs to run as YOURSELF and connect to SERVER B it won't work unless your package is using SQL Authentication.

see https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver16 near the bottom of page

you may need it setup though if you have a setup like this.

SERVER A - SSIS Server

executes package - reaches to SERVER B

executes Stored Proc on SERVER B that uses a linked server to SERVER C

for the above setup the kerberos setup will be required and it will, with few exceptions, work.

WilburSmith Hall of Fame Points: 3225 More actions · Answer 8

OK

I moved over to JOBs instead now.

I created credentials (me) and a Proxy 'Win_Auth_Proxy' that uses me credentials

The loads that only uses Win Auth (thatis, from STG to DW or DW to DW) work fine with that proxy

Eh...hold it...pretty strange that the same STG DB win auth project connection works with most of the packages but then with two

Connectiuon error

Are the packages somehow corrupted because they also give some error when opened in VS 22 but yet they run OK...?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

That looks like the sort of error you would get if you copied a package containing a project connection from one project to another.

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

WilburSmith Hall of Fame Points: 3225 More actions · Answer 10

No that is not the case...I have copied a package or package objects within one project though but that should be a normal procedure...

I might even recreate those packages but it is a bit painful since I need to first replace my project from the VStudio 22 backup since I can not run them after TargetServerVersion is set to Sql 2019

==>

Exception deserializing the package "Value does not fall within the expected range.".

Argh