Problem connecting to sql server

  • Thread starter Thread starter Ajak
  • Start date Start date
A

Ajak

Hi,

I am using XP Pro SP2, VS.NET 2003 & IIS 5. I used to develop an ASP.NET
application on my PC on my local IIS, connecting to a LAN server which hosts
my MSSQL database. I have no problem running the web app locally.

After I reformat and reinstall XP, I could not run the app anymore. The app
could not find the SQL server. But I can connect to the server via
Enterprise Manager installed locally and browse the tables. And I can even
ping the server.

I have tried using both IP and servername but to no avail. However, my
machine can connect when I tried using normal windows application, using the
same connection string.

I think I am missing something bcos I used to have the same problem last
time. But I forgot what is needed to be done on my machine to make it work.
 
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
Click to expand...

In which case, are you absolutely certain that the connection string hasn't
changed...?
 
Ajak,

Could it be the firewall settings ? Try temporarily disabling the Windows XP
SP2 firewall to see if makes any difference.

HTH,
Stephen
 
As I stated in my first post, I tried the stand alone exe of the same
connection string, using the same code, It connects and display the record
without any problem.

In the exe, the code is as below:-
Dim dadpt As New SqlDataAdapter("Photos_SelectRandom",
"server=rnds2003; uid=sa; pwd=******; database=ABG_SQL")
dadpt.SelectCommand.CommandType = CommandType.StoredProcedure

Dim dstInfo As New DataSet
dadpt.Fill(dstInfo)
DataGrid1.DataSource = dstInfo

Whereas in the ASP.NET project, the code is as follows:-
Dim objData As New PhotoData
Dim dadpt As New SqlDataAdapter("Photos_SelectRandom",
"server=rnds2003; uid=sa; pwd=******; database=ABG_SQL")
dadpt.SelectCommand.CommandType = CommandType.StoredProcedure

Dim dstInfo As New DataSet
dadpt.Fill(dstInfo)
If dstInfo.Tables(0).Rows.Count > 0 Then
objData.ID = CInt(dstInfo.Tables(0).Rows(0).Item("ID"))
objData.GalleryID =
CInt(dstInfo.Tables(0).Rows(0).Item("GalleryID"))
... bla bla bla
End If

NOTE that in the connection string in both cases, I only edit the
pwd=******.

Since it works in the exe, it couldn't be the firewall settings. Anyway,
just to make sure, I quit my zonealarm (I dont use XP Firewall, it's set to
disabled), but still the web app could not connect giving the error:-

SQL Server does not exist or access denied.

Source Error:


Line 293:
Line 294: Dim dstInfo As New DataSet
Line 295: dadpt.Fill(dstInfo)
Line 296: If dstInfo.Tables(0).Rows.Count > 0 Then
Line 297: objData.ID = CInt(dstInfo.Tables(0).Rows(0).Item("ID"))


When I have this problem for the first time last year, I read somewhere on
the net about something need to be done to the aspnet_wp.exe and/or ASPNET
windows account (If I'm not mistaken). That was what I did to make it work.
But now, I just could not figure out what I did. Does anybody else have ever
encounter this problem and know the solution?


Thanks
 
Yes, connecting can be a ... well, problematic. ASP apps also pose
additional issues. First, I suggest not connecting as SA. This is not only
dangerous but masks many of the issues you'll have to deal with eventually.
Ok, in order to connect several factors must be in place:
1) Your application must identify the machine name and the instance of
the target SQL Server. The DNS must be able to identify the named machine
(it must resolve to a valid IP address). If the DNS is not working, the
server can't be found. DNS accessibility can be a function of how the NIC is
configured but can also be a function of the availability of the domain
controller.
2) The server must be running the target instance. SQL Server can be
configured in single-user or paused mode. These don't permit external
connections.
3) If you're using SQL Server authentication (as you are), the server
instance must be configured for mixed mode security
4) Depending on the protocols, you must punch a hole in the firewall if
the server is on another machine. If not, it's best to bypass the network
providers and use the shared memory provider. You must also make sure the
protocols you need are enabled on the server--they are off by default.
5) The login account must exist and be granted rights to the initial
catalog.
6) The server itself must have available connections. This is not a
given. Check the server configuration to ensure that the license restriction
or the DBA-imposed restriction on accounts has not been exceeded.
7) The connection pool must also have available space. (I don't think
this is your issue in this case).

The new exception handlers are not that helpful. They often return a generic
message that doesn't really help.

I hope this does.
I'll include a long talk on connecting at my talk at VSLive in San
Francisco. I hope you're connected by then.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
hi Ajak,
A similar problem occured to me, I interchanged my Initial catalog
and Datasource names, then you won't believe it worked. I had given the
connection string as

"Initial catalog=mySQLserver;Data source=testdb;user Id=sa;password=sa"

where testdb was the database name and Initial Catalog is the sql
server name.
 
