Question about Windows Authentication with ASP

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")
jackjeckylAsked:
Who is Participating?
 
NetminderCommented:
Closed, 250 points refunded.
Netminder
Site Admin
0
 
pheineCommented:
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
 
deighcCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
jackjeckylAuthor Commented:
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
 
deighcCommented:
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
 
jackjeckylAuthor Commented:

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
 
deighcCommented:
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
 
jackjeckylAuthor Commented:

Yes, they are on different machines.
0
 
deighcCommented:
> 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
 
jackjeckylAuthor Commented:
I can't even get my own ID to work let alone others.  
0
 
jackjeckylAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.