Help - IIS fails to access MSSQL on a nother computer

Hello all.

I am having a pretty serious problem and I tried almost every thing (I
know…) but still with no success.
There are 2 computer on a workgroup (lets call them A and B). On A there is
an MSSQL Server 2000 with windows and QSL mixed authentication running on top
of Windows 2003 machine. On B I set up an ODBC as system DNS that points to
one of the databases exists on the MSSQQL sever on A using an SQL
authentication (user & password). The connection is working (the “test
connection” step at the end of the wizard is successful) and I can access the
database using SQL Server Enterprise Manager and the SQL Query Analyzer. I
also using this DSN with other desktop application running from B. BUT when
I’m running a web service server on the IIS that is located on B using the
same ODBC connection and passing the user name and the password I get the
error: “Sql Server does not exist or access denied”. (Machine B is running
Windows XP OS)

Why is that and how can I solve it?

Thanks for your answers.

My email is: assaf@nipendo.com

Stern Assaf.

Re: Help - IIS fails to access MSSQL on a nother computer

Stren Assaf wrote:
> Hello all.
>
> I am having a pretty serious problem and I tried almost every thing (I
> know.) but still with no success.
> There are 2 computer on a workgroup (lets call them A and B). On A
> there is an MSSQL Server 2000 with windows and QSL mixed
> authentication running on top of Windows 2003 machine. On B I set up
> an ODBC as system DNS that points to one of the databases exists on
> the MSSQQL sever on A using an SQL authentication (user & password).
> The connection is working (the "test connection" step at the end of
> the wizard is successful) and I can access the database using SQL
> Server Enterprise Manager and the SQL Query Analyzer. I also using
> this DSN with other desktop application running from B. BUT when I'm
> running a web service server on the IIS that is located on B using
> the same ODBC connection and passing the user name and the password I
> get the error: "Sql Server does not exist or access denied". (Machine
> B is running Windows XP OS)
>
> Why is that and how can I solve it?


Your answer will be found in this article:
http://www.aspfaq.com/show.asp?id=2009

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Help - IIS fails to access MSSQL on a nother computer

I went over the artical but still its not the case.
In my case other desktop applications on the computer are successfuly
connect to the remote SQL server using the same ODBC DSN while access from
the iis fails. There is some kind of permisions problem that i do not get.

Thanks any how Bob.

"Bob Barrows [MVP]" wrote:

> Stren Assaf wrote:
> > Hello all.
> >
> > I am having a pretty serious problem and I tried almost every thing (I
> > know.) but still with no success.
> > There are 2 computer on a workgroup (lets call them A and B). On A
> > there is an MSSQL Server 2000 with windows and QSL mixed
> > authentication running on top of Windows 2003 machine. On B I set up
> > an ODBC as system DNS that points to one of the databases exists on
> > the MSSQQL sever on A using an SQL authentication (user & password).
> > The connection is working (the "test connection" step at the end of
> > the wizard is successful) and I can access the database using SQL
> > Server Enterprise Manager and the SQL Query Analyzer. I also using
> > this DSN with other desktop application running from B. BUT when I'm
> > running a web service server on the IIS that is located on B using
> > the same ODBC connection and passing the user name and the password I
> > get the error: "Sql Server does not exist or access denied". (Machine
> > B is running Windows XP OS)
> >
> > Why is that and how can I solve it?
>
>
> Your answer will be found in this article:
> http://www.aspfaq.com/show.asp?id=2009
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: Help - IIS fails to access MSSQL on a nother computer

You're failing to see the part about the IUSR account being relevant. The
users logged into the desktop applictions can successfully connect. ASP runs
under a different user account (the IUSR_machinename account). There are
some KB articles listed that discuss troubleshooting connectivity problems
between IIS and SQL Server. Did you read them?

FWIW, you should not be using ODBC. Start with using the native OLE DB
Provider for SQL Server (SQLOLEDB - see www.connectionstrings.com). If you
still have problems, check out the MS KB articles.

Bob Barrows

