SSIS Package permissions??

Luis Simões

Hi,

I had developed an ASP.NET application that runs SSIS packages, everything went well until i got the need to run a package that updates an Analysis Services Cube.

My package connection string to the cube looks like this:

"Data Source=<server>;Initial Catalog=<database>;Provider=MSOLAP.3;Integrated Security=SSPI;"

So my .net website has impersonation=true and my IIS denies anonymous and has integrated security also true.

I have packages that create files and those files are created correctly so permissions are passing just fine. The problem has arrived when i tried to process the cube. I got the following error:

"Either the user, NT AUTHORITY\NETWORK SERVICE, does not have access to the <database> database, or the database does not exist. "

So if i have Integrated Security in all places why does it uses Network Service

Best Regards,

Luis Simoes

Phil Brammer


Because doesn't IIS run as NETWORK SERVICE

Luis Simoes

Yes, but i have impersonation on, should the user not be impersonated to the system So i would be processing the cube like myself instead of IIS user

How can i do this then

Best Regards,

MatthewRoche

Luis Simoes wrote:

I have packages that create files and those files are created correctly so permissions are passing just fine. The problem has arrived when i tried to process the cube. I got the following error:

Just a quick question to see if I can help narrow down this problem: What are the NTFS permissions on the folder where the files are being created If the NT AUTHORITY\NETWORK SERVICE account has the requisite permissions, it can certainly create files.

I'm just concerned that your conclusion "so permissions are passing just fine" is not really backed up by the information in your post. Can you please explicitly verify that the packages that create files are running under a different user context

Luis Simoes

Ok, so lets assume the priviledges are not passing the right way...

Let me explain my ASP.NET code...

I have a few DTS's to run.. and i run them in a newly created thread so it runs out of process.

For that new thread to work with the same user is do something like this:

Before I create the new thread i get the WindowsIdentity like this:

wi is a global variable of type System.Security.Principal.WindowsIdentity

Method responsible for running the packages in a new thread

...

wi = System.Security.Principal.WindowsIdentity.GetCurrent()

_processingThread = New Thread(Addressof Execute)

_processingThread.Start()

...

Before i execute the package i set the thread context like this:

Execute Method for the Package

Try

Dim newPrim as New System.Security.Principal.WindowsPrincipal(wi)

System.Threading.Thread.CurrentPrincipal = newPrim

Dim wicont as System.Security.Principal.WindowsImpersonationContext = wi.Impersonate()

...

Execution of the package

Catch...

....

Finally

wicont.Undo()

Isn't this the right way for passing on the current security settings to the thread

Best Regards

MatthewRoche

I honestly do not know if this is the correct way to do this in this context.

Can you please check to verify if it is working for the file-creating packages

Luis Simoes

Ok i will check that. If it isn't what should be the way to go

Best Regards,

MatthewRoche

As I see it, there are two paths to go down from here:

1. If the packages creating files are also executing as NETWORK SERVICE, then your impersonation code is not working anywhere, and you need to figure out the correct way to impersonate. Using these packages for testing will likely be simpler than using SSAS for testing, since there are fewer and better known (to me, anyway) variables. I'd look here for a starting point: http://msdn2.microsoft.com/en-us/library/ms998351.aspx
2. If the packages creating files are NOT executing as NETWORK SERVICE, but are instead impersonated correctly, it would suggest that there is a difference or problem with SSAS. I know very little about SSAS security, so hopefully someone else could step up to the bar at this point.

Please let us know how this goes!

Luis Simoes

Ok it seems that i was wrong.... the user isn't really being impersonated...

How do i get that knowing that i am using a new thread... does the new thread gets the context of the primary one

Do i have to set like i did Whats the way to get these running ok

Best Regards,

MatthewRoche

Did you look at the "How To: Use Impersonation and Delegation in ASP.NET 2.0" link I sent in my last reply

Luis Simoes

Ok i think the problem only exists while running packages...

If i create a file directly from ASP.NET it would create the file correctly...

In Event Viewer the DTS is called by the correct user:

Event Type: Information
Event Source: SQLISPackage
Event Category: None
Event ID: 12288
Date: 23-05-2007
Time: 16:58:05
User: WORLDVISION\wv000031
Computer: WVISION
Description:
Package "CCUBE000D_DTS_S1" started.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

So why the hell inside the dts it uses NETWORK USER

Best Regards

Luis Simoes

MatthewRoche

That's an excellent question. Unfortunately, this is behavior I've not seen before, so I'm going to bow out and see if someone else has a suggestion.

Luis Simoes

Can it by because of new thread

Regards

MatthewRoche

Luis Simoes wrote:

Can it by because of new thread Regards

Not meaning to beat a dead horse here, but have you read the link I sent earlier

MatthewRoche

This is another resource that seems to target the error you're receiving as well:

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx