Solved

Question about Windows Authentication with ASP

Posted on 2004-04-07
12
665 Views
Last Modified: 2008-02-01
I am building an ASP application that requires the use of Windows Authentication for permissions in SQL Server 2000.  I have turned off anonymous authentication and set up an ODBC System DSN on the server.  The System DSN is setup for Authentication with the default database "TEST" selected.  

The problem I have is that when I try to run a query in the application it can't find any tables or anything.  

(Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name 'TEST.dbo.test'.

I have verified the query in fact works.  The default database per the application says "master".  (Remembering I set it to "TEST" in ODBC).  Even if it was at master, the query should still work if I specify a different database.  So what is going on here?  I have no evidence of a failed connection.  Example of my code to connect, etc:

Dim conn

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLOLEDB;Integrated Security=SSPI"
conn.Open "DSN=TEST;"

Dim rs
                          
Set rs = CreateObject("ADODB.Recordset")
 rs.Open "Select blah from TEST.dbo.test", conn
                          
 Response.Write rs.Fields("SN")
0
Comment
Question by:jackjeckyl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 2

Expert Comment

by:pheine
ID: 10781541
i guess your sql server should look like this:

Server:
->master
->TEST
---->test
->msdb
->Northwind
etc.

mean that you have the database test and the table/view test...

if so, your sql query should look like this:
"SELECT blah FROM dbo.test"
or:
"SELECT blah FROM test"

in fact you set the default database to TEST so you dont need it, cause you are in the database test... and the dbo is also not necessary.
0
 
LVL 15

Expert Comment

by:deighc
ID: 10781960
I've used Windows Authentication via IIS to authenticate users into SQL Server and never had any problems. But I don't used a DSN - I just put all the info into the connection string.

Something like this:

conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TEST;Data Source=<server name or IP>"

And, as @pheine mentioned, don't prefix your queries with database name or user name. Just use the name of the table you're querying.
0
 

Author Comment

by:jackjeckyl
ID: 10783476
I was prefixing the tables because it was defaulting to the master database.

deighc, I get the following error going DSN-less

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Obviously meaning its back to anonymous.  What other setting changes would I have to make?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:deighc
ID: 10783596
I think the problem is due to the dreaded "delegation" issues when using Integrated authentication. Basically, IIS can't pass on the user credentials to any other machine on the network if the user is authenticated using Windows Integrated. This is a known limitation of the authentication type.

But.... this article from the MS Knowledge Base may help:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q247/9/31.ASP&NoWebContent=1

Seems like a bit of a hack, and it also says it only works in non-Active Directory domains. But maybe it's of use.

0
 

Author Comment

by:jackjeckyl
ID: 10783805

I returned some system tables using my original setup, and it is clear that the userID is NOT from Windows Authentication.  It is still using the IIS user.  What am I missing??
0
 
LVL 15

Expert Comment

by:deighc
ID: 10783927
What I should've asked in my previous post is, is SQL Server on a different machine to IIS? That's when you have the delegation problem.

This question on EE describes the same problem as yours (read it all):

http://www.experts-exchange.com/Databases/Q_20800659.html

As I said, IIS is unable to pass the authentication information on to other machines in the domain. Your options in regular ASP (ie. not .NET) are to have SQL Server and IIS installed on the same machine or explore the workaround described in the MS KB article. I think you can also authenticate via the Active Directory if you're using it, but I don't know anything about that.
0
 

Author Comment

by:jackjeckyl
ID: 10784020

Yes, they are on different machines.
0
 
LVL 15

Expert Comment

by:deighc
ID: 10784070
> Yes, they are on different machines

Then you'll need to re-think things a bit.

In a nutshell, the delegation BS is the biggest downside to Integrated authentication. I've never had to work around it with SQL Server (IIS and SQL Server have always been on the same machine) but you wouldn't believe the hoops you have to jump thru to make IIS talk to an Exchange Server on the network....

As far as I know .NET has alot more flexibility when it comes to authentication.
0
 

Author Comment

by:jackjeckyl
ID: 10785683
I can't even get my own ID to work let alone others.  
0
 

Author Comment

by:jackjeckyl
ID: 10791181
OK, I figured out the problem.  The actual folder on the IIS server that has the application must have the security changed.  Instead of allowing everyone, you have to specifically state which users can access the application.  I'm imagining this forces IIS to determine the user instead of defaulting to anonymous and then sends that to SQL Server.  All I know is it works.
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 10820435
Closed, 250 points refunded.
Netminder
Site Admin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question