Newbie question: SQLClient in ASP.Net

[info25]

Hi
Whenever I tried accessing my SQL Server 7.0 database with SQLConnection in ASP.Net, I always get the error: 'Specified SQL server not found'. My code is like:

Dim Con As New SQLConnection("server=SQLSRV;database=northwind")
Conn.open()

SQLSRV is the name of my SQL server on the network. In the ASP days, I usually use DSN to access the SQL server and all works OK.
Can anyone tell me what I should do to corrrect this?
And should I specify user id and password for the connection string? Are they the same as the DSN UID and pwd?
I think I can do it in DSN way, but I need to download ODBC.Net data provider, but it requires MDAC 2.7 or later. And Micosoft's MDAC download page says: 'Due to issues with clustering, this release is currently NOT supported on SQL Server 7.0 Servers or SQL 6.5 Clustered Servers. '! Huh? Does this mean I can't use ODBC?
Thanks!

[pvb]

This worked against my SQL7 database:

Dim cn As New SqlConnection("user id=sa;password=;database=pubs;server=godsmack;")
Try
    cn.Open()
    MessageBox.Show("Open!")
Catch exSql As SqlException
    MessageBox.Show(exSql.Message)
Finally
    If Not cn Is Nothing Then
        If cn.State = ConnectionState.Open Then cn.Close()
        cn.Dispose()
    End If
    MessageBox.Show("Done!")
End Try

[SimonRigby]

Everything looks fine.

I would suggest that there is some network problem preventing your machine from seeing the SQL server. For the record this is my way of doing it and it works.

Imports System.Data.SqlClient

----

Dim cn As New SqlConnection( _
"data source=myServer; database=myDatabase; user id=myUserName; password=myPassword")

cn.Open()

---

Hope this helps
Simon

[info25]

Yeah when I tried to ping my SQL server from the web server (by machine name and IP) it didn't respond. Is it possible that some kind of firewall is installed on it? How can I get the SQLConnection to work in this case?

[Shawn N]

SQL connections are made over port 1433 on a TCP/IP pipe. You can use this snippet in VB6 to see if you can make a connection. Put a winsock and a textbox on your form and just modify the "remotehost" property of the winsock.

Option Explicit
 
Private Sub Form_Load()
    Winsock1.RemoteHost = "server ip here"
    Winsock1.RemotePort = 1433
    Winsock1.Connect
    
End Sub
 
Private Sub Winsock1_Connect()
    Text1.Text = Text1.Text & "Connected" & vbCrLf
End Sub
 
Private Sub Winsock1_DataArrival(ByVal bytesTotal As Long)
    Dim strIn As String
    Winsock1.GetData strIn, TypeName(strIn), bytesTotal
    
    Text1.Text = Text1.Text & strIn & vbCrLf
End Sub
 
Private Sub Winsock1_Error(ByVal Number As Integer, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, CancelDisplay As Boolean)
    Text1.Text = Text1.Text & "Error: " & Description & vbCrLf
End Sub

It wouldn't make sense for your SQL server to have a firewall blocking incoming connections to port 1433. Kinda defeats the whole purpose...


Also, you might want to try storing your DSN in a file so that you can change it without having to go through your whole project changing.

[info25]

I tried what you stated and the textbox said:
Error: the attempt to connect timed out

Any suggestions about the cause of this?
Thanks!

[pvb]

It wouldn't make sense for your SQL server to have a firewall blocking incoming connections to port 1433. Kinda defeats the whole purpose...

Is this a joke? Do seriously think it doesn't make sense to protect your data? Sorry to burst the fantasy that all SQL Servers are open to the internet, but, uh, they're not. That code snippet timed out for the same reason the other snippets didn't work, like SimonRigby already mentioned, there is probably a network problem and access to that SQL Server is not allowed through a firewall or some other security feature. At our company we have a firewall that you have to specify which ports to allow in and out, etc. and the SQL Server that hosts all of our important data? ya, that one doesn't see the internet and the internet doesn't see SQL Server.


How bout this. Explain where you are trying to access the sql server from and explain where the sql server is located, who is maintaining. Can you ping it? is inside your own network and you are authorized to gain access to it? is this a company network or your home network?

[info25]

