Issue connecting classic asp to SQL Server...

Hi there.

I am moving a well functioning database (classic asp and MySQL) from one server to another (classic asp and SQL Server).
I have trouble making it work. I changed the connection string as it should be, but when testing a login page that connects to SQL, then I get a time out and the usual 500 - internal server error.
I use classic asp with SQL Server with no issues on another server setup. I used the same concept as there. Here's the connection string:

"Driver={SQL Server};Server=servername;Database=databasename;Uid=username;Pwd=password;"

Open in new window


That setup never failed for me.

On this particular server I question if the created user (the user in the connection string) on the SQL server has got enough rights. The admin only checked "public" for "Database role membership". On a server I control I had to check more options to make it work. Is "Public" enough?

Can anyone help me with some trouble shooting?  
Is there a simple code I can use to test the connection from an asp-page to the SQL Server?
Thank you.

Best regards

Ulrich
ullenulleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Go to the SQL Server ( SSMS ) using your asp user. and see if he is able to connect and access the things.
1
ullenulleAuthor Commented:
Sorry I forgot to mention that... Yes, the user works fine in SSMS.
0
Pawan KumarDatabase ExpertCommented:
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 ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Pawan KumarDatabase ExpertCommented:
Please try this connection string.
"Provider=SQLOLEDB;"Source=servername;Database=databasename;UID=userid;PWD=password;"
1
ullenulleAuthor Commented:
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.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
0
arnoldCommented:
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?
0
ullenulleAuthor Commented:
@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. :-)
0
arnoldCommented:
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.
0
arnoldCommented:
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.
0
ullenulleAuthor Commented:
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
%>

Open 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?
0
ullenulleAuthor Commented:
... 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...
0
arnoldCommented:
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?
0
ullenulleAuthor Commented:
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
%>

Open in new window

0
arnoldCommented:
You should group the commands that require the presence of a connection within the section of the if check
Line 10 and below shoukd gave lines 15 through 29 as a single block.

I.e. Is the connection established! Run the queries, process.
0
ullenulleAuthor Commented:
I just added a line to the script to find out how many errors there are, and it says 2.
0
arnoldCommented:
Once the connection can not be setup, the query attempt will error out as well.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Try using the built in Err object in your test script, it'll be more reliable:

<%
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 Err.Number <> 0 Then
Response.Write "Connected OK      "
Else
Response.Write Err.Description
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
%>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ullenulleAuthor Commented:
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
0
Pawan KumarDatabase ExpertCommented:
oh... great, glad finally you got it. :)

Cheers
0
arnoldCommented:
Usually, the sql server should not be in the DMZ, a ruke shoukd allow the web server access to the SQL server.
0
ullenulleAuthor Commented:
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
1
ullenulleAuthor Commented:
Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.