Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Question about Windows Authentication with ASP

Posted on 2004-04-07
12
Medium Priority
?
669 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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 informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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