Caching Linked Server Queries

Hi,
Is the result of query via Linked Server cached? Or it depends on the data
source type of Linked Server? What about the exeution plan?
Thanks,
Leila
Leila (Leilas@.hotpop.com) writes:
> Is the result of query via Linked Server cached? Or it depends on the data
> source type of Linked Server? What about the exeution plan?
The result is not cached. The exucution plan is cached, just like for
any other query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Caching Linked Server Queries

Hi,
Is the result of query via Linked Server cached? Or it depends on the data
source type of Linked Server? What about the exeution plan?
Thanks,
LeilaLeila (Leilas@.hotpop.com) writes:
> Is the result of query via Linked Server cached? Or it depends on the data
> source type of Linked Server? What about the exeution plan?
The result is not cached. The exucution plan is cached, just like for
any other query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Caching Linked Server Queries

Hi,
Is the result of query via Linked Server cached? Or it depends on the data
source type of Linked Server? What about the exeution plan?
Thanks,
LeilaLeila (Leilas@.hotpop.com) writes:
> Is the result of query via Linked Server cached? Or it depends on the data
> source type of Linked Server? What about the exeution plan?
The result is not cached. The exucution plan is cached, just like for
any other query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Caching Deleted

I cached about 7 reports last night, and they all looked fine and worked
real fast this morning.
However, something happened today and now the caching is gone? What
would delete the cache? Does the cache delete when I deploy new
reports?
I have all of my reports set to use a shared schedule to delete the
cache.
Any suggestions?
Thanks...Check the cache configuration to see when they're set to expire and to
rerun.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John Geddes" <john_g@.alamode.com> wrote in message
news:OC$swB28EHA.2192@.TK2MSFTNGP14.phx.gbl...
>I cached about 7 reports last night, and they all looked fine and worked
>real fast this morning.
> However, something happened today and now the caching is gone? What would
> delete the cache? Does the cache delete when I deploy new reports?
> I have all of my reports set to use a shared schedule to delete the cache.
> Any suggestions?
> Thanks...
>

Cached results and sorting.

We're doing sorting by jumping to the report, and by changing some
parameters, I change the sorting. All the sorting is done by the reporting
services tables, not the MDX queries.
It takes a long time to rerun these reports. All the MDX queries are the
same. Shouldn't these queries, or the data be cached?On report manager open up the report, properties, execution and see if you
are caching the result.
Caching behavior is set on the server.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
> We're doing sorting by jumping to the report, and by changing some
> parameters, I change the sorting. All the sorting is done by the
reporting
> services tables, not the MDX queries.
> It takes a long time to rerun these reports. All the MDX queries are the
> same. Shouldn't these queries, or the data be cached?
>|||I tried that, but it didn't work.
It's has some different parameters, but all the MDX queries are the same.
The parameters make the sorting different.
Is there anyway to tell it to use the same data?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23FRV7118EHA.3828@.TK2MSFTNGP09.phx.gbl...
> On report manager open up the report, properties, execution and see if you
> are caching the result.
> Caching behavior is set on the server.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
> > We're doing sorting by jumping to the report, and by changing some
> > parameters, I change the sorting. All the sorting is done by the
> reporting
> > services tables, not the MDX queries.
> >
> > It takes a long time to rerun these reports. All the MDX queries are
the
> > same. Shouldn't these queries, or the data be cached?
> >
> >
>|||Oh, the different parameters make it decide not to use the cache is what I
guess is happening. It'll be nice when sorting will be part of the product
and not something we have to munge together (version 2 will have sortable
columns). Sorry, I am out of ideas here.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:OIUlP538EHA.4028@.TK2MSFTNGP15.phx.gbl...
>I tried that, but it didn't work.
> It's has some different parameters, but all the MDX queries are the same.
> The parameters make the sorting different.
> Is there anyway to tell it to use the same data?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23FRV7118EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> On report manager open up the report, properties, execution and see if
>> you
>> are caching the result.
>> Caching behavior is set on the server.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
>> news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> > We're doing sorting by jumping to the report, and by changing some
>> > parameters, I change the sorting. All the sorting is done by the
>> reporting
>> > services tables, not the MDX queries.
>> >
>> > It takes a long time to rerun these reports. All the MDX queries are
> the
>> > same. Shouldn't these queries, or the data be cached?
>> >
>> >
>>
>|||Adding
<UsedInQuery>False</UsedInQuery> Didn't help. That only works in a snapshot
execution, but then i can't change parameters from the default.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%239nzGa68EHA.936@.TK2MSFTNGP12.phx.gbl...
> Oh, the different parameters make it decide not to use the cache is what I
> guess is happening. It'll be nice when sorting will be part of the product
> and not something we have to munge together (version 2 will have sortable
> columns). Sorry, I am out of ideas here.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:OIUlP538EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >I tried that, but it didn't work.
> > It's has some different parameters, but all the MDX queries are the
same.
> > The parameters make the sorting different.
> >
> > Is there anyway to tell it to use the same data?
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:%23FRV7118EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> On report manager open up the report, properties, execution and see if
> >> you
> >> are caching the result.
> >>
> >> Caching behavior is set on the server.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> >> news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
> >> > We're doing sorting by jumping to the report, and by changing some
> >> > parameters, I change the sorting. All the sorting is done by the
> >> reporting
> >> > services tables, not the MDX queries.
> >> >
> >> > It takes a long time to rerun these reports. All the MDX queries are
> > the
> >> > same. Shouldn't these queries, or the data be cached?
> >> >
> >> >
> >>
> >>
> >
> >
>

Cached reports REQUIRE that SQL Server used SQL Server authentication (or mixed)?

I am trying to setup cached reports so that one of my larger reports doesn't have to be re-run every time someone wants to view it (the data source only updates every 24 hours).

Anyway I made a new data source, set the report to use that, and in that data source I said to use "SQLexampleUserName" as the stored credentials.

Now when I go to run the report it says: Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.

This is referenced in the MSKB here:

http://support.microsoft.com/default.aspx/kb/555332

Which makes sense, but now my question is: If I want to used a cached report do I HAVE to allow SQL Server Credentials?

I was using Windows Authentication only up to this point.

Yes, you can still use windows credentials for cached reports. To use windows user credentials, in the report properties, on the data sources tab, select 'credentials stored securely in the report server' then enter yorur windows creds, check the use as windows credentials checkbox, then click apply.

Hope that helps,

-Lukasz

|||

I'm afraid I'm still having trouble.

I followed your steps from the report manage web based interface and now I have:

An error has occurred during report processing.
Cannot create a connection to data source 'My-Server'.
Login failed for user 'DOMAIN\testuser'.

To make the changes I went to the Data Sources tab as you said, then selecteed "A Customer Data Source" and the Microsoft SQL Server. My Connection string is:

Data Source= My-Server; Initial Catalog=MyDatabase

Then "credentials stored securely in the report server" and "use as windows credentials when connecting to the data source"

I tried the user name as DOMAIN\user and just user.

Now it may just be that I don't have things configured properly somehow, but the report runs just fine if I use Windows Authentication and don't try to cache the report.

Can I make these adjustments from inside the design studio instead of the web based report manager?

|||

That config seems like it should work, it matches the one I'm used. Couple things to check:

1- Did you add the correct permissions for the test account to actually access the database?

2- Create a local user account, make it an SysAdmin (just to troubleshoot!!!!) and try it that way. I don't use a AD account because that account doesn't need to access any network resources, and have more control over the password reset time, etc. Creating a local account to troubleshoot should also rule out any strange AD issues you might be having.

Don't check the 'impersonate' box (you didn't mention it, so I'm guessing you haven't checked it)

Otherwise, once it's running, you'll want to harden the system by ensuring the account that's cached has the lowest possible permissions needed to execute the reports.

Geof

|||

Thank you, I will check on that.

As for security, what permissions does an account need to run a basic report?

If I was reporting off TestDatabase on the server I just need to grant the account a login on the server and then read (public) access to TestDatabase, is that correct?

|||

Depends on what security you've added to the basic database, but yes, a login, and then public access as described.

Based on your error message, I think you aren't getting past the login stage. There is no need to grant any access rights to any reportserver system databases.

|||

Thanks.

I agree that it appears my test account is having issues as I tried it with my personal account and it works just fine.

I'll review my security permissions and work it out from there..

Cached Reports

Is it possible to cache a version of a report, meaning say I pass a parameter "CacheReport" which is a boolean. Can I only cache this report when this variable is set to true?I don't believe that's currently a settable option. You can set them to
cache with the first user, on a schedule or have it pull from a snapshot
each time.
"comet61" <comet61@.discussions.microsoft.com> wrote in message
news:C6DEDF65-C480-430B-A94C-968107D1AC6C@.microsoft.com...
> Is it possible to cache a version of a report, meaning say I pass a
> parameter "CacheReport" which is a boolean. Can I only cache this report
> when this variable is set to true?

Cached Report and Windows Authentication

I want to use cached reports with Reporting Services 2005. But when I edit the execution properties of a report, an error message answser that I can't use cache because it has a connexion to Analysis Services with Windows Authentication.

I understand why I can't do that, but I still want to do so, because the reports take too much time to load. How can I do that ?

Thanks.

You can configure the datasource in the report to run using stored credentials. In report manager, go the report and then click on the DataSource tab on the left side, you can store the credentials using that UI.|||

Thanks. I tried this and it works, but I don't see better performance...

I thought that I would not see "Generating Report, please wait" any more...

|||

Query the ExecutionLog table in the report server catalog database to see if we are submitting the queries. You can tell by looking at the "rowcount" column. You will also see how much time we are spending in processing and rendering respectively. Processing is the time that it takes for us to create the snapshot, and should be minimized by rendering from the execution cache.

|||

TimeDataRetrieval 645 0
TimeProcessing 4855 347
TimeRendering 23518 1090
Source 1 2
ByteCount 10249 10249
RowCount 183 21

Thank you, it seems that the cache is used. The first column is created the first time when the cache is generated, and the second column is created when I open the report a second time. I still got a RowCount > 0, I don't know why... Isn't there a way to tell the ReportServer the generate a .html file as cache, I think it would be much faster.

But then again, it works, so I thank you for your help.

