Classic ASP + SQL Server: accessing SQL Server from web server

I've been through a half dozen different suggested solutions for this
problem, but can't seem to find anything that works.

I've got a web server (Windows Server 2003, IIS 6.0, SQL Server 7.0)
and a database server (Windows Server 2003, SQL Server 2000). We're
using "Classic ASP" to serve our Intranet pages and we now need to
connect to the databases on the the database server.

We're dynamically building pages based on the Active Directory user
account accessing the Intranet. On the older pages which connect to the
SQL Server 7 databases (hosted on the same machine as IIS) we've had no
problem. Now that we need to connect to the SQL Server 2000 databases
on the other machine, we're getting the dreaded "[Microsoft][ODBC SQL
Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection." error.

I can enable Basic Authentication, but then the users have to log on to
the Intranet because Request.ServerVariables("Auth_User") doesn't work.
I can enable Integrated Windows Authentication but then we get the
"Login failed for user '(null)'" error and can't access the remote
databases.

Is there one solution that will allow access to the remote SQL Server
databases and also allow use of Request.ServerVariables("Auth_User")?

Re: Classic ASP + SQL Server: accessing SQL Server from web server

ste3ve@gmail.com wrote:
> I've been through a half dozen different suggested solutions for this
> problem, but can't seem to find anything that works.
>
> I've got a web server (Windows Server 2003, IIS 6.0, SQL Server 7.0)
> and a database server (Windows Server 2003, SQL Server 2000). We're
> using "Classic ASP" to serve our Intranet pages and we now need to
> connect to the databases on the the database server.
>
> We're dynamically building pages based on the Active Directory user
> account accessing the Intranet. On the older pages which connect to
> the SQL Server 7 databases (hosted on the same machine as IIS) we've
> had no problem. Now that we need to connect to the SQL Server 2000
> databases on the other machine, we're getting the dreaded
> "[Microsoft][ODBC

To start off: stop using ODBC:
http://www.aspfaq.com/show.asp?id=2126


> SQL Server Driver][SQL Server]Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server
> connection." error.
>
> I can enable Basic Authentication, but then the users have to log on
> to the Intranet because Request.ServerVariables("Auth_User") doesn't
> work. I can enable Integrated Windows Authentication but then we get
> the "Login failed for user '(null)'" error and can't access the remote
> databases.
>
> Is there one solution that will allow access to the remote SQL Server
> databases and also allow use of Request.ServerVariables("Auth_User")?


What have you tried? I don't know how to answer this without simply
directiing you to the above page. Maybe this page will help as well:
http://support.microsoft.com/default.aspx/kb/247931


I typically create a sql login that has the privileges required by the
application and use that login to connect to sql server. However, it
should be possible to use windows authentication. You have to disable
Anonymous access on the website and use the correct connection string.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Classic ASP + SQL Server: accessing SQL Server from web server

ste3ve@gmail.com wrote:
> I've been through a half dozen different suggested solutions for this
> problem, but can't seem to find anything that works.
>
> I've got a web server (Windows Server 2003, IIS 6.0, SQL Server 7.0)
> and a database server (Windows Server 2003, SQL Server 2000). We're
> using "Classic ASP" to serve our Intranet pages and we now need to
> connect to the databases on the the database server.
>
> We're dynamically building pages based on the Active Directory user
> account accessing the Intranet. On the older pages which connect to
> the SQL Server 7 databases (hosted on the same machine as IIS) we've
> had no problem. Now that we need to connect to the SQL Server 2000
> databases on the other machine, we're getting the dreaded
> "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server
> connection." error.
>
> I can enable Basic Authentication, but then the users have to log on
> to the Intranet because Request.ServerVariables("Auth_User") doesn't
> work. I can enable Integrated Windows Authentication but then we get
> the "Login failed for user '(null)'" error and can't access the remote
> databases.
>
> Is there one solution that will allow access to the remote SQL Server
> databases and also allow use of Request.ServerVariables("Auth_User")?

Sorry, but this is not possible. According to
http://support.microsoft.com/default.aspx/kb/247931,

"To configure IIS for Windows NT authentication, you cannot use Windows
NT Challenge\Response (NTLM) authentication"

You have to use Basic Authentication


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Classic ASP + SQL Server: accessing SQL Server from web server

Bob Barrows [MVP] wrote:
> ste3ve@gmail.com wrote:
> > I've been through a half dozen different suggested solutions for this
> > problem, but can't seem to find anything that works.
> >
> > I've got a web server (Windows Server 2003, IIS 6.0, SQL Server 7.0)
> > and a database server (Windows Server 2003, SQL Server 2000). We're
> > using "Classic ASP" to serve our Intranet pages and we now need to
> > connect to the databases on the the database server.
> >
> > We're dynamically building pages based on the Active Directory user
> > account accessing the Intranet. On the older pages which connect to
> > the SQL Server 7 databases (hosted on the same machine as IIS) we've
> > had no problem. Now that we need to connect to the SQL Server 2000
> > databases on the other machine, we're getting the dreaded
> > "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> > '(null)'. Reason: Not associated with a trusted SQL Server
> > connection." error.
> >
> > I can enable Basic Authentication, but then the users have to log on
> > to the Intranet because Request.ServerVariables("Auth_User") doesn't
> > work. I can enable Integrated Windows Authentication but then we get
> > the "Login failed for user '(null)'" error and can't access the remote
> > databases.
> >
> > Is there one solution that will allow access to the remote SQL Server
> > databases and also allow use of Request.ServerVariables("Auth_User")?
>
> Sorry, but this is not possible. According to
> http://support.microsoft.com/default.aspx/kb/247931,
>
> "To configure IIS for Windows NT authentication, you cannot use Windows
> NT Challenge\Response (NTLM) authentication"
>
> You have to use Basic Authentication
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Bob, thanks for the pointers. Using OLEDB instead of ODBC has given me
a method to solve my problem.

We are using NTLM so we pick up the Auth_User - that's then referenced
in a local SQL Server database to determine permissions on the ASP
pages. All users connect to the remote SQL Server using a common login
via OLEDB.