Issue connecting classic asp to SQL Server...

Go to the SQL Server ( SSMS ) using your asp user. and see if he is able to connect and access the things.

Sorry I forgot to mention that... Yes, the user works fine in SSMS.

Ok,. So if the user is working then check for the connection string based on the version from here -
https://www.connectionstrings.com/sql-server/

Please see if the connection string is perfectly fine or not?


Also Is the code breaking on the connection object correct ?

Please try this connection string.
"Provider=SQLOLEDB;"Source=servername;Database=databasename;UID=userid;PWD=password;"

Doesn't work either... I tried multiple strings... One thing I notice is:  When I use "Driver={SQL Server};" in the string, then it takes about a minute to fail. But if I try some of the other ones like "Driver={SQL Server Native Client 11.0};" etc, then it fails right away. So it might be something with that driver in the string.
Any more suggestion after this?
It should be SQL Server 2012 if that matters.

Go into IIS and turn on error messages to get a more descriptive error in the browser.

Open  up your site, go to ASP. Go to debugging, and set "send errors to browser" to true

A 500 error means the response from the asp is unexpected. Make sure your asp tests to see if the connection established and if not output back db connection failed.
Etc. this way when there are issues in the asp processing data is still transmitted to the browser with an indication what the issue might be..

Using servername presumes that it can be resolved, what is the relationship between the web server and the SQL server?

@Big Monty:  I have no access to IIS. It's a server I don't control at all. I just have a webspace and SQL-Server space.

@arnold: You have an example for a simple test?  The sysadmin claims that there's a connection between SQL and web. I'm not quite sure about that. That's why I need a simple example to test and confront the sysadmin with that. :-)

One is to use I think pragmatic on error ...to continue versus aborting.
Do you have access to logs including error logs?
I do not have an example handy, the asp shoukd check whether the. Connection is established, if memory serves if the vonnection fails, the variable will be null.

With a limited access what you effectively need is to catch the error that terminates your asp page .
You could the first thing is output text/HTML
Disable friendly error I. The browser and see if the error page includes more detail that may clearup the issue.

See http://www.powerasp.net/content/new/on-error-resume-next.asp or handling errors.

You should check for errors after events, connect to db, send a query, etc.

Without the error capture, e script aborts, and the 500 error is shown to the user.

Ok. I have news here. I used this code to test the connection:

<%
On Error Resume Next

Dim objConn
Dim strSQL
Set objConn = Server.CreateObject("ADODB.Command")
strSQL = "SELECT * FROM tbl_patient"
objConn.ActiveConnection "Driver={SQL Server Native Client 11.0};Server=serveraddress;Database=dbname;Uid=username;Pwd=pwd"
If objConn.errors.count = 0 Then
Response.Write "Connected OK   "
Else
Response.Write "Connection problem   "
End If

Set Recordset1 = objConn.Execute

If Not Recordset1.EOF Then
Response.Write "Recordset holds data   "
Else
Response.Write "Recordset holds NO data   "
End If

i=Recordset1.RecordCount

Response.Write ("The number of records is: " & i)

Recordset1.Close()
Set Recordset1 = Nothing

If Err.Number <> 0 Then
Response.Write( Err.Description)
Response.End
end if
%>

Select allOpen in new window

When I run it, I get this response:

Connected OK Recordset holds data The number of records is: Object required

So there seems to be a problem with line 23, and I know there is records in the table.
Anything I did wrong? Any suggestions?

... and an odd thing is: if I change the server name, database name or user name to something that doesn't exist, then I get the same response.... :-(  very odd...

Unless and until you determine where the issue that terminates the script without output,.....

Why would it be different?
The issue with your script does not change.
The info terminates the script without output.
Changing the connection screen to anything that it definitely will not connect and will not get any info, why would you think the response will change?

It's like you try to open a car, failing that you randomly try to open other cars expecting different results.

Did you disable friendly errors in the browser's advanced settings?
To actually get the raw error from the server when this page is accessed?

Yes, friendly errors disabled... but no more info when I load the page.
I did experience in the past that a wrong connection string prevented access to a database.
But I wrote the sysadmin and are waiting for a response from them. That may take several days.
Aaaaaaaaaand by the way: I discovered an error in the test script. It does say "No connection" now all the time. I rewrote the script. I will paste it below.
I will return with more info when I heard from the sysadmin.

Best regards

Ulrich

Test script:

<%
On Error Resume Next
Dim objConn
Dim strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tbl_patient"
objConn.Open "Driver={SQL Server};Server=serveraddress;Database=dbname;Uid=username;Pwd=pwd"
If objConn.errors.count = 0 Then
Response.Write "Connected OK      "
Else
Response.Write "NOT Connected       "
End If

objRS.Open strSQL, objConn, 1,3
If Not objRS.EOF Then
'iterate through records here
Else
'no records found
End If

i=objRS.RecordCount

Response.Write ("The number of records is: " & i)

objRS.close
Set objRS=Nothing
objConn.close
Set objConn=Nothing
%>

Select allOpen in new window

I just added a line to the script to find out how many errors there are, and it says 2.

Once the connection can not be setup, the query attempt will error out as well.

Hi therr.

Ok. It turned out that the admin placed the database in a secure area where the webserver couldn't connect to.
Now it will be moved to a DMZ zone. I will close this post when all is working and award points.  :-)

best regards

Ulrich

oh... great, glad finally you got it. :)

Cheers

Usually, the sql server should not be in the DMZ, a ruke shoukd allow the web server access to the SQL server.

Ok. Final chapter in this story. After aaaaaall the struggling to make the connection from website to SQL Server work, it turned out, that the admin placed the SQL away from the DMZ zone, and that's why the connection didn't work. Then he moved the database to a SQL Server in the DMZ zone. Did it work then? Not quite. Now I couldn't even connect to the database via SSMS. After sitting next to the admin and digging through a lot of stuff with user settings, permission and so on, we came to the SQL Server event log... and oooops... When he moved the database into the DMZ zone, he thought that the password remained the same. But the log said something else. All my attempts to connect was listed with the comment: password didn't match username... I suggested to change my password. Bingo. Then it finally worked.
Lesson: Always change/update password if you move a SQL database to another server. :-)
So finally I can do some solid work now. :-)
Thanks for your help and suggestions.

Best regards

Ulrich

Thanks again.