Stren Assaf wrote:
> I went over the artical but still its not the case.
> In my case other desktop applications on the computer are successfuly
> connect to the remote SQL server using the same ODBC DSN while access
> from the iis fails. There is some kind of permisions problem that i
> do not get.
>
> Thanks any how Bob.
>
> "Bob Barrows [MVP]" wrote:
>
>> Stren Assaf wrote:
>>> Hello all.
>>>
>>> I am having a pretty serious problem and I tried almost every thing
>>> (I know.) but still with no success.
>>> There are 2 computer on a workgroup (lets call them A and B). On A
>>> there is an MSSQL Server 2000 with windows and QSL mixed
>>> authentication running on top of Windows 2003 machine. On B I set up
>>> an ODBC as system DNS that points to one of the databases exists on
>>> the MSSQQL sever on A using an SQL authentication (user & password).
>>> The connection is working (the "test connection" step at the end of
>>> the wizard is successful) and I can access the database using SQL
>>> Server Enterprise Manager and the SQL Query Analyzer. I also using
>>> this DSN with other desktop application running from B. BUT when I'm
>>> running a web service server on the IIS that is located on B using
>>> the same ODBC connection and passing the user name and the password
>>> I get the error: "Sql Server does not exist or access denied".
>>> (Machine B is running Windows XP OS)
>>>
>>> Why is that and how can I solve it?
>>
>>
>> Your answer will be found in this article:
>> http://www.aspfaq.com/show.asp?id=2009
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Help - IIS fails to access MSSQL on a nother computer

First of all thanks Bob for your replys.
Second, I did read the part regarding the IUSER_machinename acount and to
see if this is realy the problem i configured the IIS to use the same acount
as the one I am using with the regular desktop applications and it still
failed... From waht i raed in the articals and from what you wrote it should
have worked.

"Bob Barrows [MVP]" wrote:

> You're failing to see the part about the IUSR account being relevant. The
> users logged into the desktop applictions can successfully connect. ASP runs
> under a different user account (the IUSR_machinename account). There are
> some KB articles listed that discuss troubleshooting connectivity problems
> between IIS and SQL Server. Did you read them?
>
> FWIW, you should not be using ODBC. Start with using the native OLE DB
> Provider for SQL Server (SQLOLEDB - see www.connectionstrings.com). If you
> still have problems, check out the MS KB articles.
>
> Bob Barrows
>
> Stren Assaf wrote:
> > I went over the artical but still its not the case.
> > In my case other desktop applications on the computer are successfuly
> > connect to the remote SQL server using the same ODBC DSN while access
> > from the iis fails. There is some kind of permisions problem that i
> > do not get.
> >
> > Thanks any how Bob.
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> Stren Assaf wrote:
> >>> Hello all.
> >>>
> >>> I am having a pretty serious problem and I tried almost every thing
> >>> (I know.) but still with no success.
> >>> There are 2 computer on a workgroup (lets call them A and B). On A
> >>> there is an MSSQL Server 2000 with windows and QSL mixed
> >>> authentication running on top of Windows 2003 machine. On B I set up
> >>> an ODBC as system DNS that points to one of the databases exists on
> >>> the MSSQQL sever on A using an SQL authentication (user & password).
> >>> The connection is working (the "test connection" step at the end of
> >>> the wizard is successful) and I can access the database using SQL
> >>> Server Enterprise Manager and the SQL Query Analyzer. I also using
> >>> this DSN with other desktop application running from B. BUT when I'm
> >>> running a web service server on the IIS that is located on B using
> >>> the same ODBC connection and passing the user name and the password
> >>> I get the error: "Sql Server does not exist or access denied".
> >>> (Machine B is running Windows XP OS)
> >>>
> >>> Why is that and how can I solve it?
> >>
> >>
> >> Your answer will be found in this article:
> >> http://www.aspfaq.com/show.asp?id=2009
> >>
> >> Bob Barrows
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: Help - IIS fails to access MSSQL on a nother computer

"Stren Assaf" wrote in message
news:7C6D15EB-5112-4764-BDDD-BE341BA3A210@microsoft.com...
[synopsis begin]
I'm getting a "Server does not exist or access denied" error in ASP when I
attempt to connect to an SQL2K database via an ODBC DSN that uses SQL
Authentication. I am able to connect successfully in several desktop
applications using the same DSN.
[synopsis end]

Please post a relevant code snippet. Specifically, what does the connection
string look like? Does it look like this?:

http://www.carlprothman.net/Default.aspx?tabid=89#DSN

Obviously, you'll want to redact the username/password when you post. It's
probably not a IUSR_ permissions issue since you said you're
using SQL Authentication. What type of DSN is it, system, user or file? Did
you try a native OLE-DB connection like Bob suggested?

