Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Question about Windows Authentication with ASP

Posted on 2004-04-07
12
Medium Priority
?
671 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

972 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