IIS doesn't retain windows authenication

[mjjks]

I don't know of a quick way of describing it, so please bear with me.

I inherited an ASP web application which uses COM component to connect to an Access database. It uses MS DataLink file for that. It all works ok.

Now, I migrated database to SQL2005, changed DataLink to point to the SQL server and use Windows Authenication security. It tests out successfully, however fails when I run it through webpage with database login error.

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

Login failed for user 'ROTOR\S-IDS456C-131$'.

User in the error above, is server\machine name

I don't get it as in IIS(6.0), in directory security I specified to use Integrated Windows Security and unchecked Ananymous access.

How do I fix it so Windows Authenication is being enforced and passed to DataLink correcly?

Thanks
Steve

 

[mjjks]

Anyone? Any ideas? I need some HELP asap.

Thank you.

 

[Chopstik]

So is your windows ID not being passed to your DataLink correctly? Or is your windows ID not allowed as a user in your SQL database? I'm not sure I understand the exact problem...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[mjjks]

Yes, windows ID is not being passed to DataLink correctly, even Integrated Windows Authenication option is selected.

Should pass my login/password, instead passes domain\machine name and errors out. When editing DataLink and testing, DataLink passes credentials to SQL Server correctly. Hope this makes it a little bit more clear.

Thanks

 

[Chopstik]

What is the code that you're using to do this? Sorry, but I'm not familiar with DataLink so not sure how it is supposed to work...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[mjjks]

There's no code. Once you open UDL (DataLink) file, it gives you a screen where you can select provider, connection method and some more options.

If open with a Notepad, it has this entry:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MySQLDatabase;Data Source=ROTOR\S-IDS456C-131$

They way it works is: ASP page creates component(DLL) and dll uses DataLink as connection string to the SQL server.

Thanks

 

[Chopstik]

Ok, now I have a better understanding. Actually, I use a UDL file in much the same manner you do, just didn't know that it was called a DataLink.

That being said, when you select for it to use Windows NT Integrated security instead of specifying a User ID and Password, it is going to accept the Domain/Machine Name that is passed via the application. If that Domain/Machine Name does not have access rights to your database, it will fail. It *DOES NOT* pass the User ID and Password (which I assume is what you manually enter when you test your UDL connection).

Do you not have a specific User ID set up for your users? If not, it might be necessary to re-think this process.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[mjjks]

Thanks for the reply Chopstik.

While editing UDL, I have a SQL server name and "Windows NT Integrated Security" option selected. When I press the "test" button, it tests out successfully, but when I run application (ASP-->DLL-->UDL), then it errors out with login error.

I think that it's an IIS issue, however I might be wrong.

DLL doesn't do anything fancy. Creates connection and uses UDL for connection string. I just don't get it why login changes.

With UDL pointing to an Access db, there's no problem.

Thanks

 

[Chopstik]

Below is copied from an internet source I found (which follows):

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YourDB;_
Data Source=YOUR_LOCAL_SERVER_NAME

The site is

http://www.drdev.net/article10.htm

Your data source looks like your domain/machine name which shouldn't be. Perhaps there is something else going on?

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[mjjks]

data source is just a SQL server\instance name and it's on different server than IIS.

Yeah, that's what I'm trying to figure out, maybe I'm digging in the wrong place.

Thanks for the ideas.

 

[mjjks]

I solved it. In order to make it work, I had to register DLL as COM+ application in MTS

 

[Chopstik]

Interesting... And thanks for posting your solution. Hopefully others may make use of this in the future.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill