SQL Server connection string works with IIS 5.1 but not IIS 6 in ASP.NET

[John Kennedy]

Hello,

I have a very simple aspx file that runs on WinXP Pro IIS 5.1 and connects
to a SQL Server 2k DB running on a Win2003 Server box. It works fine with
the connection string below (X replace real values for security).
"packet size=4096;user id=XXX;pwd=XXX;data source='XX.XXX.XXX.X';persist
security info=False;initial catalog=XXX"

When I place this aspx file on the Win2003 Server under a Virtual Directory
I get the following error.

"SQL Server does not exist or access denied"

I have replaced the IP address for the data source value in the original
connection string with every imaginable option (local, localhost,
<servername\sqlservername>, etc.) to no avail.

I have added ASPNET, NETWORK SERVICES, & SYSTEM as logins to the SQL Server
and the database I am trying to access. I have modified my <processModel
section of the machine.config file to use the SYSTEM user name.

Does anyone have any idea what is going on?

Thank you ahead of time for your suggestions.

John

 

[Carl Prothman [MVP]]

I have a very simple aspx file that runs on WinXP Pro IIS 5.1 and connects
to a SQL Server 2k DB running on a Win2003 Server box. It works fine with
the connection string below (X replace real values for security).
"packet size=4096;user id=XXX;pwd=XXX;data source='XX.XXX.XXX.X';persist
security info=False;initial catalog=XXX"
When I place this aspx file on the Win2003 Server under a Virtual Directory
I get the following error.
"SQL Server does not exist or access denied"
I have replaced the IP address for the data source value in the original
connection string with every imaginable option (local, localhost,
<servername\sqlservername>, etc.) to no avail.
I have added ASPNET, NETWORK SERVICES, & SYSTEM as logins to the SQL Server
and the database I am trying to access. I have modified my <processModel
section of the machine.config file to use the SYSTEM user name.

John,
Is SQL Server setup for Mix Secuirty mode?

Do you have an evaluation copy of SQL Server 2000 which expired?

Is the connecton timeout set too short?

Are you using the SqlConnection object?

Also, did you turn off connection pooling?
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476

Try adding the SQL Server port number to the IP address to your connection string.
"Data Source=xxx.xxx.xxx.xxx,1433"
(use the port number your SQL Server is setup for. Default is 1433)

Try adding the TCP/IP network library to your connection string
"Network Library=DBMSSOCN"
http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProviderIP

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[John Kennedy]

Carl,

Thanks for the response. I answered your specific questions below. We have
tried everthing you suggested with no luck in solving this issue. The MSDN
lines are all busy, so these newsgroups are our only hope until this worm
support issue eases up a bit.

John

John,
Is SQL Server setup for Mix Secuirty mode?

Authentication is set to SQL and Windows.

Do you have an evaluation copy of SQL Server 2000 which expired?

This is an MSDN Univ. sub. version of SQL Server 2000. This page works
remotely connecting to this database. Would that work if it was a license
expiration issue?

Is the connecton timeout set too short?

I have tried 90 seconds as well. Why would it work remotely on IIS 5.1 with
a 30 second timeout and not locally with a 90 second?

Are you using the SqlConnection object?

Yes. The .NET framework version.

Also, did you turn off connection pooling?
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476

No I don't think so. This is a fresh out of the box install. Everything is
set to what ever the defaults are.

Try adding the SQL Server port number to the IP address to your connection string.
"Data Source=xxx.xxx.xxx.xxx,1433"
(use the port number your SQL Server is setup for. Default is 1433)

We tried this and had the same result. It works remotely without the port
number added.

Try adding the TCP/IP network library to your connection string
"Network Library=DBMSSOCN"

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProviderIP

We tried this and had the same result.

 

[Carl Prothman [MVP]]

We have
tried everthing you suggested with no luck in solving this issue. The MSDN
lines are all busy, so these newsgroups are our only hope until this worm
support issue eases up a bit.

John,
The only other suggestions I have is to review the following KB articles and
make sure you have everything setup correctly

INF: Potential Causes of the "SQL Server Does Not Exist or Access Denied" Error Message
http://support.microsoft.com/default.aspx?scid=kb;en-us;328306

HOW TO: Configure SQL Server Security for .NET Applications
http://support.microsoft.com/default.aspx?scid=kb;en-us;815154

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[John Kennedy]

Carl,

I was able to solve the problem with help from MSDN support.

One of the items I tried earlier actually fixed the issue what kept it from
working is that I was editing my connection string on this test page in the
actual *.vb file on the server. This sort of edit requires a recompile which
I did not realize. I recompiled and updated the bin\*.dll file and it was
solved. Made the change in the actual app's web.config file and it worked.

I was using old asp file habits of editing code on the server that is
implemented immediately. Not so with ASP.NET. I wish MS made that a clear
distinction in their What's new marketing. None of our developers realized
that. BIG difference between asp and asp.net.

John

 

[Carl Prothman [MVP]]

I was able to solve the problem with help from MSDN support.

Cool! I'm glad to hear that you got it solved!

One of the items I tried earlier actually fixed the issue what kept it from
working is that I was editing my connection string on this test page in the
actual *.vb file on the server. This sort of edit requires a recompile which
I did not realize. I recompiled and updated the bin\*.dll file and it was
solved. Made the change in the actual app's web.config file and it worked.

Correct, use the web.config for all your configuration needs...

I was using old asp file habits of editing code on the server that is
implemented immediately. Not so with ASP.NET. I wish MS made that a clear
distinction in their What's new marketing. None of our developers realized
that. BIG difference between asp and asp.net.

Yup, if you stay with the code-behind model (which is the default in Visual
Studio .NET), then you will need to recompile the DLL if you make changes
to the code-behind.

However, you can use in-line code (e.g. download Web Matrix from
www.asp.net). If you add a button to the form in that tool and double-click
on it, then it creates in-line code (at the top of the aspx file). You can then make
changes to the in-line code in the ASPX file (since it gets compile at run time)
without having to recompile any DLL. Food for thought... ;-)

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com