Newbie needs code pages for SQL Server 2000 access from asp.net page using vb.net

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I am only trying to connect to a local host .
I am on Windows 2000 Server with sql 2000 server.


My error is the classic "SQL server does not exist or access denied"
I went to the MS site & they tell me what I know......"some" permissioning
issue.

I had this code working 2 months ago on a different server but now I cannot
get it going now on a different
machine

I can setup ODBC connections every which way to this local server using
Integrated mode access
using different connectivity methods such as by using "local" or
<machinename> or 127.0.0.1 or <machine IP address>. I can also connect
using SQL authentication for user sa or some other new user I created.
So I am not sure about this access denied BS.

I need to connect to Northwind & pubs dbs (the sample dbs that come with sql
2000)
Please post the complete page in (without code behind crap for now).

I went to different sites & they have partial code & they cause different
errors ( I am not a Vb.net guru)

The page I used is something similar to below.

I am just trying to connect & print the server name & SQL version etc

'================
Sub Page_Load(Source As Object, E As EventArgs)

Dim strConnection1 As String = "server=localhost; database=Northwind; " & _

"integrated security=true"

Dim objConnection As New SqlConnection(strConnection)

Dim strSQL As String = "SELECT FirstName, LastName, Country " & _

"FROM Employees;"

Dim objCommand As New SqlCommand(strSQL, objConnection)

objConnection.Open()

Response.Write("ServerVersion: " & objConnection.ServerVersion & _

vbCRLF & "Datasource: " & objConnection.DataSource & _

vbCRLF & "Database: " & objConnection.Database)

dgNameList.DataSource = objCommand.ExecuteReader()

dgNameList.DataBind()

objConnection.Close()

End Sub

'==================

Can someone tell me what is wrong & also ALL the authentication settings
step by step I need in Windows 2000 server / SQL 2000 server ?
This is just the freaking local machine & server. I cannot believe this is
so hard.
Last time someone in some newsgroup had me play with registry settings to
make this work
in addition to some other Windows 2000 user changes
(Sorry I did not save it ...did not know this would be so bad)

I would prefer a complete code page that does both Integrated Auth (as
above) and also
SQL auth (using Username / PW).
(I know the actual call is a one or two line code but the exact format
without syntax or other errors is the key)


I don't have VS-7 so I cannot drag & drop the SQL connector control as
someone suggested.
 
Lori,

When you are connecting to SQL server in integrated security mode IIS is
passing SQL server the account used to run the website. If you haven't
changed the web site to use impersonation (you would do that in the
web.config file) then the site is passing SQL server the anonymous login
account which the website would normally run under. Depending on your needs
their are multiple ways to configure this.

Here's a good article to get you started:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
 
If you haven't
changed the web site to use impersonation (you would do that in the
web.config file) then the site is passing SQL server the anonymous login
account which the website would normally run under.

I have no idea what you mean. Is there an error in logic such as your
sttement should read

======================
If you haven't
changed the web site to use impersonation (you would do that in the
web.config file) then the site is NOT passing SQL server the anonymous login
account which the website would normally run under =============================


Depending on your needs
their are multiple ways to configure this.

Here's a good article to get you started:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q176378


The above site shows how to do a DSN connection.
I already said I am able to do a DSN conncection (but am not using it)

If you look at my sample code, it is a DSNless conncection, is it not ?
 
And another thing.
The Microsoft error message is so misleading.
If there is a problem with IIS permissions why the hell does it say
"SQL server does not exist ?" Very helpful if troubleshooting is it not , by
misleading you ?
Also "access denied" by whom by SQL server ? By 2000 Server ? By IIS ?
Can they be anymore vague ?
 
Create a new project to test this with and pull the SQLDataDapter from the
ToolBox, use this to try and connect to your SQl server. You can use the
wizard here to help you try different options and get the connection tested.

Alternatively, you can open the server window from View Servers and add this
data connection connection to the form this way.
 
What I am trying say is this
It would make more sense if the error message described that permission was
denied at one of the
possible 3 layers . Even the KB article does not make any references to the
IIS layer.

That said, I am not sure what user to add where in IIS

And another thing.
How would the SQL Auth. work ? Does it not go thru IIS also anyway,
regardless ?
 
I appreciate the link but honestly, it is a typical Microsoft link.
They just thorough bits & pieces here & there.

What I need is this (to achieve the simple goal)

1. What I need to do at the Windows 2000 Server level ( User security
settings, Registry or whatever)
2. What I need to do at the IIS-5 level
3. The asp.net code page using vb.net (or even c# is fine).

I was hoping someone would already have the code page & tell me the
corresponding settings
for items 2 & 3 above on their machine to use the code.

I have done enough "fishing" on this & getting tired of it.
 
Actually it should read like this

1. What I need to do at the Windows 2000 Server level ( User security
settings, Registry or whatever)
2. What I need to set at the SQL server 2000 level
3. What I need to do at the IIS-5 level
4. The asp.net code page using vb.net (or even c# is fine) & the project
file including web.config file if any

I ran into 100's code pages ( Item 4 ) that tells you how to connect but
none of them address other layers
(Items 1 thru 3 above)

I am hoping some of you can tell me your machine configs for items 1 thru 3
above.
I think item 4 is ok with what I have. (And I suspect item 2 is OK 2 for me)
It is the OS level or IIS settings that are always a pain.
It is amazing how painful it is for even everything local to same machine
 
Back
Top