Re: Help - IIS fails to access MSSQL on a nother computer

On Wed, 8 Jun 2005 04:23:03 -0700, "Stren Assaf"
wrote:

>I am having a pretty serious problem and I tried almost every thing (I
>know…) but still with no success.
>There are 2 computer on a workgroup (lets call them A and B). On A there is
>an MSSQL Server 2000 with windows and QSL mixed authentication running on top
>of Windows 2003 machine. On B I set up an ODBC as system DNS that points to
>one of the databases exists on the MSSQQL sever on A using an SQL
>authentication (user & password). The connection is working (the “test
>connection” step at the end of the wizard is successful) and I can access the
>database using SQL Server Enterprise Manager and the SQL Query Analyzer. I
>also using this DSN with other desktop application running from B. BUT when
>I’m running a web service server on the IIS that is located on B using the
>same ODBC connection and passing the user name and the password I get the
>error: “Sql Server does not exist or access denied”. (Machine B is running
>Windows XP OS)
>
>Why is that and how can I solve it?

Take a look at:

HOWTO: IIS and SQL Server on Separate Machines with Trusted
Connection:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q176 379

Jeff

Re: Help - IIS fails to access MSSQL on a nother computer

Chris, thanks for your answer.

I did try veriuse connection string such as: "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
and others but still with the same result. I did not check other connection
options because I'm obligate to use the Odbc DSN. If it any help - in my code
I am using microsoft.OdbcConnection class to implement the connection.

Any more ideas ?

"Chris Hohmann" wrote:

> "Stren Assaf" wrote in message
> news:7C6D15EB-5112-4764-BDDD-BE341BA3A210@microsoft.com...
> [synopsis begin]
> I'm getting a "Server does not exist or access denied" error in ASP when I
> attempt to connect to an SQL2K database via an ODBC DSN that uses SQL
> Authentication. I am able to connect successfully in several desktop
> applications using the same DSN.
> [synopsis end]
>
> Please post a relevant code snippet. Specifically, what does the connection
> string look like? Does it look like this?:
>
> http://www.carlprothman.net/Default.aspx?tabid=89#DSN
>
> Obviously, you'll want to redact the username/password when you post. It's
> probably not a IUSR_ permissions issue since you said you're
> using SQL Authentication. What type of DSN is it, system, user or file? Did
> you try a native OLE-DB connection like Bob suggested?
>
>
>

Re: Help - IIS fails to access MSSQL on a nother computer

Jeff, Thanks for your reply.

However i did change the web service to support basic authentication but
still with the same unfortunate result.

Any more ideas?


"Jeff Cochran" wrote:

> On Wed, 8 Jun 2005 04:23:03 -0700, "Stren Assaf"
> wrote:
>
> >I am having a pretty serious problem and I tried almost every thing (I
> >know…) but still with no success.
> >There are 2 computer on a workgroup (lets call them A and B). On A there is
> >an MSSQL Server 2000 with windows and QSL mixed authentication running on top
> >of Windows 2003 machine. On B I set up an ODBC as system DNS that points to
> >one of the databases exists on the MSSQQL sever on A using an SQL
> >authentication (user & password). The connection is working (the “test
> >connection” step at the end of the wizard is successful) and I can access the
> >database using SQL Server Enterprise Manager and the SQL Query Analyzer. I
> >also using this DSN with other desktop application running from B. BUT when
> >I’m running a web service server on the IIS that is located on B using the
> >same ODBC connection and passing the user name and the password I get the
> >error: “Sql Server does not exist or access denied”. (Machine B is running
> >Windows XP OS)
> >
> >Why is that and how can I solve it?
>
> Take a look at:
>
> HOWTO: IIS and SQL Server on Separate Machines with Trusted
> Connection:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;q176 379
>
> Jeff
>

Re: Help - IIS fails to access MSSQL on a nother computer

