Solved

Question about Windows Authentication with ASP

Posted on 2004-04-07
12
649 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now