I'm trying to execute a very simple DTS package from ASP. The package simply spits the contents of one table to a text file located on a Windows 2003 server.

My ASP source is almost exactly as per Microsoft's example here: http://support.microsoft.com/default...b;en-us;252987

I have been unable to get it to work using a trusted connection, even though I log in to SQL Server using Enterprise Manager and a trust connection. The error I get when I try looks like this:

Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

/apps/dnc/exnum.asp, line 21

Why it's using anonymous here puzzles me a little since when I do a: Response.Write Request.ServerVariables("AUTH_USER")

I get: ERGWEST\patrick which is my user name and domain. Why wouldn't it use that? All of our servers and PCs are on active directory, so I can't figure out why it won't work. That's only part 1 of my problem though. By specifying a user/password and creating a local SQL login I am able to connect to SQL server and run my DTS package. However, now I get a new error that I see in the DTS package log. (The webpage just says the package failed). In the log I get this:

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Error opening datafile: Access is denied.

Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

I did a Profiler trace on Security events and it looks like the IWAN account is involved as well. In the end I can't get the thing to run though... can anyone help me out?

Ideally I would go the webpage, gain access to the SQL Server using my trusted connection/active domain name, then execute the DTS package also with my same credentials and write the text file out to the networked server ALSO with my credentials... I don't have to do it that way but it seems like that's the way I should be able to make it work.

Patrick

If you don't turn off anonymous authentication on the virtual folder then anonymous will be used. Alternatively make the iis account a member of a group that is allowed to run the package.



--

Joe

Anonymous authentication is turned off... I've done some more testing and found it to be a problem authenticating across the network. On a test IIS server that is also running SQL Server I authenticate correctly, using the same code. It's when IIS and SQL are on different servers, and IIS has to authenticate to SQL across the network that it doesn't work.

I think that makes sense. AFAIK, the account won't be able to "hop" from machine to machine, unless you use Delegation.

If I am not mistaken, you'll need to check the "Account is Trusted for Delegation" option on the properties dialog for the domain user account that you are using to run the package.

You may also need to enable Kerberos / Delegation in IIS, but I am not 100% sure about that.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Interesting. I've taken a look into this and it might help. As of right now I don't believe I can set things up properly because the IIS server in question is currently running on our primary domain controller. We're going to move it to it's own server. Am I right in assuming that I want to setup delegation for the account IIS is running under?

I found this page on MS which looks like it covers the details of what you've mentioned Imar:

http://msdn.microsoft.com/library/de...SecNetHT05.asp

Well, it all depends on how you configure IIS.

If you allow anonymous access, IIS will run under the IUSR account. This account then needs permissions to access files etc on the local computer. If this account also needs to access network resources, you'll need to do some extra work (since the IUSR is a local account, it can't access network resources). You'll either have to give the IUSR account an explicit password and then create a mirrored account (IUSR_MachineName with the exact same password, where MachineName is the name of IIS) on the database server.

This way, when IIS comes knocking on the SQL box, it says: "Hi, I am IUSR_IIS". Since this account also exists as a local account on the SQL box, everything will work as expected.

The second alternative is to change the anonymous account for IIS to a low privileged domain account, like yourDomain\IISAnon. This account should then be granted access to network resources.

The scenario above would be the first things I'd look into. Maybe this is the cause of your problems.

If you're using Integrated Security, IIS will impersonate you locally. That is, you access IIS as ERGWEST\patrick and any local access checks are done with that account.
If IIS needs to access network resources (i.e. the SQL box) it will impersonate you. That is, it knocks on SQL's door and says: "Hi, I am ERGWEST\patrick".

This impersonation is limited to one hop, That is, machine A can go to machine B, but you can't reach machine C. To make that possible, you'll need delegation / Kerberos. The article you posted provides a lot of useful information about this subject. It uses ASP.NET as application server, but most of the ideas are pretty much the same for classic ASP.

You may also be interested in this: Flowing the Original Caller to the Database at http://msdn.microsoft.com/library/de...OriginalCaller

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

First, thanks for all the help. Second, I've got authentication working properly! What I ended up doing was going into the Computers list for Active Directory Users & Computers, then right clicking properties on both the IIS and SQL computers, and then putting a check in the box marked 'Trust Computer for delegation'. Now, this was a bit of a guess because I had IIS running under the local system account, and wasn't able to set delegation for the SYSTEM account. I think the end result is the same though, and it works. At least authenticating one hop... I'm going to have to go two to write the output of the DTS package to a different server; if I hit a wall I'll be back here else assume it all worked out. Thanks again Joe and especially Imar!

I think I'm really close to being all the way home here... but not quite yet. First, the problem is that my DTS package is still failing, but now it's at least attempting to run. It does run without error when I execute through Enterprise Manager. I've setup a trace using SQL Profiler watching all security events and this is where I've got the info that comes next. I'm authenticating through from IIS to SQL with ERGWEST\patrick. The package starts running and does it's thing -- through the Profiler I see that the Application Name is Windows 2000 Operating System, and then the NT user is me, login name is ERGWEST\patrick. This repeats for about 10 lines as the package executes until the Application Name changes to DTS Import/Export Wizard and the user names changes to IWAM_ENVWDB1 and ENVWDB/IWAM_ENVWDB1 respectively. At this point the trace message is 'Login failed for user ENVWDB/IWAM_ENVWDB1'. The same error is viewable in the package log.

Now, I understand that IWAM is the account IIS is using to run a process on the SQL server right? I guess that IWAM account is not trusted because it's not a domain account. Unfortunately I can't figure out how to create that account on SQL Server, though I can create ERGWEST\IWAM_ENVWDB1. And if I create it like IWAM_ENVWDB1 I don't know what to put for a password since that account was setup and created by IIS, not me. Am I making sense here? It's possible that I don't even need to create an account of SQL Server at all for this login, but I can't think where else I'd be getting the login failed message if not from there -- the Import/Export part of the DTS is writing a text file back to IIS, so that's the other possible source of a failed login I suppose, though it seems unlikely it would happen there.

Yeah, you're making sense.

The IWAM account is used by IIS for "out of process" operations. It's usually used when you have defined a Web site in High isolation mode.

I think the solution you're facing now can be solved with the mirrored account I talked about earlier. Go into User Management for your computer, find IWAM and then give it a strong password. Create an account with the exact same name (IWAM_ENVWDB1) on the SQL Server and give it the same password.
Now, both machines have the same, local account so the authentication should work.

In your Inetpub\adminscripts folder there should be a script that allows you to undo this operation (actually, the file shouldn't be there, as it is recommended to remove the adminscripts ;)).

You could also change the IWAM account to a domain account. One of the ways to do that is to search through the IIS MetaBase and replace all the IWAM occurrences. You'll need a tool like MetaEdit for that.

Check out this article for more info about the IWAM account:
http://support.microsoft.com/default...b;EN-US;296851

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Ok, it's working but I had to make some compromises to get it there. In the SQL connection within my DTS package I had to change it from Windows Authentication to SQL Authentication, and I specified a local SQL account there. For whatever reason it just wouldn't authenticate IWAM even when I changed the IWAM password and created the local account there. I suspect it's because for whatever reason it's ENVWDB/IWAM instead of just IWAM. Regardless, it's working and I should be able to repeat it with other packages.

A lot of work for something that should be a lot easier I think -- I mean, I'm using 3 Windows 2000 machines, all using Active Directory, to execute a DTS package authenticating against Windows all the way through should not have been so difficult. I'm going to try all of this off a Windows 2003 Server running IIS6 and see if it works any cleaner.

Cheers Imar,

Patrick