"Stren Assaf" wrote in message
news:26A34F42-5B05-45B8-AE3A-60C65373BC61@microsoft.com...
> "Chris Hohmann" wrote:
>> "Stren Assaf" wrote in message
>> news:7C6D15EB-5112-4764-BDDD-BE341BA3A210@microsoft.com...
>> [synopsis begin]
>> I'm getting a "Server does not exist or access denied" error in ASP when
>> I
>> attempt to connect to an SQL2K database via an ODBC DSN that uses SQL
>> Authentication. I am able to connect successfully in several desktop
>> applications using the same DSN.
>> [synopsis end]
>>
>> Please post a relevant code snippet. Specifically, what does the
>> connection
>> string look like? Does it look like this?:
>>
>> http://www.carlprothman.net/Default.aspx?tabid=89#DSN
>>
>> Obviously, you'll want to redact the username/password when you post.
>> It's
>> probably not a IUSR_ permissions issue since you said
>> you're
>> using SQL Authentication. What type of DSN is it, system, user or file?
>> Did
>> you try a native OLE-DB connection like Bob suggested?
>>
> Chris, thanks for your answer.
>
> I did try veriuse connection string such as: "Driver={SQL Server};" & _
> "Server=xxx.xxx.xxx.xxx;" & _
> "Address=xxx.xxx.xxx.xxx,1433;" & _
> "Network=DBMSSOCN;" & _
> "Database=myDatabaseName;" & _
> "Uid=myUsername;" & _
> "Pwd=myPassword"
> and others but still with the same result. I did not check other
> connection
> options because I'm obligate to use the Odbc DSN. If it any help - in my
> code
> I am using microsoft.OdbcConnection class to implement the connection.
>
> Any more ideas ?
>

Please post code! Please! Please! Please!

You mention that your using the OdbcConnection class. Is this ASP.NET? If so
your in the wrong newsgroup. This newsgroup is for classic ASP. Dotnet
newsgroups have the word "dotnet" in them. Also I don't know what "veriuse"
means.

Re: Help - IIS fails to access MSSQL on a nother computer

Stren Assaf wrote:
> Chris, thanks for your answer.
>
> I did try veriuse connection string such as: "Driver={SQL Server};" &
> _ "Server=xxx.xxx.xxx.xxx;" & _
> "Address=xxx.xxx.xxx.xxx,1433;" & _
> "Network=DBMSSOCN;" & _
> "Database=myDatabaseName;" & _
> "Uid=myUsername;" & _
> "Pwd=myPassword"
> and others but still with the same result. I did not check other
> connection options because I'm obligate to use the Odbc DSN.

I do not understand. What is obligating you to use the obsolete ODBC
provider?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Help - IIS fails to access MSSQL on a nother computer

Stren,

I just ran a search on the MS KB and this is the closest article I could
find on this topic:
http://support.microsoft.com/default.aspx?scid=kb;en-us;8840 12


"John Beschler" wrote:

> Stren,
>
> I had the exact same issue with my config when I was running SQL on Windows
> Server 2003. I went through the same hoops that you've already been through
> until I discovered an MS KB article about this issue. Unfortunately, I've
> lost the article; however, the fix was to make sure SQL Service Pack 3 or
> higher is installed on the SQL installation.
>
> Please try that and let me know if it fixes your issue.
>
> John
>
> "Stren Assaf" wrote:
>
> > Jeff, Thanks for your reply.
> >
> > However i did change the web service to support basic authentication but
> > still with the same unfortunate result.
> >
> > Any more ideas?
> >
> >
> > "Jeff Cochran" wrote:
> >
> > > On Wed, 8 Jun 2005 04:23:03 -0700, "Stren Assaf"
> > > wrote:
> > >
> > > >I am having a pretty serious problem and I tried almost every thing (I
> > > >know…) but still with no success.
> > > >There are 2 computer on a workgroup (lets call them A and B). On A there is
> > > >an MSSQL Server 2000 with windows and QSL mixed authentication running on top
> > > >of Windows 2003 machine. On B I set up an ODBC as system DNS that points to
> > > >one of the databases exists on the MSSQQL sever on A using an SQL
> > > >authentication (user & password). The connection is working (the “test
> > > >connection” step at the end of the wizard is successful) and I can access the
> > > >database using SQL Server Enterprise Manager and the SQL Query Analyzer. I
> > > >also using this DSN with other desktop application running from B. BUT when
> > > >I’m running a web service server on the IIS that is located on B using the
> > > >same ODBC connection and passing the user name and the password I get the
> > > >error: “Sql Server does not exist or access denied”. (Machine B is running
> > > >Windows XP OS)
> > > >
> > > >Why is that and how can I solve it?
> > >
> > > Take a look at:
> > >
> > > HOWTO: IIS and SQL Server on Separate Machines with Trusted
> > > Connection:
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;q176 379
> > >
> > > Jeff
> > >