|||We will automatically cache whatever we think is safe to cache. HTML caching can be problematic because there is some session specific information which is embedded in the HTML, so we cannot simply serve up the same HTML to multiple users.

Cached Report and Windows Authentication

I want to use cached reports with Reporting Services 2005. But when I edit the execution properties of a report, an error message answser that I can't use cache because it has a connexion to Analysis Services with Windows Authentication.

I understand why I can't do that, but I still want to do so, because the reports take too much time to load. How can I do that ?

Thanks.

You can configure the datasource in the report to run using stored credentials. In report manager, go the report and then click on the DataSource tab on the left side, you can store the credentials using that UI.|||

Thanks. I tried this and it works, but I don't see better performance...

I thought that I would not see "Generating Report, please wait" any more...

|||

Query the ExecutionLog table in the report server catalog database to see if we are submitting the queries. You can tell by looking at the "rowcount" column. You will also see how much time we are spending in processing and rendering respectively. Processing is the time that it takes for us to create the snapshot, and should be minimized by rendering from the execution cache.

|||

TimeDataRetrieval 645 0
TimeProcessing 4855 347
TimeRendering 23518 1090
Source 1 2
ByteCount 10249 10249
RowCount 183 21

Thank you, it seems that the cache is used. The first column is created the first time when the cache is generated, and the second column is created when I open the report a second time. I still got a RowCount > 0, I don't know why... Isn't there a way to tell the ReportServer the generate a .html file as cache, I think it would be much faster.

But then again, it works, so I thank you for your help.

|||We will automatically cache whatever we think is safe to cache. HTML caching can be problematic because there is some session specific information which is embedded in the HTML, so we cannot simply serve up the same HTML to multiple users.

Cached query result

I want to check the performance of m query and i just want to remove cached query results. Is there any suggestion how can i do this.

I just want to check after each modificatin how much improvement in performance

Check the following dbcc commands in BOL.

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

DO NOT ATTEMPT THIS ON A PRODUCTION SERVER!!!!!!!!

AMB

Cached Logon