Yes... I've checked every single thing you listed and they are fine...
otherwise, the stand alone exe won't connect either, right?.

Thank you for your tips though. They will be useful for my next project.
 
Ajak:

First thing, go to Client Configuation properties on the client machine
by going to Start->un->cliconfg and check to see if the protocols are
enabled. You may wish to try using a different protocol tha the one its
failing with currently, like Named Pipes for TCP.

Check to see if the account your application is trying to see has
access on SQL Server.

HTH!

Skanda
 
Skanda Subramanian said:
Ajak:

First thing, go to Client Configuation properties on the client machine
by going to Start->un->cliconfg and check to see if the protocols are
enabled. You may wish to try using a different protocol tha the one its
failing with currently, like Named Pipes for TCP.
Click to expand...

Do you mean Client Network Utility? Is so, then you mean I need to check its
settings on the SQL server, not my machine right?
I need to clarify that I didn't change anything on the SQL server. And my
Web Application works fine before I format and reinstall Win XP on my
machine. That's all I did.
Check to see if the account your application is trying to see has
access on SQL Server.
Click to expand...

The account my web application is using is the 'sa' account and yes, it has
all the privillege on the SQL server. And I'm sure the connection string is
right because the same conn string used in 2 different types of app, ran on
my one and only development machine -

1) Web Application written in VB.NET (Where I believe run by aspnet_wp.exe)
<-- cannot find the server or access denied
2) Stand Alone EXE written in VB.NET <--- yipeee! I can see the server just
fine!!!

On my mechine, I also installed SQL Client Tool such as Enterprise Maneger,
Query Analyzer... They can find the SQL server without any problem. The same
goes when I created System DSN in ODBC Data Source Administrator.

That means, only the aspnet_wp.exe cannot find the server. Right?

I am going to try connecting using classic ASP and see if the problem is
there as well.

I hope I have explained the situation I'm having clearly.
 
The problem sounds like an account problem. Your asp.net (web app) on your
local machine is using your local asp account which doesn't have access to
the SQL server you are trying to connect to. What you can do for testing I
think is to change the account running your asp.net service on your local
machine to your network account (however you log onto your network because
that account has access to the server because your other process is working
properly.) Please reply and let me know if this makes any sense and/or is
helpful.

It has been awhile since I have worked on this stuff, and if you need more
detailed information, I will have to dig into it a bit more.
 
Thanks... I have found the solution to the problem. Now I remember what I
did to make it work the last time I had this problem.

Yes, it is an account problem. All I did to make it work is change the
userName & password impersonation settings under processModel section in
machine.config file. By the default, they are set to 'Machine' &
'AutoGenerate' respectively. Setting to Machine will force Aspnet_wp.exe to
run under the built-in account ASPNET. But I wonder why it still doesn't
work even when I add ASPNET account to the Administrators group.

Then I tried setting it to 'System'. But this yields another error:- 'Login
failed for user 'sa'. Reason: Not associated with a trusted SQL Server
connection.' Not wanting to spend even more time to fix this error, I
decided to use my own login account. And it works now.

BTW, I set my login name and password in the registry and encrypt it using
aspnet_setreg to add some security.

This is a bug, but I wonder why nobody in this newsgroup have had this
problem.

For further readings, go to
http://support.microsoft.com/default.aspx?scid=kb;en-us;315159

Thanks to all.
 
¤ Thanks... I have found the solution to the problem. Now I remember what I
¤ did to make it work the last time I had this problem.
¤
¤ Yes, it is an account problem. All I did to make it work is change the
¤ userName & password impersonation settings under processModel section in
¤ machine.config file. By the default, they are set to 'Machine' &
¤ 'AutoGenerate' respectively. Setting to Machine will force Aspnet_wp.exe to
¤ run under the built-in account ASPNET. But I wonder why it still doesn't
¤ work even when I add ASPNET account to the Administrators group.
¤
¤ Then I tried setting it to 'System'. But this yields another error:- 'Login
¤ failed for user 'sa'. Reason: Not associated with a trusted SQL Server
¤ connection.' Not wanting to spend even more time to fix this error, I
¤ decided to use my own login account. And it works now.
¤
¤ BTW, I set my login name and password in the registry and encrypt it using
¤ aspnet_setreg to add some security.
¤
¤ This is a bug, but I wonder why nobody in this newsgroup have had this
¤ problem.
¤
¤ For further readings, go to
¤ http://support.microsoft.com/default.aspx?scid=kb;en-us;315159
¤

If I understand your description correctly it doesn't sound like a bug. The difference appears to be
how you are handling your impersonation. You can't forward impersonated credentials from the
*client* (when using Integrated Windows authentication) if your database is located on a remote
machine, without enabling Kerberos. When using the config file you are impersonating the credentials
on the *server* instead and then delegating to the database resource. This doesn't require Kerberos.

Of course this type of delegation is less secure than the double-hop scenario in a trusted
environment


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top