Re: Help - IIS fails to access MSSQL on a nother computer

Stren,

I had the exact same issue with my config when I was running SQL on Windows
Server 2003. I went through the same hoops that you've already been through
until I discovered an MS KB article about this issue. Unfortunately, I've
lost the article; however, the fix was to make sure SQL Service Pack 3 or
higher is installed on the SQL installation.

Please try that and let me know if it fixes your issue.

John

"Stren Assaf" wrote:

> Jeff, Thanks for your reply.
>
> However i did change the web service to support basic authentication but
> still with the same unfortunate result.
>
> Any more ideas?
>
>
> "Jeff Cochran" wrote:
>
> > On Wed, 8 Jun 2005 04:23:03 -0700, "Stren Assaf"
> > wrote:
> >
> > >I am having a pretty serious problem and I tried almost every thing (I
> > >know…) but still with no success.
> > >There are 2 computer on a workgroup (lets call them A and B). On A there is
> > >an MSSQL Server 2000 with windows and QSL mixed authentication running on top
> > >of Windows 2003 machine. On B I set up an ODBC as system DNS that points to
> > >one of the databases exists on the MSSQQL sever on A using an SQL
> > >authentication (user & password). The connection is working (the “test
> > >connection” step at the end of the wizard is successful) and I can access the
> > >database using SQL Server Enterprise Manager and the SQL Query Analyzer. I
> > >also using this DSN with other desktop application running from B. BUT when
> > >I’m running a web service server on the IIS that is located on B using the
> > >same ODBC connection and passing the user name and the password I get the
> > >error: “Sql Server does not exist or access denied”. (Machine B is running
> > >Windows XP OS)
> > >
> > >Why is that and how can I solve it?
> >
> > Take a look at:
> >
> > HOWTO: IIS and SQL Server on Separate Machines with Trusted
> > Connection:
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;q176 379
> >
> > Jeff
> >

Re: Help - IIS fails to access MSSQL on a nother computer

John you are the man of the hour !!!

This was exactly the problem and installing MS-SQL SP3 solve it.

Thanks for your time its saved me alot of work.

"John Beschler" wrote:

> Stren,
>
> I just ran a search on the MS KB and this is the closest article I could
> find on this topic:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;8840 12
>
>
> "John Beschler" wrote:
>
> > Stren,
> >
> > I had the exact same issue with my config when I was running SQL on Windows
> > Server 2003. I went through the same hoops that you've already been through
> > until I discovered an MS KB article about this issue. Unfortunately, I've
> > lost the article; however, the fix was to make sure SQL Service Pack 3 or
> > higher is installed on the SQL installation.
> >
> > Please try that and let me know if it fixes your issue.
> >
> > John
> >
> > "Stren Assaf" wrote:
> >
> > > Jeff, Thanks for your reply.
> > >
> > > However i did change the web service to support basic authentication but
> > > still with the same unfortunate result.
> > >
> > > Any more ideas?
> > >
> > >
> > > "Jeff Cochran" wrote:
> > >
> > > > On Wed, 8 Jun 2005 04:23:03 -0700, "Stren Assaf"
> > > > wrote:
> > > >
> > > > >I am having a pretty serious problem and I tried almost every thing (I
> > > > >know…) but still with no success.
> > > > >There are 2 computer on a workgroup (lets call them A and B). On A there is
> > > > >an MSSQL Server 2000 with windows and QSL mixed authentication running on top
> > > > >of Windows 2003 machine. On B I set up an ODBC as system DNS that points to
> > > > >one of the databases exists on the MSSQQL sever on A using an SQL
> > > > >authentication (user & password). The connection is working (the “test
> > > > >connection” step at the end of the wizard is successful) and I can access the
> > > > >database using SQL Server Enterprise Manager and the SQL Query Analyzer. I
> > > > >also using this DSN with other desktop application running from B. BUT when
> > > > >I’m running a web service server on the IIS that is located on B using the
> > > > >same ODBC connection and passing the user name and the password I get the
> > > > >error: “Sql Server does not exist or access denied”. (Machine B is running
> > > > >Windows XP OS)
> > > > >
> > > > >Why is that and how can I solve it?
> > > >
> > > > Take a look at:
> > > >
> > > > HOWTO: IIS and SQL Server on Separate Machines with Trusted
> > > > Connection:
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;q176 379
> > > >
> > > > Jeff
> > > >