If IIS cached a logon just for one particular page, but not for other pages
performing the same logon, is that normal? I know it sounds bizarre but I
have an ASP page that makes an ADO connection to MS SQL Server.
Authentication on SQL is set to SQL and Microsoft. Authentication in IIS is
set to Basic and Integrated. Anonymous is disabled.
I know this is not a coding NG but this is not necessarily a coding
question.
StraightASP.asp prompted me for credentials and I put in credentials for the
domain administrator. It makes an ADO connection to SQL server using sa.
(System Administrator). There is no sa account in Active Directory and the
domain administrator does not have a SQL logon.
I got access to the page, as I should but then the logon to SQL failed. I
verified the password was correct and tried again. Same result. I then
chose to try another file, which resides in the same folder:
StraightXML1.asp It uses the exact same ADO code for logon to SQL, and it
works. I have one last file, StraightXML2.asp, same code, works fine.
The differences between the files is:
1. ASP only.
2. Recordset written to XML using CSS.
3. Recordset written to XML using XLS.
I renamed the first file and it worked.
I rename it back, it fails.
I shutdown and restarted SQL. Same issue.
I shutdown and restarted IIS. Same issue.
I reboot the IIS/SQL server. Problem disappears. I now do not get prompted
for any credentials and all files work. No changes to code.
Other info:
IIS/SQL server is W2K Adv. Server (completely patched).
Remote: WinXP Pro (completely patched)
domain1\administrator - domain2\administrator both have the same password.
I was only prompted for credentials by the one file. I'm wondering where
the caching issue resides and/or if the system had a problem with SQL being
set for SQL and Windows authentication and IIS set for Basic and Integrated
only.
This question crosses several boundaries, ASP, SQL, IIS, W2K. I've included
several NGs to see if anyone can provide an opinion on the cause of this
phenomena. If you can only provide a partial answer which is relevant to
the NG you read this in, that's fine.
TIA...Why did you reference domain1 and domain2 ?
> domain1\administrator - domain2\administrator both have the same password.
What is their relationship to this?
It appears that the IIS and the SQL are installed on one machine ?
Is the XP used to access the asp pages allowed to provide "behind
the scenes" windows authentication information?
You are saying that the connection string used for ado is
identical on all three pages ?
--
Roger Abell
Microsoft MVP (Windows Security)
MCSE (W2k3,W2k,Nt4) MCDBA
"Roland Hall" <nobody@.nowhere> wrote in message
news:uN$9owb5EHA.3784@.TK2MSFTNGP10.phx.gbl...
> If IIS cached a logon just for one particular page, but not for other
pages
> performing the same logon, is that normal? I know it sounds bizarre but I
> have an ASP page that makes an ADO connection to MS SQL Server.
> Authentication on SQL is set to SQL and Microsoft. Authentication in IIS
is
> set to Basic and Integrated. Anonymous is disabled.
> I know this is not a coding NG but this is not necessarily a coding
> question.
> StraightASP.asp prompted me for credentials and I put in credentials for
the
> domain administrator. It makes an ADO connection to SQL server using sa.
> (System Administrator). There is no sa account in Active Directory and
the
> domain administrator does not have a SQL logon.
> I got access to the page, as I should but then the logon to SQL failed. I
> verified the password was correct and tried again. Same result. I then
> chose to try another file, which resides in the same folder:
> StraightXML1.asp It uses the exact same ADO code for logon to SQL, and it
> works. I have one last file, StraightXML2.asp, same code, works fine.
> The differences between the files is:
> 1. ASP only.
> 2. Recordset written to XML using CSS.
> 3. Recordset written to XML using XLS.
> I renamed the first file and it worked.
> I rename it back, it fails.
> I shutdown and restarted SQL. Same issue.
> I shutdown and restarted IIS. Same issue.
> I reboot the IIS/SQL server. Problem disappears. I now do not get
prompted
> for any credentials and all files work. No changes to code.
> Other info:
> IIS/SQL server is W2K Adv. Server (completely patched).
> Remote: WinXP Pro (completely patched)
> domain1\administrator - domain2\administrator both have the same password.
> I was only prompted for credentials by the one file. I'm wondering where
> the caching issue resides and/or if the system had a problem with SQL
being
> set for SQL and Windows authentication and IIS set for Basic and
Integrated
> only.
> This question crosses several boundaries, ASP, SQL, IIS, W2K. I've
included
> several NGs to see if anyone can provide an opinion on the cause of this
> phenomena. If you can only provide a partial answer which is relevant to
> the NG you read this in, that's fine.
> TIA...
>|||Hi,
Your browser is caching the credentials, and resending them for each
subsequent page request to that server (until the browser is closed). IE
will continue sending those credentials as long as they work. As far as IE
is concerned, the credentials work, even for the first page, as the page can
be accessed (it's just the ASP page -> SQL Server that doesn't work, but IE
doesn't know anything about logging into databases)
In terms of the login failure - can we see the exact error message that you
are seeing please?
Also, are you using a common connection string across all three pages (eg is
the connection string stored in an include file, or in the registry?) Or
have you hard coded the connection string into all three pages? If the
latter, then I would suggest moving the connection string to a central
include file (or application variable), and using that on all three pages to
ensure that you aren't getting a dud connection string on one of the pages.
Cheers
Ken
"Roland Hall" <nobody@.nowhere> wrote in message
news:uN$9owb5EHA.3784@.TK2MSFTNGP10.phx.gbl...
> If IIS cached a logon just for one particular page, but not for other
> pages
> performing the same logon, is that normal? I know it sounds bizarre but I
> have an ASP page that makes an ADO connection to MS SQL Server.
> Authentication on SQL is set to SQL and Microsoft. Authentication in IIS
> is
> set to Basic and Integrated. Anonymous is disabled.
> I know this is not a coding NG but this is not necessarily a coding
> question.
> StraightASP.asp prompted me for credentials and I put in credentials for
> the
> domain administrator. It makes an ADO connection to SQL server using sa.
> (System Administrator). There is no sa account in Active Directory and
> the
> domain administrator does not have a SQL logon.
> I got access to the page, as I should but then the logon to SQL failed. I
> verified the password was correct and tried again. Same result. I then
> chose to try another file, which resides in the same folder:
> StraightXML1.asp It uses the exact same ADO code for logon to SQL, and it
> works. I have one last file, StraightXML2.asp, same code, works fine.
> The differences between the files is:
> 1. ASP only.
> 2. Recordset written to XML using CSS.
> 3. Recordset written to XML using XLS.
> I renamed the first file and it worked.
> I rename it back, it fails.
> I shutdown and restarted SQL. Same issue.
> I shutdown and restarted IIS. Same issue.
> I reboot the IIS/SQL server. Problem disappears. I now do not get
> prompted
> for any credentials and all files work. No changes to code.
> Other info:
> IIS/SQL server is W2K Adv. Server (completely patched).
> Remote: WinXP Pro (completely patched)
> domain1\administrator - domain2\administrator both have the same password.
> I was only prompted for credentials by the one file. I'm wondering where
> the caching issue resides and/or if the system had a problem with SQL
> being
> set for SQL and Windows authentication and IIS set for Basic and
> Integrated
> only.
> This question crosses several boundaries, ASP, SQL, IIS, W2K. I've
> included
> several NGs to see if anyone can provide an opinion on the cause of this
> phenomena. If you can only provide a partial answer which is relevant to
> the NG you read this in, that's fine.
> TIA...
>|||"Roger Abell" <mvpNOSpam@.asu.edu> wrote in message
news:%23fCBKGh5EHA.2592@.TK2MSFTNGP09.phx.gbl...
: Why did you reference domain1 and domain2 ?
: > domain1\administrator - domain2\administrator both have the same
password.
: What is their relationship to this?
: It appears that the IIS and the SQL are installed on one machine ?
: Is the XP used to access the asp pages allowed to provide "behind
: the scenes" windows authentication information?
: You are saying that the connection string used for ado is
: identical on all three pages ?
Domain1:
Win2K Adv. Server
DC
AD
DNS
MS SQL 2K
IIS
Domain2:
XP Pro SP2
The folder on IIS holds all 3 .asp files.
All 3 connect to MS SQL using the same exact code for the connection and
same authentication.
On the server I was logged in as domain1\administrator.
On XP, I was logged in as domain2\administrator.
When I tried to access StraightASP.asp, I was asked for credentials. I
typed in domain1\administrator and that password which is the same password
used for the domain2\administrator. IIS, on that web server, uses Basic and
Integrated authentication only. MS SQL was set for SQL and Windows
authentication.
Only access the first page prompted me for credentials and then told me
access was denied.
The pages that used XML did not. The only difference between those files is
CSS in StraightXML1.asp and XSL in StraightXML2.asp.
It was my understanding that Windows would try to authenticate with the
current credentials and only after failing would prompt for credentials. I
also assumed that domain1\administrator would be treated differently than
domain2\administrator even though they had the same password. It appeared
that part worked correctly by requesting credentials but then failed to
authenticate. Only a reboot of the server itself cleared the problem and
ONLY the first file failed. The two others worked fine. And, when I
renamed the first file, it also worked. Rename is back, it fails. Rename
it to something else again, it works.
So, where is it being cached by filename, failing authentication, but
allowing it for other files with the same code? I restarted IIS, SQL and
cleared IE cache and closed all IE windows and started fresh and still only
a reboot of the server cleared the apparent caching issue.
I've never seen this issue and other than what I listed before the reboot, I
cannot reproduce the error.
I knew it was not IE, but I tested all that I thought could possibly be
involved. IE from the server and from the workstation both failed with the
same file and the other two worked on both. It apparently was happening at
the server level but I cannot prove it.
This is the connection string in all 3 files: (The password is not blank.
It has just been removed from here.)
Function GetRecordset()
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
"sa", ""
Set GetRecordset = cnn.Execute("select * from authors")
End Function
The only thing different in the first file, when run the first time, was an
error in the file I received from MSFT. CatalogPUBS should have been
Catalog=PUBS. All I did was add an the = sign and the password.
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp|||"Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
: Hi,
:
: Your browser is caching the credentials, and resending them for each
: subsequent page request to that server (until the browser is closed).
The cache was cleared and the browser was closed and still it failed but I
was prompted each time for credentials but only for the first page.
: IE
: will continue sending those credentials as long as they work. As far as IE
: is concerned, the credentials work, even for the first page, as the page
can
: be accessed (it's just the ASP page -> SQL Server that doesn't work, but
IE
: doesn't know anything about logging into databases)
I know IE is not logging into the database. The logon to the PUBS database
using sa was failing, but the password was correct and the other two files
worked.
: In terms of the login failure - can we see the exact error message that
you
: are seeing please?
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'sa'.
/msft/StraightASP.asp, line 7
Here's the whole file. It's not my code. It belongs to MSFT. (password
removed)
<%@. Language=VBScript %>
<%
Function GetRecordset()
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
"sa", ""
Set GetRecordset = cnn.Execute("select * from authors")
End Function
%>
<html>
<head>
<title>Straight ASP Approach</title>
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
</head>
<body>
<h1>Authors</h1>
<table cellpadding="3" cellspacing="0">
<tr>
<th>SSN</th>
<th>Last Name</th>
<th>First Name</th>
<th>Phone</th>
</tr>
<%
Dim rs
Set rs = GetRecordset()
do While Not rs.EOF
%>
<tr>
<td><%=rs("au_id")%></td>
<td><%=rs("au_lname")%></td>
<td><%=rs("au_fname")%></td>
<td><%=rs("phone")%></td>
</tr>
<%
rs.MoveNext
loop
rs.Close
Set rs = Nothing
%>
</table>
</body>
</html>
: Also, are you using a common connection string across all three pages (eg
is
: the connection string stored in an include file, or in the registry?)
Within the file itself. No include. No reg. See above.
: Or
: have you hard coded the connection string into all three pages?
Yes, MSFT did.
: If the
: latter, then I would suggest moving the connection string to a central
: include file (or application variable), and using that on all three pages
to
: ensure that you aren't getting a dud connection string on one of the
pages.
I copied the connection from StraightXML1.asp and pasted it over the one in
StraightASP.asp. Same issue.
I'm not new to this stuff. I experienced something out of the norm and was
wondering if anyone else has also experienced this.
Here is the code from StraightXML1.asp: (password removed)
<%@. Language=VBScript %>
<!-- #INCLUDE FILE="inc/xmlutil.asp" -->
<%
Function GetRecordset()
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
"sa", ""
Set GetRecordset = cnn.Execute("select * from authors")
End Function
%>
<html>
<head>
<title>Straight XML Approach</title>
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
</head>
<body>
<% = TransformXML( RecordsetToXMLDoc(GetRecordset(), "author"),
"xsl/authors.xsl" ) %>
</body>
</html>
Everything came from here:
http://msdn.microsoft.com/library/default.asp?url=/msdnmag/issues/1100/beyondasp/toc.asp
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp|||"Roland Hall" <nobody@.nowhere> wrote in message
news:uaDiFu95EHA.1596@.tk2msftngp13.phx.gbl...
> "Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
> news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> : Hi,
> :
> : Your browser is caching the credentials, and resending them for each
> : subsequent page request to that server (until the browser is closed).
> The cache was cleared and the browser was closed and still it failed but I
> was prompted each time for credentials but only for the first page.
I think you are completely missing the point, or I am not understanding what
you are saying.
When the browser connects to "firstpage.asp", it first attempts to connect
anonymously (sending no credentials). The webserver sends back a 401 header
(Access Denied) and lists available authentication mechanisms via
WWW-Authenticate HTTP headers.
The browser then prompts the user to supply credentials (or, in the case of
IE, if the website is in the local intranet security zone, will attempt to
send the current logged on user's credentials automatically). The user
supplies their credentials, and the browser sends them to the server. If the
credentials are acceptable, the server sends back a 200 OK status.
The browser then continues to reuse those credentials for subsequent
requests for pages on that site. This is why (I suspect) you are not seeing
credential prompts on subsequent pages. You can not "clear" this cache per
se. Instead, when you close the browser completely, the browser "forgets"
the credentials it has been using. When you open the browser again (well,
technically you need to start a new iexplore.exe process) and hit the site
again, you will once again be prompted for credentials
> : In terms of the login failure - can we see the exact error message that
> : you are seeing please?
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E4D)
> Login failed for user 'sa'.
> /msft/StraightASP.asp, line 7
Well, for some reason SQL Server thinks that the password being supplied by
your ADO connection object is not correct for the "sa" account.
This doesn't have anything to do with IIS caching a logon (for the purposes
impersonating an NT account for processing an ASP page), or with HTTP
authentication between the client and server, which is what your original
post seemed to be talking about. This seems to be an authentication issue
between your ADO components and SQL Server.
That said, if you've cut-n-pasted the code from one working page to another
page, that does seem "out of the norm". Just as a trial, could you put the
connection string into an include file, and include that into your two
pages, and then use the connection string from the include file? That would
probably eliminate some odd non-printable character (or something similar)
being in the connection string. Both pages should either work, or not work.
Cheers
Ken
> Here's the whole file. It's not my code. It belongs to MSFT. (password
> removed)
> <%@. Language=VBScript %>
> <%
> Function GetRecordset()
> Dim cnn
> Set cnn = CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
> "sa", ""
> Set GetRecordset = cnn.Execute("select * from authors")
> End Function
> %>
> <html>
> <head>
> <title>Straight ASP Approach</title>
> <link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
> </head>
> <body>
> <h1>Authors</h1>
> <table cellpadding="3" cellspacing="0">
> <tr>
> <th>SSN</th>
> <th>Last Name</th>
> <th>First Name</th>
> <th>Phone</th>
> </tr>
> <%
> Dim rs
> Set rs = GetRecordset()
> do While Not rs.EOF
> %>
> <tr>
> <td><%=rs("au_id")%></td>
> <td><%=rs("au_lname")%></td>
> <td><%=rs("au_fname")%></td>
> <td><%=rs("phone")%></td>
> </tr>
> <%
> rs.MoveNext
> loop
> rs.Close
> Set rs = Nothing
> %>
> </table>
> </body>
> </html>
> : Also, are you using a common connection string across all three pages
> (eg
> is
> : the connection string stored in an include file, or in the registry?)
> Within the file itself. No include. No reg. See above.
> : Or
> : have you hard coded the connection string into all three pages?
> Yes, MSFT did.
> : If the
> : latter, then I would suggest moving the connection string to a central
> : include file (or application variable), and using that on all three
> pages
> to
> : ensure that you aren't getting a dud connection string on one of the
> pages.
> I copied the connection from StraightXML1.asp and pasted it over the one
> in
> StraightASP.asp. Same issue.
> I'm not new to this stuff. I experienced something out of the norm and
> was
> wondering if anyone else has also experienced this.
> Here is the code from StraightXML1.asp: (password removed)
> <%@. Language=VBScript %>
> <!-- #INCLUDE FILE="inc/xmlutil.asp" -->
> <%
> Function GetRecordset()
> Dim cnn
> Set cnn = CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
> "sa", ""
> Set GetRecordset = cnn.Execute("select * from authors")
> End Function
> %>
> <html>
> <head>
> <title>Straight XML Approach</title>
> <link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
> </head>
> <body>
> <% = TransformXML( RecordsetToXMLDoc(GetRecordset(), "author"),
> "xsl/authors.xsl" ) %>
> </body>
> </html>
> Everything came from here:
> http://msdn.microsoft.com/library/default.asp?url=/msdnmag/issues/1100/beyondasp/toc.asp
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
> WSH 5.6 Documentation -
> http://msdn.microsoft.com/downloads/list/webdev.asp
> MSDN Library - http://msdn.microsoft.com/library/default.asp
>|||"Ken Schaefer" wrote in message
news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
:
: "Roland Hall" <nobody@.nowhere> wrote in message
: news:uaDiFu95EHA.1596@.tk2msftngp13.phx.gbl...
: > "Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
: > news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
: > : Hi,
: > :
: > : Your browser is caching the credentials, and resending them for each
: > : subsequent page request to that server (until the browser is closed).
: >
: > The cache was cleared and the browser was closed and still it failed but
I
: > was prompted each time for credentials but only for the first page.
:
: I think you are completely missing the point, or I am not understanding
what
: you are saying.
:
: When the browser connects to "firstpage.asp", it first attempts to connect
: anonymously (sending no credentials). The webserver sends back a 401
header
: (Access Denied) and lists available authentication mechanisms via
: WWW-Authenticate HTTP headers.
:
: The browser then prompts the user to supply credentials (or, in the case
of
: IE, if the website is in the local intranet security zone, will attempt to
: send the current logged on user's credentials automatically). The user
: supplies their credentials, and the browser sends them to the server. If
the
: credentials are acceptable, the server sends back a 200 OK status.
:
: The browser then continues to reuse those credentials for subsequent
: requests for pages on that site. This is why (I suspect) you are not
seeing
: credential prompts on subsequent pages. You can not "clear" this cache per
: se. Instead, when you close the browser completely, the browser "forgets"
: the credentials it has been using. When you open the browser again (well,
: technically you need to start a new iexplore.exe process) and hit the site
: again, you will once again be prompted for credentials
:
: > : In terms of the login failure - can we see the exact error message
that
: > : you are seeing please?
: >
: > Error Type:
: > Microsoft OLE DB Provider for SQL Server (0x80040E4D)
: > Login failed for user 'sa'.
: > /msft/StraightASP.asp, line 7
:
: Well, for some reason SQL Server thinks that the password being supplied
by
: your ADO connection object is not correct for the "sa" account.
:
: This doesn't have anything to do with IIS caching a logon (for the
purposes
: impersonating an NT account for processing an ASP page), or with HTTP
: authentication between the client and server, which is what your original
: post seemed to be talking about. This seems to be an authentication issue
: between your ADO components and SQL Server.
:
: That said, if you've cut-n-pasted the code from one working page to
another
: page, that does seem "out of the norm". Just as a trial, could you put the
: connection string into an include file, and include that into your two
: pages, and then use the connection string from the include file? That
would
: probably eliminate some odd non-printable character (or something similar)
: being in the connection string. Both pages should either work, or not
work.
Ken...
I provided the link at MSFT where I got the files. You can see for yourself
there are no non-printable characters. Again, this is not my code. Also, I
cannot reproduce the problem now that the server has been rebooted. You're
also apparently excluding the other particulars.
If the sa logon fails, it should fail no matter which file is trying to
authenticate as long as that code is the same. Renaming the file, makes it
work. Renaming it back to the original name makes it fail once again.
However, nothing fails now that the server has been rebooted. Explain to me
how the code has anything to do with that. Also, since event viewer shows
nothing, where can I get information regarding the failed logon?
TIA...
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp|||From what you have described, I am at a loss as to a
probable cause/explaination.
One thing I noticed in your reply was mention that you
understood browsing client authentications as
> It was my understanding that Windows would try to authenticate with the
> current credentials and only after failing would prompt for credentials.
Keep in mind that whether the IE browser will supply the Windows
credentials or not is configurable in the IE security settings.
This however does not seem to be a factor in what you have
described as some of the cross-test seem to rule this out.
--
Roger Abell
Microsoft MVP (Windows Security)
MCSE (W2k3,W2k,Nt4) MCDBA
"Roland Hall" <nobody@.nowhere> wrote in message
news:OZdeOn95EHA.1452@.TK2MSFTNGP11.phx.gbl...
> "Roger Abell" <mvpNOSpam@.asu.edu> wrote in message
> news:%23fCBKGh5EHA.2592@.TK2MSFTNGP09.phx.gbl...
> : Why did you reference domain1 and domain2 ?
> : > domain1\administrator - domain2\administrator both have the same
> password.
> : What is their relationship to this?
> : It appears that the IIS and the SQL are installed on one machine ?
> : Is the XP used to access the asp pages allowed to provide "behind
> : the scenes" windows authentication information?
> : You are saying that the connection string used for ado is
> : identical on all three pages ?
> Domain1:
> Win2K Adv. Server
> DC
> AD
> DNS
> MS SQL 2K
> IIS
> Domain2:
> XP Pro SP2
> The folder on IIS holds all 3 .asp files.
> All 3 connect to MS SQL using the same exact code for the connection and
> same authentication.
> On the server I was logged in as domain1\administrator.
> On XP, I was logged in as domain2\administrator.
> When I tried to access StraightASP.asp, I was asked for credentials. I
> typed in domain1\administrator and that password which is the same
password
> used for the domain2\administrator. IIS, on that web server, uses Basic
and
> Integrated authentication only. MS SQL was set for SQL and Windows
> authentication.
> Only access the first page prompted me for credentials and then told me
> access was denied.
> The pages that used XML did not. The only difference between those files
is
> CSS in StraightXML1.asp and XSL in StraightXML2.asp.
> It was my understanding that Windows would try to authenticate with the
> current credentials and only after failing would prompt for credentials.
I
> also assumed that domain1\administrator would be treated differently than
> domain2\administrator even though they had the same password. It appeared
> that part worked correctly by requesting credentials but then failed to
> authenticate. Only a reboot of the server itself cleared the problem and
> ONLY the first file failed. The two others worked fine. And, when I
> renamed the first file, it also worked. Rename is back, it fails. Rename
> it to something else again, it works.
> So, where is it being cached by filename, failing authentication, but
> allowing it for other files with the same code? I restarted IIS, SQL and
> cleared IE cache and closed all IE windows and started fresh and still
only
> a reboot of the server cleared the apparent caching issue.
> I've never seen this issue and other than what I listed before the reboot,
I
> cannot reproduce the error.
> I knew it was not IE, but I tested all that I thought could possibly be
> involved. IE from the server and from the workstation both failed with
the
> same file and the other two worked on both. It apparently was happening
at
> the server level but I cannot prove it.
> This is the connection string in all 3 files: (The password is not blank.
> It has just been removed from here.)
> Function GetRecordset()
> Dim cnn
> Set cnn = CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data
Source=localhost",
> "sa", ""
> Set GetRecordset = cnn.Execute("select * from authors")
> End Function
> The only thing different in the first file, when run the first time, was
an
> error in the file I received from MSFT. CatalogPUBS should have been
> Catalog=PUBS. All I did was add an the = sign and the password.
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
> WSH 5.6 Documentation -
http://msdn.microsoft.com/downloads/list/webdev.asp
> MSDN Library - http://msdn.microsoft.com/library/default.asp
>|||"Roland Hall" <nobody@.nowhere> wrote in message
news:OqNjkx%235EHA.3736@.TK2MSFTNGP10.phx.gbl...
> "Ken Schaefer" wrote in message
> news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
> :
> : "Roland Hall" <nobody@.nowhere> wrote in message
> : news:uaDiFu95EHA.1596@.tk2msftngp13.phx.gbl...
> : > "Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
> : > news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> : > : Hi,
> : > :
> : > : Your browser is caching the credentials, and resending them for each
> : > : subsequent page request to that server (until the browser is
closed).
> : >
> : > The cache was cleared and the browser was closed and still it failed
but
> I
> : > was prompted each time for credentials but only for the first page.
> :
> : I think you are completely missing the point, or I am not understanding
> what
> : you are saying.
> :
> : When the browser connects to "firstpage.asp", it first attempts to
connect
> : anonymously (sending no credentials). The webserver sends back a 401
> header
> : (Access Denied) and lists available authentication mechanisms via
> : WWW-Authenticate HTTP headers.
> :
> : The browser then prompts the user to supply credentials (or, in the case
> of
> : IE, if the website is in the local intranet security zone, will attempt
to
> : send the current logged on user's credentials automatically). The user
> : supplies their credentials, and the browser sends them to the server. If
> the
> : credentials are acceptable, the server sends back a 200 OK status.
> :
> : The browser then continues to reuse those credentials for subsequent
> : requests for pages on that site. This is why (I suspect) you are not
> seeing
> : credential prompts on subsequent pages. You can not "clear" this cache
per
> : se. Instead, when you close the browser completely, the browser
"forgets"
> : the credentials it has been using. When you open the browser again
(well,
> : technically you need to start a new iexplore.exe process) and hit the
site
> : again, you will once again be prompted for credentials
> :
> : > : In terms of the login failure - can we see the exact error message
> that
> : > : you are seeing please?
> : >
> : > Error Type:
> : > Microsoft OLE DB Provider for SQL Server (0x80040E4D)
> : > Login failed for user 'sa'.
> : > /msft/StraightASP.asp, line 7
> :
> : Well, for some reason SQL Server thinks that the password being supplied
> by
> : your ADO connection object is not correct for the "sa" account.
> :
> : This doesn't have anything to do with IIS caching a logon (for the
> purposes
> : impersonating an NT account for processing an ASP page), or with HTTP
> : authentication between the client and server, which is what your
original
> : post seemed to be talking about. This seems to be an authentication
issue
> : between your ADO components and SQL Server.
> :
> : That said, if you've cut-n-pasted the code from one working page to
> another
> : page, that does seem "out of the norm". Just as a trial, could you put
the
> : connection string into an include file, and include that into your two
> : pages, and then use the connection string from the include file? That
> would
> : probably eliminate some odd non-printable character (or something
similar)
> : being in the connection string. Both pages should either work, or not
> work.
> Ken...
> I provided the link at MSFT where I got the files. You can see for
yourself
> there are no non-printable characters. Again, this is not my code. Also,
I
> cannot reproduce the problem now that the server has been rebooted.
You're
> also apparently excluding the other particulars.
> If the sa logon fails, it should fail no matter which file is trying to
> authenticate as long as that code is the same. Renaming the file, makes
it
> work. Renaming it back to the original name makes it fail once again.
This is the most perplexing part. And, as you mentioned in post of the
other
subthread, you could close all IE processes, start again, and see the same
pattern (prior to the rebooting). That the file name should be so important
is not simple to understand, and makes it sound as if there was a corruption
in IIS's caching of the file involved.
--
Roger
> However, nothing fails now that the server has been rebooted. Explain to
me
> how the code has anything to do with that. Also, since event viewer shows
> nothing, where can I get information regarding the failed logon?
> TIA...
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
> WSH 5.6 Documentation -
http://msdn.microsoft.com/downloads/list/webdev.asp
> MSDN Library - http://msdn.microsoft.com/library/default.asp
>|||"Roland Hall" <nobody@.nowhere> wrote in message
news:OqNjkx%235EHA.3736@.TK2MSFTNGP10.phx.gbl...
> "Ken Schaefer" wrote in message
> news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
> :
> I provided the link at MSFT where I got the files. You can see for
> yourself
> there are no non-printable characters. Again, this is not my code. Also,
> I
> cannot reproduce the problem now that the server has been rebooted.
> You're
> also apparently excluding the other particulars.
I just wanted to address the points you raised by using Basic / Integrated
Authentication with IIS, and also with having to authenticate on the "first"
page versus subsequent pages, and about clearing the cache. None of those
things should have anything to do with the issue you are seeing. I was not
trying to "exclude the other particulars", but rather explain why I do not
think some of the issues raised where not relevant.
I agree that the situation you are in is a bit odd. I'm struggling to think
of a reason why you might have been seeing the behaviour that you were. As
Roger Abell has mentioned, perhaps the in-memory cached copy of the ASP page
was somehow corrupt. Rebooting the box would have completely cleared that
cache.
Cheers
Ken|||"Roger Abell" wrote in message news:uE2EmkB6EHA.1264@.TK2MSFTNGP12.phx.gbl...
: From what you have described, I am at a loss as to a
: probable cause/explaination.
: One thing I noticed in your reply was mention that you
: understood browsing client authentications as
: > It was my understanding that Windows would try to authenticate with the
: > current credentials and only after failing would prompt for credentials.
: Keep in mind that whether the IE browser will supply the Windows
: credentials or not is configurable in the IE security settings.
: This however does not seem to be a factor in what you have
: described as some of the cross-test seem to rule this out.
I can't think of anything either nor can I find a log file, including SQL,
that documented the error.
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp|||"Roger Abell" wrote in message
news:%23yW0kBD6EHA.2124@.TK2MSFTNGP15.phx.gbl...
:
: "Roland Hall" <nobody@.nowhere> wrote in message
: news:OqNjkx%235EHA.3736@.TK2MSFTNGP10.phx.gbl...
: > "Ken Schaefer" wrote in message
: > news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
: > :
: > : "Roland Hall" <nobody@.nowhere> wrote in message
: > : news:uaDiFu95EHA.1596@.tk2msftngp13.phx.gbl...
: > : > "Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
: > : > news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
: > : > : Hi,
: > : > :
: > : > : Your browser is caching the credentials, and resending them for
each
: > : > : subsequent page request to that server (until the browser is
: closed).
: > : >
: > : > The cache was cleared and the browser was closed and still it failed
: but
: > I
: > : > was prompted each time for credentials but only for the first page.
: > :
: > : I think you are completely missing the point, or I am not
understanding
: > what
: > : you are saying.
: > :
: > : When the browser connects to "firstpage.asp", it first attempts to
: connect
: > : anonymously (sending no credentials). The webserver sends back a 401
: > header
: > : (Access Denied) and lists available authentication mechanisms via
: > : WWW-Authenticate HTTP headers.
: > :
: > : The browser then prompts the user to supply credentials (or, in the
case
: > of
: > : IE, if the website is in the local intranet security zone, will
attempt
: to
: > : send the current logged on user's credentials automatically). The user
: > : supplies their credentials, and the browser sends them to the server.
If
: > the
: > : credentials are acceptable, the server sends back a 200 OK status.
: > :
: > : The browser then continues to reuse those credentials for subsequent
: > : requests for pages on that site. This is why (I suspect) you are not
: > seeing
: > : credential prompts on subsequent pages. You can not "clear" this cache
: per
: > : se. Instead, when you close the browser completely, the browser
: "forgets"
: > : the credentials it has been using. When you open the browser again
: (well,
: > : technically you need to start a new iexplore.exe process) and hit the
: site
: > : again, you will once again be prompted for credentials
: > :
: > : > : In terms of the login failure - can we see the exact error message
: > that
: > : > : you are seeing please?
: > : >
: > : > Error Type:
: > : > Microsoft OLE DB Provider for SQL Server (0x80040E4D)
: > : > Login failed for user 'sa'.
: > : > /msft/StraightASP.asp, line 7
: > :
: > : Well, for some reason SQL Server thinks that the password being
supplied
: > by
: > : your ADO connection object is not correct for the "sa" account.
: > :
: > : This doesn't have anything to do with IIS caching a logon (for the
: > purposes
: > : impersonating an NT account for processing an ASP page), or with HTTP
: > : authentication between the client and server, which is what your
: original
: > : post seemed to be talking about. This seems to be an authentication
: issue
: > : between your ADO components and SQL Server.
: > :
: > : That said, if you've cut-n-pasted the code from one working page to
: > another
: > : page, that does seem "out of the norm". Just as a trial, could you put
: the
: > : connection string into an include file, and include that into your two
: > : pages, and then use the connection string from the include file? That
: > would
: > : probably eliminate some odd non-printable character (or something
: similar)
: > : being in the connection string. Both pages should either work, or not
: > work.
: >
: > Ken...
: >
: > I provided the link at MSFT where I got the files. You can see for
: yourself
: > there are no non-printable characters. Again, this is not my code.
Also,
: I
: > cannot reproduce the problem now that the server has been rebooted.
: You're
: > also apparently excluding the other particulars.
: >
: > If the sa logon fails, it should fail no matter which file is trying to
: > authenticate as long as that code is the same. Renaming the file, makes
: it
: > work. Renaming it back to the original name makes it fail once again.
:
: This is the most perplexing part. And, as you mentioned in post of the
: other
: subthread, you could close all IE processes, start again, and see the same
: pattern (prior to the rebooting). That the file name should be so
important
: is not simple to understand, and makes it sound as if there was a
corruption
: in IIS's caching of the file involved.
Is bouncing the box the only way to clear the IIS cache?
--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp|||"Ken Schaefer" wrote in message
news:uChlX4I6EHA.1260@.TK2MSFTNGP12.phx.gbl...
:
: "Roland Hall" <nobody@.nowhere> wrote in message
: news:OqNjkx%235EHA.3736@.TK2MSFTNGP10.phx.gbl...
: > "Ken Schaefer" wrote in message
: > news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
: > :
:
: > I provided the link at MSFT where I got the files. You can see for
: > yourself
: > there are no non-printable characters. Again, this is not my code.
Also,
: > I
: > cannot reproduce the problem now that the server has been rebooted.
: > You're
: > also apparently excluding the other particulars.
:
: I just wanted to address the points you raised by using Basic / Integrated
: Authentication with IIS, and also with having to authenticate on the
"first"
: page versus subsequent pages, and about clearing the cache. None of those
: things should have anything to do with the issue you are seeing.
I agree.
: I was not
: trying to "exclude the other particulars", but rather explain why I do not
: think some of the issues raised where not relevant.
Ok, I misunderstood your comment.
: I agree that the situation you are in is a bit odd.
Well, I'm no longer in it but because I was, I do not feel comfortable it
will not reoccur.
: I'm struggling to think
: of a reason why you might have been seeing the behaviour that you were. As
: Roger Abell has mentioned, perhaps the in-memory cached copy of the ASP
page
: was somehow corrupt. Rebooting the box would have completely cleared that
: cache.
Thanks Ken. If I give the wrong password for sa will it cache that response
for the file? I wasn't sure of the password as I never use sa for anything
and knew it as not blank. After it failed, I set the password so I would
know what it was, but it still failed. That's when I started testing.
However, during all this, the other files never failed although I don't
think I tried them with the bad password [if it was bad in the beginning].
With that timeline, the file [StraightASP.asp] that was failing appears to
have been cached and tied to that filename and after the password was
correct, the other files worked because they were not in the cache. That
would also make sense as to why renaming the file would then work but the
old filename would not since that file was cached.
I wonder if anyone can confirm that it caches the filename with the
authentication and until released, refuses additional credentials. It
doesn't sound like it is working by design. Sounds more like what Oracle
calls a 'feature.' (O:=--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp|||"Roland Hall" <nobody@.nowhere> wrote in message
news:eF6YLEJ6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> "Ken Schaefer" wrote in message
> news:uChlX4I6EHA.1260@.TK2MSFTNGP12.phx.gbl...
> :
> : "Roland Hall" <nobody@.nowhere> wrote in message
> : news:OqNjkx%235EHA.3736@.TK2MSFTNGP10.phx.gbl...
> : > "Ken Schaefer" wrote in message
> : > news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
> : > :
> :
> : > I provided the link at MSFT where I got the files. You can see for
> : > yourself
> : > there are no non-printable characters. Again, this is not my code.
> Also,
> : > I
> : > cannot reproduce the problem now that the server has been rebooted.
> : > You're
> : > also apparently excluding the other particulars.
> :
> : I just wanted to address the points you raised by using Basic /
Integrated
> : Authentication with IIS, and also with having to authenticate on the
> "first"
> : page versus subsequent pages, and about clearing the cache. None of
those
> : things should have anything to do with the issue you are seeing.
> I agree.
> : I was not
> : trying to "exclude the other particulars", but rather explain why I do
not
> : think some of the issues raised where not relevant.
> Ok, I misunderstood your comment.
> : I agree that the situation you are in is a bit odd.
> Well, I'm no longer in it but because I was, I do not feel comfortable it
> will not reoccur.
> : I'm struggling to think
> : of a reason why you might have been seeing the behaviour that you were.
As
> : Roger Abell has mentioned, perhaps the in-memory cached copy of the ASP
> page
> : was somehow corrupt. Rebooting the box would have completely cleared
that
> : cache.
> Thanks Ken. If I give the wrong password for sa will it cache that
response
> for the file? I wasn't sure of the password as I never use sa for
anything
> and knew it as not blank. After it failed, I set the password so I would
> know what it was, but it still failed. That's when I started testing.
> However, during all this, the other files never failed although I don't
> think I tried them with the bad password [if it was bad in the beginning].
> With that timeline, the file [StraightASP.asp] that was failing appears to
> have been cached and tied to that filename and after the password was
> correct, the other files worked because they were not in the cache. That
> would also make sense as to why renaming the file would then work but the
> old filename would not since that file was cached.
> I wonder if anyone can confirm that it caches the filename with the
> authentication and until released, refuses additional credentials. It
> doesn't sound like it is working by design. Sounds more like what Oracle
> calls a 'feature.' (O:=>
Too funny Roland :-)
Since you have said/shown the connection string was just hard
wired into the page, whenever the page is hit that is what gets
used. Connection pooling etc. should all happen only if the
existing (if any) connection is correct for the connection string
specification (but, of course this is none to recycle as the auth
failed). If the cached copy did have invalid sa+pwd combination
that would tend to explain all you have stated as far as I can see.
--
Roger

Cached Logon

If IIS cached a logon just for one particular page, but not for other pages
performing the same logon, is that normal? I know it sounds bizarre but I
have an ASP page that makes an ADO connection to MS SQL Server.
Authentication on SQL is set to SQL and Microsoft. Authentication in IIS is
set to Basic and Integrated. Anonymous is disabled.
I know this is not a coding NG but this is not necessarily a coding
question.
StraightASP.asp prompted me for credentials and I put in credentials for the
domain administrator. It makes an ADO connection to SQL server using sa.
(System Administrator). There is no sa account in Active Directory and the
domain administrator does not have a SQL logon.
I got access to the page, as I should but then the logon to SQL failed. I
verified the password was correct and tried again. Same result. I then
chose to try another file, which resides in the same folder:
StraightXML1.asp It uses the exact same ADO code for logon to SQL, and it
works. I have one last file, StraightXML2.asp, same code, works fine.
The differences between the files is:
1. ASP only.
2. Recordset written to XML using CSS.
3. Recordset written to XML using XLS.
I renamed the first file and it worked.
I rename it back, it fails.
I shutdown and restarted SQL. Same issue.
I shutdown and restarted IIS. Same issue.
I reboot the IIS/SQL server. Problem disappears. I now do not get prompted
for any credentials and all files work. No changes to code.
Other info:
IIS/SQL server is W2K Adv. Server (completely patched).
Remote: WinXP Pro (completely patched)
domain1\administrator - domain2\administrator both have the same password.
I was only prompted for credentials by the one file. I'm wondering where
the caching issue resides and/or if the system had a problem with SQL being
set for SQL and Windows authentication and IIS set for Basic and Integrated
only.
This question crosses several boundaries, ASP, SQL, IIS, W2K. I've included
several NGs to see if anyone can provide an opinion on the cause of this
phenomena. If you can only provide a partial answer which is relevant to
the NG you read this in, that's fine.
TIA...
Why did you reference domain1 and domain2 ?
> domain1\administrator - domain2\administrator both have the same password.
What is their relationship to this?
It appears that the IIS and the SQL are installed on one machine ?
Is the XP used to access the asp pages allowed to provide "behind
the scenes" windows authentication information?
You are saying that the connection string used for ado is
identical on all three pages ?
Roger Abell
Microsoft MVP (Windows Security)
MCSE (W2k3,W2k,Nt4) MCDBA
"Roland Hall" <nobody@.nowhere> wrote in message
news:uN$9owb5EHA.3784@.TK2MSFTNGP10.phx.gbl...
> If IIS cached a logon just for one particular page, but not for other
pages
> performing the same logon, is that normal? I know it sounds bizarre but I
> have an ASP page that makes an ADO connection to MS SQL Server.
> Authentication on SQL is set to SQL and Microsoft. Authentication in IIS
is
> set to Basic and Integrated. Anonymous is disabled.
> I know this is not a coding NG but this is not necessarily a coding
> question.
> StraightASP.asp prompted me for credentials and I put in credentials for
the
> domain administrator. It makes an ADO connection to SQL server using sa.
> (System Administrator). There is no sa account in Active Directory and
the
> domain administrator does not have a SQL logon.
> I got access to the page, as I should but then the logon to SQL failed. I
> verified the password was correct and tried again. Same result. I then
> chose to try another file, which resides in the same folder:
> StraightXML1.asp It uses the exact same ADO code for logon to SQL, and it
> works. I have one last file, StraightXML2.asp, same code, works fine.
> The differences between the files is:
> 1. ASP only.
> 2. Recordset written to XML using CSS.
> 3. Recordset written to XML using XLS.
> I renamed the first file and it worked.
> I rename it back, it fails.
> I shutdown and restarted SQL. Same issue.
> I shutdown and restarted IIS. Same issue.
> I reboot the IIS/SQL server. Problem disappears. I now do not get
prompted
> for any credentials and all files work. No changes to code.
> Other info:
> IIS/SQL server is W2K Adv. Server (completely patched).
> Remote: WinXP Pro (completely patched)
> domain1\administrator - domain2\administrator both have the same password.
> I was only prompted for credentials by the one file. I'm wondering where
> the caching issue resides and/or if the system had a problem with SQL
being
> set for SQL and Windows authentication and IIS set for Basic and
Integrated
> only.
> This question crosses several boundaries, ASP, SQL, IIS, W2K. I've
included
> several NGs to see if anyone can provide an opinion on the cause of this
> phenomena. If you can only provide a partial answer which is relevant to
> the NG you read this in, that's fine.
> TIA...
>
|||Hi,
Your browser is caching the credentials, and resending them for each
subsequent page request to that server (until the browser is closed). IE
will continue sending those credentials as long as they work. As far as IE
is concerned, the credentials work, even for the first page, as the page can
be accessed (it's just the ASP page -> SQL Server that doesn't work, but IE
doesn't know anything about logging into databases)
In terms of the login failure - can we see the exact error message that you
are seeing please?
Also, are you using a common connection string across all three pages (eg is
the connection string stored in an include file, or in the registry?) Or
have you hard coded the connection string into all three pages? If the
latter, then I would suggest moving the connection string to a central
include file (or application variable), and using that on all three pages to
ensure that you aren't getting a dud connection string on one of the pages.
Cheers
Ken
"Roland Hall" <nobody@.nowhere> wrote in message
news:uN$9owb5EHA.3784@.TK2MSFTNGP10.phx.gbl...
> If IIS cached a logon just for one particular page, but not for other
> pages
> performing the same logon, is that normal? I know it sounds bizarre but I
> have an ASP page that makes an ADO connection to MS SQL Server.
> Authentication on SQL is set to SQL and Microsoft. Authentication in IIS
> is
> set to Basic and Integrated. Anonymous is disabled.
> I know this is not a coding NG but this is not necessarily a coding
> question.
> StraightASP.asp prompted me for credentials and I put in credentials for
> the
> domain administrator. It makes an ADO connection to SQL server using sa.
> (System Administrator). There is no sa account in Active Directory and
> the
> domain administrator does not have a SQL logon.
> I got access to the page, as I should but then the logon to SQL failed. I
> verified the password was correct and tried again. Same result. I then
> chose to try another file, which resides in the same folder:
> StraightXML1.asp It uses the exact same ADO code for logon to SQL, and it
> works. I have one last file, StraightXML2.asp, same code, works fine.
> The differences between the files is:
> 1. ASP only.
> 2. Recordset written to XML using CSS.
> 3. Recordset written to XML using XLS.
> I renamed the first file and it worked.
> I rename it back, it fails.
> I shutdown and restarted SQL. Same issue.
> I shutdown and restarted IIS. Same issue.
> I reboot the IIS/SQL server. Problem disappears. I now do not get
> prompted
> for any credentials and all files work. No changes to code.
> Other info:
> IIS/SQL server is W2K Adv. Server (completely patched).
> Remote: WinXP Pro (completely patched)
> domain1\administrator - domain2\administrator both have the same password.
> I was only prompted for credentials by the one file. I'm wondering where
> the caching issue resides and/or if the system had a problem with SQL
> being
> set for SQL and Windows authentication and IIS set for Basic and
> Integrated
> only.
> This question crosses several boundaries, ASP, SQL, IIS, W2K. I've
> included
> several NGs to see if anyone can provide an opinion on the cause of this
> phenomena. If you can only provide a partial answer which is relevant to
> the NG you read this in, that's fine.
> TIA...
>
|||"Roger Abell" <mvpNOSpam@.asu.edu> wrote in message
news:%23fCBKGh5EHA.2592@.TK2MSFTNGP09.phx.gbl...
: Why did you reference domain1 and domain2 ?
: > domain1\administrator - domain2\administrator both have the same
password.
: What is their relationship to this?
: It appears that the IIS and the SQL are installed on one machine ?
: Is the XP used to access the asp pages allowed to provide "behind
: the scenes" windows authentication information?
: You are saying that the connection string used for ado is
: identical on all three pages ?
Domain1:
Win2K Adv. Server
DC
AD
DNS
MS SQL 2K
IIS
Domain2:
XP Pro SP2
The folder on IIS holds all 3 .asp files.
All 3 connect to MS SQL using the same exact code for the connection and
same authentication.
On the server I was logged in as domain1\administrator.
On XP, I was logged in as domain2\administrator.
When I tried to access StraightASP.asp, I was asked for credentials. I
typed in domain1\administrator and that password which is the same password
used for the domain2\administrator. IIS, on that web server, uses Basic and
Integrated authentication only. MS SQL was set for SQL and Windows
authentication.
Only access the first page prompted me for credentials and then told me
access was denied.
The pages that used XML did not. The only difference between those files is
CSS in StraightXML1.asp and XSL in StraightXML2.asp.
It was my understanding that Windows would try to authenticate with the
current credentials and only after failing would prompt for credentials. I
also assumed that domain1\administrator would be treated differently than
domain2\administrator even though they had the same password. It appeared
that part worked correctly by requesting credentials but then failed to
authenticate. Only a reboot of the server itself cleared the problem and
ONLY the first file failed. The two others worked fine. And, when I
renamed the first file, it also worked. Rename is back, it fails. Rename
it to something else again, it works.
So, where is it being cached by filename, failing authentication, but
allowing it for other files with the same code? I restarted IIS, SQL and
cleared IE cache and closed all IE windows and started fresh and still only
a reboot of the server cleared the apparent caching issue.
I've never seen this issue and other than what I listed before the reboot, I
cannot reproduce the error.
I knew it was not IE, but I tested all that I thought could possibly be
involved. IE from the server and from the workstation both failed with the
same file and the other two worked on both. It apparently was happening at
the server level but I cannot prove it.
This is the connection string in all 3 files: (The password is not blank.
It has just been removed from here.)
Function GetRecordset()
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
"sa", ""
Set GetRecordset = cnn.Execute("select * from authors")
End Function
The only thing different in the first file, when run the first time, was an
error in the file I received from MSFT. CatalogPUBS should have been
Catalog=PUBS. All I did was add an the = sign and the password.
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
|||"Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
: Hi,
:
: Your browser is caching the credentials, and resending them for each
: subsequent page request to that server (until the browser is closed).
The cache was cleared and the browser was closed and still it failed but I
was prompted each time for credentials but only for the first page.
: IE
: will continue sending those credentials as long as they work. As far as IE
: is concerned, the credentials work, even for the first page, as the page
can
: be accessed (it's just the ASP page -> SQL Server that doesn't work, but
IE
: doesn't know anything about logging into databases)
I know IE is not logging into the database. The logon to the PUBS database
using sa was failing, but the password was correct and the other two files
worked.
: In terms of the login failure - can we see the exact error message that
you
: are seeing please?
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'sa'.
/msft/StraightASP.asp, line 7
Here's the whole file. It's not my code. It belongs to MSFT. (password
removed)
<%@. Language=VBScript %>
<%
Function GetRecordset()
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
"sa", ""
Set GetRecordset = cnn.Execute("select * from authors")
End Function
%>
<html>
<head>
<title>Straight ASP Approach</title>
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
</head>
<body>
<h1>Authors</h1>
<table cellpadding="3" cellspacing="0">
<tr>
<th>SSN</th>
<th>Last Name</th>
<th>First Name</th>
<th>Phone</th>
</tr>
<%
Dim rs
Set rs = GetRecordset()
do While Not rs.EOF
%>
<tr>
<td><%=rs("au_id")%></td>
<td><%=rs("au_lname")%></td>
<td><%=rs("au_fname")%></td>
<td><%=rs("phone")%></td>
</tr>
<%
rs.MoveNext
loop
rs.Close
Set rs = Nothing
%>
</table>
</body>
</html>
: Also, are you using a common connection string across all three pages (eg
is
: the connection string stored in an include file, or in the registry?)
Within the file itself. No include. No reg. See above.
: Or
: have you hard coded the connection string into all three pages?
Yes, MSFT did.
: If the
: latter, then I would suggest moving the connection string to a central
: include file (or application variable), and using that on all three pages
to
: ensure that you aren't getting a dud connection string on one of the
pages.
I copied the connection from StraightXML1.asp and pasted it over the one in
StraightASP.asp. Same issue.
I'm not new to this stuff. I experienced something out of the norm and was
wondering if anyone else has also experienced this.
Here is the code from StraightXML1.asp: (password removed)
<%@. Language=VBScript %>
<!-- #INCLUDE FILE="inc/xmlutil.asp" -->
<%
Function GetRecordset()
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
"sa", ""
Set GetRecordset = cnn.Execute("select * from authors")
End Function
%>
<html>
<head>
<title>Straight XML Approach</title>
<link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
</head>
<body>
<% = TransformXML( RecordsetToXMLDoc(GetRecordset(), "author"),
"xsl/authors.xsl" ) %>
</body>
</html>
Everything came from here:
http://msdn.microsoft.com/library/de...ondasp/toc.asp
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
|||"Roland Hall" <nobody@.nowhere> wrote in message
news:uaDiFu95EHA.1596@.tk2msftngp13.phx.gbl...
> "Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
> news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> : Hi,
> :
> : Your browser is caching the credentials, and resending them for each
> : subsequent page request to that server (until the browser is closed).
> The cache was cleared and the browser was closed and still it failed but I
> was prompted each time for credentials but only for the first page.
I think you are completely missing the point, or I am not understanding what
you are saying.
When the browser connects to "firstpage.asp", it first attempts to connect
anonymously (sending no credentials). The webserver sends back a 401 header
(Access Denied) and lists available authentication mechanisms via
WWW-Authenticate HTTP headers.
The browser then prompts the user to supply credentials (or, in the case of
IE, if the website is in the local intranet security zone, will attempt to
send the current logged on user's credentials automatically). The user
supplies their credentials, and the browser sends them to the server. If the
credentials are acceptable, the server sends back a 200 OK status.
The browser then continues to reuse those credentials for subsequent
requests for pages on that site. This is why (I suspect) you are not seeing
credential prompts on subsequent pages. You can not "clear" this cache per
se. Instead, when you close the browser completely, the browser "forgets"
the credentials it has been using. When you open the browser again (well,
technically you need to start a new iexplore.exe process) and hit the site
again, you will once again be prompted for credentials

> : In terms of the login failure - can we see the exact error message that
> : you are seeing please?
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E4D)
> Login failed for user 'sa'.
> /msft/StraightASP.asp, line 7
Well, for some reason SQL Server thinks that the password being supplied by
your ADO connection object is not correct for the "sa" account.
This doesn't have anything to do with IIS caching a logon (for the purposes
impersonating an NT account for processing an ASP page), or with HTTP
authentication between the client and server, which is what your original
post seemed to be talking about. This seems to be an authentication issue
between your ADO components and SQL Server.
That said, if you've cut-n-pasted the code from one working page to another
page, that does seem "out of the norm". Just as a trial, could you put the
connection string into an include file, and include that into your two
pages, and then use the connection string from the include file? That would
probably eliminate some odd non-printable character (or something similar)
being in the connection string. Both pages should either work, or not work.
Cheers
Ken

> Here's the whole file. It's not my code. It belongs to MSFT. (password
> removed)
> <%@. Language=VBScript %>
> <%
> Function GetRecordset()
> Dim cnn
> Set cnn = CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
> "sa", ""
> Set GetRecordset = cnn.Execute("select * from authors")
> End Function
> %>
> <html>
> <head>
> <title>Straight ASP Approach</title>
> <link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
> </head>
> <body>
> <h1>Authors</h1>
> <table cellpadding="3" cellspacing="0">
> <tr>
> <th>SSN</th>
> <th>Last Name</th>
> <th>First Name</th>
> <th>Phone</th>
> </tr>
> <%
> Dim rs
> Set rs = GetRecordset()
> do While Not rs.EOF
> %>
> <tr>
> <td><%=rs("au_id")%></td>
> <td><%=rs("au_lname")%></td>
> <td><%=rs("au_fname")%></td>
> <td><%=rs("phone")%></td>
> </tr>
> <%
> rs.MoveNext
> loop
> rs.Close
> Set rs = Nothing
> %>
> </table>
> </body>
> </html>
> : Also, are you using a common connection string across all three pages
> (eg
> is
> : the connection string stored in an include file, or in the registry?)
> Within the file itself. No include. No reg. See above.
> : Or
> : have you hard coded the connection string into all three pages?
> Yes, MSFT did.
> : If the
> : latter, then I would suggest moving the connection string to a central
> : include file (or application variable), and using that on all three
> pages
> to
> : ensure that you aren't getting a dud connection string on one of the
> pages.
> I copied the connection from StraightXML1.asp and pasted it over the one
> in
> StraightASP.asp. Same issue.
> I'm not new to this stuff. I experienced something out of the norm and
> was
> wondering if anyone else has also experienced this.
> Here is the code from StraightXML1.asp: (password removed)
> <%@. Language=VBScript %>
> <!-- #INCLUDE FILE="inc/xmlutil.asp" -->
> <%
> Function GetRecordset()
> Dim cnn
> Set cnn = CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data Source=localhost",
> "sa", ""
> Set GetRecordset = cnn.Execute("select * from authors")
> End Function
> %>
> <html>
> <head>
> <title>Straight XML Approach</title>
> <link rel="stylesheet" type="text/css" href="http://links.10026.com/?link=default.css">
> </head>
> <body>
> <% = TransformXML( RecordsetToXMLDoc(GetRecordset(), "author"),
> "xsl/authors.xsl" ) %>
> </body>
> </html>
> Everything came from here:
> http://msdn.microsoft.com/library/de...ondasp/toc.asp
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
> WSH 5.6 Documentation -
> http://msdn.microsoft.com/downloads/list/webdev.asp
> MSDN Library - http://msdn.microsoft.com/library/default.asp
>
|||"Ken Schaefer" wrote in message
news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
:
: "Roland Hall" <nobody@.nowhere> wrote in message
: news:uaDiFu95EHA.1596@.tk2msftngp13.phx.gbl...
: > "Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
: > news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
: > : Hi,
: > :
: > : Your browser is caching the credentials, and resending them for each
: > : subsequent page request to that server (until the browser is closed).
: >
: > The cache was cleared and the browser was closed and still it failed but
I
: > was prompted each time for credentials but only for the first page.
:
: I think you are completely missing the point, or I am not understanding
what
: you are saying.
:
: When the browser connects to "firstpage.asp", it first attempts to connect
: anonymously (sending no credentials). The webserver sends back a 401
header
: (Access Denied) and lists available authentication mechanisms via
: WWW-Authenticate HTTP headers.
:
: The browser then prompts the user to supply credentials (or, in the case
of
: IE, if the website is in the local intranet security zone, will attempt to
: send the current logged on user's credentials automatically). The user
: supplies their credentials, and the browser sends them to the server. If
the
: credentials are acceptable, the server sends back a 200 OK status.
:
: The browser then continues to reuse those credentials for subsequent
: requests for pages on that site. This is why (I suspect) you are not
seeing
: credential prompts on subsequent pages. You can not "clear" this cache per
: se. Instead, when you close the browser completely, the browser "forgets"
: the credentials it has been using. When you open the browser again (well,
: technically you need to start a new iexplore.exe process) and hit the site
: again, you will once again be prompted for credentials
:
: > : In terms of the login failure - can we see the exact error message
that
: > : you are seeing please?
: >
: > Error Type:
: > Microsoft OLE DB Provider for SQL Server (0x80040E4D)
: > Login failed for user 'sa'.
: > /msft/StraightASP.asp, line 7
:
: Well, for some reason SQL Server thinks that the password being supplied
by
: your ADO connection object is not correct for the "sa" account.
:
: This doesn't have anything to do with IIS caching a logon (for the
purposes
: impersonating an NT account for processing an ASP page), or with HTTP
: authentication between the client and server, which is what your original
: post seemed to be talking about. This seems to be an authentication issue
: between your ADO components and SQL Server.
:
: That said, if you've cut-n-pasted the code from one working page to
another
: page, that does seem "out of the norm". Just as a trial, could you put the
: connection string into an include file, and include that into your two
: pages, and then use the connection string from the include file? That
would
: probably eliminate some odd non-printable character (or something similar)
: being in the connection string. Both pages should either work, or not
work.
Ken...
I provided the link at MSFT where I got the files. You can see for yourself
there are no non-printable characters. Again, this is not my code. Also, I
cannot reproduce the problem now that the server has been rebooted. You're
also apparently excluding the other particulars.
If the sa logon fails, it should fail no matter which file is trying to
authenticate as long as that code is the same. Renaming the file, makes it
work. Renaming it back to the original name makes it fail once again.
However, nothing fails now that the server has been rebooted. Explain to me
how the code has anything to do with that. Also, since event viewer shows
nothing, where can I get information regarding the failed logon?
TIA...
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
|||From what you have described, I am at a loss as to a
probable cause/explaination.
One thing I noticed in your reply was mention that you
understood browsing client authentications as
> It was my understanding that Windows would try to authenticate with the
> current credentials and only after failing would prompt for credentials.
Keep in mind that whether the IE browser will supply the Windows
credentials or not is configurable in the IE security settings.
This however does not seem to be a factor in what you have
described as some of the cross-test seem to rule this out.
Roger Abell
Microsoft MVP (Windows Security)
MCSE (W2k3,W2k,Nt4) MCDBA
"Roland Hall" <nobody@.nowhere> wrote in message
news:OZdeOn95EHA.1452@.TK2MSFTNGP11.phx.gbl...
> "Roger Abell" <mvpNOSpam@.asu.edu> wrote in message
> news:%23fCBKGh5EHA.2592@.TK2MSFTNGP09.phx.gbl...
> : Why did you reference domain1 and domain2 ?
> : > domain1\administrator - domain2\administrator both have the same
> password.
> : What is their relationship to this?
> : It appears that the IIS and the SQL are installed on one machine ?
> : Is the XP used to access the asp pages allowed to provide "behind
> : the scenes" windows authentication information?
> : You are saying that the connection string used for ado is
> : identical on all three pages ?
> Domain1:
> Win2K Adv. Server
> DC
> AD
> DNS
> MS SQL 2K
> IIS
> Domain2:
> XP Pro SP2
> The folder on IIS holds all 3 .asp files.
> All 3 connect to MS SQL using the same exact code for the connection and
> same authentication.
> On the server I was logged in as domain1\administrator.
> On XP, I was logged in as domain2\administrator.
> When I tried to access StraightASP.asp, I was asked for credentials. I
> typed in domain1\administrator and that password which is the same
password
> used for the domain2\administrator. IIS, on that web server, uses Basic
and
> Integrated authentication only. MS SQL was set for SQL and Windows
> authentication.
> Only access the first page prompted me for credentials and then told me
> access was denied.
> The pages that used XML did not. The only difference between those files
is
> CSS in StraightXML1.asp and XSL in StraightXML2.asp.
> It was my understanding that Windows would try to authenticate with the
> current credentials and only after failing would prompt for credentials.
I
> also assumed that domain1\administrator would be treated differently than
> domain2\administrator even though they had the same password. It appeared
> that part worked correctly by requesting credentials but then failed to
> authenticate. Only a reboot of the server itself cleared the problem and
> ONLY the first file failed. The two others worked fine. And, when I
> renamed the first file, it also worked. Rename is back, it fails. Rename
> it to something else again, it works.
> So, where is it being cached by filename, failing authentication, but
> allowing it for other files with the same code? I restarted IIS, SQL and
> cleared IE cache and closed all IE windows and started fresh and still
only
> a reboot of the server cleared the apparent caching issue.
> I've never seen this issue and other than what I listed before the reboot,
I
> cannot reproduce the error.
> I knew it was not IE, but I tested all that I thought could possibly be
> involved. IE from the server and from the workstation both failed with
the
> same file and the other two worked on both. It apparently was happening
at
> the server level but I cannot prove it.
> This is the connection string in all 3 files: (The password is not blank.
> It has just been removed from here.)
> Function GetRecordset()
> Dim cnn
> Set cnn = CreateObject("ADODB.Connection")
> cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=PUBS;Data
Source=localhost",
> "sa", ""
> Set GetRecordset = cnn.Execute("select * from authors")
> End Function
> The only thing different in the first file, when run the first time, was
an
> error in the file I received from MSFT. CatalogPUBS should have been
> Catalog=PUBS. All I did was add an the = sign and the password.
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
> WSH 5.6 Documentation -
http://msdn.microsoft.com/downloads/list/webdev.asp
> MSDN Library - http://msdn.microsoft.com/library/default.asp
>
|||"Roland Hall" <nobody@.nowhere> wrote in message
news:OqNjkx%235EHA.3736@.TK2MSFTNGP10.phx.gbl...
> "Ken Schaefer" wrote in message
> news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
> :
> : "Roland Hall" <nobody@.nowhere> wrote in message
> : news:uaDiFu95EHA.1596@.tk2msftngp13.phx.gbl...
> : > "Ken Schaefer" <kenREMOVE@.THISadopenstatic.com> wrote in message
> : > news:uBfCwRi5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> : > : Hi,
> : > :
> : > : Your browser is caching the credentials, and resending them for each
> : > : subsequent page request to that server (until the browser is
closed).
> : >
> : > The cache was cleared and the browser was closed and still it failed
but
> I
> : > was prompted each time for credentials but only for the first page.
> :
> : I think you are completely missing the point, or I am not understanding
> what
> : you are saying.
> :
> : When the browser connects to "firstpage.asp", it first attempts to
connect
> : anonymously (sending no credentials). The webserver sends back a 401
> header
> : (Access Denied) and lists available authentication mechanisms via
> : WWW-Authenticate HTTP headers.
> :
> : The browser then prompts the user to supply credentials (or, in the case
> of
> : IE, if the website is in the local intranet security zone, will attempt
to
> : send the current logged on user's credentials automatically). The user
> : supplies their credentials, and the browser sends them to the server. If
> the
> : credentials are acceptable, the server sends back a 200 OK status.
> :
> : The browser then continues to reuse those credentials for subsequent
> : requests for pages on that site. This is why (I suspect) you are not
> seeing
> : credential prompts on subsequent pages. You can not "clear" this cache
per
> : se. Instead, when you close the browser completely, the browser
"forgets"
> : the credentials it has been using. When you open the browser again
(well,
> : technically you need to start a new iexplore.exe process) and hit the
site
> : again, you will once again be prompted for credentials
> :
> : > : In terms of the login failure - can we see the exact error message
> that
> : > : you are seeing please?
> : >
> : > Error Type:
> : > Microsoft OLE DB Provider for SQL Server (0x80040E4D)
> : > Login failed for user 'sa'.
> : > /msft/StraightASP.asp, line 7
> :
> : Well, for some reason SQL Server thinks that the password being supplied
> by
> : your ADO connection object is not correct for the "sa" account.
> :
> : This doesn't have anything to do with IIS caching a logon (for the
> purposes
> : impersonating an NT account for processing an ASP page), or with HTTP
> : authentication between the client and server, which is what your
original
> : post seemed to be talking about. This seems to be an authentication
issue
> : between your ADO components and SQL Server.
> :
> : That said, if you've cut-n-pasted the code from one working page to
> another
> : page, that does seem "out of the norm". Just as a trial, could you put
the
> : connection string into an include file, and include that into your two
> : pages, and then use the connection string from the include file? That
> would
> : probably eliminate some odd non-printable character (or something
similar)
> : being in the connection string. Both pages should either work, or not
> work.
> Ken...
> I provided the link at MSFT where I got the files. You can see for
yourself
> there are no non-printable characters. Again, this is not my code. Also,
I
> cannot reproduce the problem now that the server has been rebooted.
You're
> also apparently excluding the other particulars.
> If the sa logon fails, it should fail no matter which file is trying to
> authenticate as long as that code is the same. Renaming the file, makes
it
> work. Renaming it back to the original name makes it fail once again.
This is the most perplexing part. And, as you mentioned in post of the
other
subthread, you could close all IE processes, start again, and see the same
pattern (prior to the rebooting). That the file name should be so important
is not simple to understand, and makes it sound as if there was a corruption
in IIS's caching of the file involved.
Roger

> However, nothing fails now that the server has been rebooted. Explain to
me
> how the code has anything to do with that. Also, since event viewer shows
> nothing, where can I get information regarding the failed logon?
> TIA...
> --
> Roland Hall
> /* This information is distributed in the hope that it will be useful, but
> without any warranty; without even the implied warranty of merchantability
> or fitness for a particular purpose. */
> Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
> WSH 5.6 Documentation -
http://msdn.microsoft.com/downloads/list/webdev.asp
> MSDN Library - http://msdn.microsoft.com/library/default.asp
>
|||"Roland Hall" <nobody@.nowhere> wrote in message
news:OqNjkx%235EHA.3736@.TK2MSFTNGP10.phx.gbl...
> "Ken Schaefer" wrote in message
> news:Oo7Cj695EHA.2608@.TK2MSFTNGP10.phx.gbl...
> :

> I provided the link at MSFT where I got the files. You can see for
> yourself
> there are no non-printable characters. Again, this is not my code. Also,
> I
> cannot reproduce the problem now that the server has been rebooted.
> You're
> also apparently excluding the other particulars.
I just wanted to address the points you raised by using Basic / Integrated
Authentication with IIS, and also with having to authenticate on the "first"
page versus subsequent pages, and about clearing the cache. None of those
things should have anything to do with the issue you are seeing. I was not
trying to "exclude the other particulars", but rather explain why I do not
think some of the issues raised where not relevant.
I agree that the situation you are in is a bit odd. I'm struggling to think
of a reason why you might have been seeing the behaviour that you were. As
Roger Abell has mentioned, perhaps the in-memory cached copy of the ASP page
was somehow corrupt. Rebooting the box would have completely cleared that
cache.
Cheers
Ken