I'm trying to access it from the web server, which is located on the same network as the SQL Server (different workgroup though). I can view and access the shared folders on the SQL server machine via the network neighbourhood, but I couldn't ping it (by both machine name and IP). It was previously maintained by my company's previous employee, but he's moved now, and I don't know what the configurations etc. are.
Oh and BTW, old ASP scripts which use DSN still work OK, but when I tried to connect using DSN (with ODBC .Net Data Provider), well, it couldn't. Anyone can explain this?

Thanks!

[Shawn N]

Originally posted by pvb


Is this a joke? Do seriously think it doesn't make sense to protect your data? Sorry to burst the fantasy that all SQL Servers are open to the internet, but, uh, they're not. That code snippet timed out for the same reason the other snippets didn't work, like SimonRigby already mentioned, there is probably a network problem and access to that SQL Server is not allowed through a firewall or some other security feature. At our company we have a firewall that you have to specify which ports to allow in and out, etc. and the SQL Server that hosts all of our important data? ya, that one doesn't see the internet and the internet doesn't see SQL Server.

I'll just say the same thing again - a firewall protecting a SQL server blocking 1433 doesn't make sense. You took it upon yourself to come up with this "open to the internet" stuff.

info25 - you could try using the System.Data.SqlClient namespace. Coding is like this:

Dim conSQL As New SqlConnection(ConfigurationSettings.AppSettings("DSN"))
Dim cmdSQL As New SqlCommand("SELECT * FROM TestTable, conSQL)
Dim rdrData As SqlDataReader

cmdSQL.Connection.Open()
rdrData = cmdSQL.ExecuteReader

[pvb]

I'll just say the same thing again - a firewall protecting a SQL server blocking 1433 doesn't make sense

hmmm....this is getting to be funny dude. Do you even know what a firewall is? ya it's that little barrier between your company intranet and <sarcasm>what's that new fangled thing they keep talkin about these days? oh yeah, the INTERNET</sarcasm>

You took it upon yourself to come up with this "open to the internet" stuff

uh, what? i guess i should thank you? not really sure what that babble meant, but prior to knowing that info25 is supposedly sitting inside the same private network as his sql server , the thought was thrown out that maybe there's a firewall not allowing access to sql server. Does that make sense? That's what firewalls do, they frickin allow/disallow traffic in and out of ports on specified IP addresses. Does that make sense? If not please please explain, how did you put it,

It wouldn't make sense for your SQL server to have a firewall blocking incoming connections to port 1433. Kinda defeats the whole purpose...

...explain how that kinda defeats the purpose. This should be a good one. Explain to me how, in your world, setting up a firewall to block access to a SQL Server defeats the whole purpose(maybe explain what "the whole purpose" is also, that pretty much makes no sense) Now I'm not saying all SQL Servers should be blocked or all open, it totally depends on the situation that the sql server bein used(and most secure ones block access to sql server through a firewall). Now in case you skipped over some details, that means that firewall (that's the barrier between your private network and the internet) is blocking access(i.e. "incoming connections" as you put it) from the internet to your sql server(cuz that's why you have a firewall) but your webserver(behind the firewall) can hit sql server all day long.


Back to the problem. It's a frickin network problem. FYI, if the DSN works then you have to be able to ping it. If you can't ping it and the DSN works then that DSN is pointing to a different machine than the one info25 thinks is the target of the DSN.

Assumptions:Info25 has full rights and priv's to administer that SQL Server machine, and has a user account that has permission to log on to the sql server machine and the webserver. It's also assumed that Info25 is trying to ping from the webserver to the sql server(and not from some other machine). Info25 can also physically walk over to each of the machines as well and log on to each of them, otherwise there has to be a network guy or somebody runnin the show who can walk to them and help ya out.

At the webserver, go to command prompt, type ipconfig, right down your ip. walk over to the sql server machine do the same thing, then ping the web server ip. did it work? great. go back to the webserver and ping the sql server ip, it must work. That DSN will not work if you cannot ping the same machine the DSN is looking at, period. It has no extra special magic that makes itself work while you're unable to ping it. If you can't get that far I'm afraid you have problems far greater that not being able to connect to that sql server.

[Shawn N]

pvb I'm saying that if the computer the server is on has a firewall blocking 1433, then how would anyone even on the same network connect to it?

info25 - I'm almost positive that your problem is your connection string.

I'm not sure what program you need on your computer, but if you create a new text file on your computer and rename the extension "udl" then open it, you'll get a screen to make a connection string. After you're done with that open up the file back in notepad and your connection string will be there.