Web Server Can"t Connect to Sql Server

Hi,

I have two seperate production machines that I'm trying to get to play
nicely together.
This setup has worked for years before - until recently bother
machines were reformatted, and reinstalled fresh. I know I'm missing a
simple setting here somewhere...

I have a Win 2003 IIS 6.0 box running as web server, and a Win 2003
IIS 6.0 box running as a web server with SQL 2000 Server installed.

I can write ASP scripts to access the SQL Server but they only work
from the box running the SQL Server.

If I write ASP scripts to run from the first Win 2003 Web server,
trying to connect to the second box actually running the SQL Server it
gives an error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not
exist or access denied.

If I open up the ODBC Admin on the first Web Server box, and create a
System DSN, and supply it with a SQL Login (the SQL Server is set to
mixed authentication mode and not strictly windows authentication)
the login and test say they complete successfully. However, whenever I
run the ASP script, it errors out.

Example of Connection String that works on box running SQL but not on
first box that is trying to connect to SQL:

strSQL = "SELECT TOP 10 * FROM MAIN ORDER BY [Key] DESC;"

'Create and Open Connection Object
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString =
"dsn=**********;uid=*********;pwd=********;"
'System DSN is established on the non-sql machine that this
script is running from
'uid & pwd are SQL accounts.
objConnection.Open

set RsList = objConnection.Execute(strSQL)


I'm sure I've left out vital peices of info here- so please let me
know what you need to know to offer advice.

Much thanks!
-xx75vulcan

Re: Web Server Can"t Connect to Sql Server

xx75vulcan (xx75vulcan@gmail.com) writes:
> If I write ASP scripts to run from the first Win 2003 Web server,
> trying to connect to the second box actually running the SQL Server it
> gives an error:
> Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not
> exist or access denied.

Maybe this article can give some clues?
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q328 306.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Web Server Can"t Connect to Sql Server

On Jun 18, 4:34 pm, Erland Sommarskog wrote:
> xx75vulcan (xx75vul...@gmail.com) writes:
> > If I write ASP scripts to run from the first Win 2003 Web server,
> > trying to connect to the second box actually running the SQL Server it
> > gives an error:
> > Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> > [Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not
> > exist or access denied.
>
> Maybe this article can give some clues?http://support.microsoft.com/default.aspx?scid=KB;EN-U S;q328306.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

I've read that particular article somewhere before, and I believe I
have the settings/common problems correct.
I'm wondering, do I need to setup a "Linked Server" between the SQL
box and the web box not running SQL?

It appears that "linked servers" provide OLE DB providers and OLE DB
data sources?

Re: Web Server Can"t Connect to Sql Server

ok I just noticed an event on the server (hosting SQL) event log when
attempting to connect from a seperate box.

Error:
An anonymous session connected from FREUDIANA has attempted to open an
LSA policy handle on this machine. The attempt was rejected with
STATUS_ACCESS_DENIED to prevent leaking security sensitive information
to the anonymous caller.

Upon further investigation, lead me to this MS KB: http://support.microsoft.com/kb/839569

Stating "Instead of allowing the anonymous connections to your
instance of SQL Server, you can grant the required access to a
specific SQL Server account and pass the logon credentials for the SQL
Server account in the connection string in the ASP.NET page. Using SQL
Server authentication avoids the anonymous connection attempts to the
instance of SQL Server and is more secure."

I'm passing the SQL login credentials in my connecton string, and the
ODBC manager specifies a SQL account -- why does it tell me it's
attempting an annonymous connection?!?!?!

Re: Web Server Can"t Connect to Sql Server

On Jun 19, 8:40 am, xx75vulcan wrote:
> ok I just noticed an event on the server (hosting SQL) event log when
> attempting to connect from a seperate box.
>
> Error:
> An anonymous session connected from FREUDIANA has attempted to open an
> LSA policy handle on this machine. The attempt was rejected with
> STATUS_ACCESS_DENIED to prevent leaking security sensitive information
> to the anonymous caller.
>
> Upon further investigation, lead me to this MS KB:http://support.microsoft.com/kb/839569
>
> Stating "Instead of allowing the anonymous connections to your
> instance of SQL Server, you can grant the required access to a
> specific SQL Server account and pass the logon credentials for the SQL
> Server account in the connection string in the ASP.NET page. Using SQL
> Server authentication avoids the anonymous connection attempts to the
> instance of SQL Server and is more secure."
>
> I'm passing the SQL login credentials in my connecton string, and the
> ODBC manager specifies a SQL account -- why does it tell me it's
> attempting an annonymous connection?!?!?!


Also noticed the SQL server box is running ODBC SQL Driver
2000.85.1022.00 and the web box (that no asp page can connect through)
is running ODBC SQL Driver 2000.86.1830.00. do they need to be the
same driver version on both boxes?

Re: Web Server Can"t Connect to Sql Server

On Jun 19, 10:27 am, xx75vulcan wrote:
> On Jun 19, 8:40 am, xx75vulcan wrote:
>
>
>
> > ok I just noticed an event on the server (hosting SQL) event log when
> > attempting to connect from a seperate box.
>
> > Error:
> > An anonymous session connected from FREUDIANA has attempted to open an
> > LSA policy handle on this machine. The attempt was rejected with
> > STATUS_ACCESS_DENIED to prevent leaking security sensitive information
> > to the anonymous caller.
>
> > Upon further investigation, lead me to this MS KB:http://support.microsoft.com/kb/839569
>
> > Stating "Instead of allowing the anonymous connections to your
> > instance of SQL Server, you can grant the required access to a
> > specific SQL Server account and pass the logon credentials for the SQL
> > Server account in the connection string in the ASP.NET page. Using SQL
> > Server authentication avoids the anonymous connection attempts to the
> > instance of SQL Server and is more secure."
>
> > I'm passing the SQL login credentials in my connecton string, and the
> > ODBC manager specifies a SQL account -- why does it tell me it's
> > attempting an annonymous connection?!?!?!
>
> Also noticed the SQL server box is running ODBC SQL Driver
> 2000.85.1022.00 and the web box (that no asp page can connect through)
> is running ODBC SQL Driver 2000.86.1830.00. do they need to be the
> same driver version on both boxes?

ANSWER:
I figured it out, but wanted to post for anyone else experiencing this
issue.
While this was a very very odd solution for me, it might just work for
you.

On the second box - that hosts SQL Server, IIS was running, and the
default.htm page - - for whatever reason, included a redirect to the
homepage on the primary server.

I can only assume the connection string (pointing to the sql box) was
being redirected to the primary server - where there was no sql
server.

chris

Re: Web Server Can"t Connect to Sql Server

xx75vulcan (xx75vulcan@gmail.com) writes:
> I've read that particular article somewhere before, and I believe I
> have the settings/common problems correct.
> I'm wondering, do I need to setup a "Linked Server" between the SQL
> box and the web box not running SQL?

I see that you have sorted out your issue (and as I understand, the
answer was in the domain of web servers, where I know next to nothing),
but I still wanted to answer this.

No, linked servers is nothing about that. When you set up a linked server
in SQL Server, is when you want to access some other data source from
SQL Server. That data source could be another SQL Server, it could be
Oracle, it could be Excel. It could be anything that could be reached
with an OLE DB provider (and indirectly an ODBC driver). But linked servers
is nothing about access into SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx