SQL V Windows Authentication in SQL 2008

[cptspock]

I have the strangest problem. First let me state that I am by no means an expert on either Web Applications or SQL. I have however, been delegated this project and have made some progress.

First, all the servers for a small office I work in were upgraded to 64 bit processors and everything was migrated up with no problems, except for the WEB server. I was able to figure this out without much problem and have the web server running on out 64 bit processor. Again, with one exception. There is a very small SQL database that was running on SQL 2000. I migrated the database up to SQL 2008 with minimum difficulty. However, now I cannot get the ASP script to connect to the SQL DB to add or retrieve data. I built an ODBC connection and I can connect perfectly well, but when my ASP tries to connect, I get the following errors:

Connection made using Windows authentication
Login Failed: Reason Failed to open explicitly specified Database.
Connection made using SQL authentication, Server configured for Windows Authentication only.

I have checked and double checked my configuration and I have it configured for MIXED mode, SQL and Windows Authentication.

I have tried the ODBS connection using both windows integrated and SQL server authentication and both connections work. it is the database itself that seems to have the problem. The database has a special user that is used to add or update records. I have tried making this user, a windows user as well as the SQL user but I still have the same problem.

I hope someone out there has a solution.

Thanks in advance
Annetta

[AndreRet]

Did you check your SQL error log file on the server? This will give you an exact error code on which we can then respond. I have not worked on SQL 2008 for some time now, but I always used to us ethe following links, which gives quite a good description and error solving from the log file.

http://blogs.msdn.com/b/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

Or,

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/c488cf76-2515-440f-b3f8-9cfad689c5b6/

[cptspock]

The error code from the SQL Error Log is. Error: 18456 Severity:14, State 38

I have tried everything I could find on the MSDN site. According to everything I have found, it should be working. it just doesn't . I even tried enabling the SA login, but that did not help either.

Thanks for the response.

[AndreRet]

Look at the link below, I think your problem might be at the bottom -

We had the same type of error messages in our SQL Server log: "Login failed..., error: 18456, severity: 14, state: 38). It turned out to be our report server trying to connect to the SQL Server and trying to access its database with an incorrect name. Once we corrected the database name in the "Report Server Configuration Manager", the problem went away.

Read through the link, your answer might be found there.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121202

Did this help?

[AndreRet]

These also cover quite a few on state 38 apart from above -

http://boardreader.com/thread/error_18456_severity_14_state_38_f2fbX2liq.html

Or

http://www.windows-noob.com/forums/index.php?/topic/1016-sccm-reporting-webpage/

[cptspock]

Using the link you provided, I was able to use the Trace file to identify the process that was giving me the error. w3wp.exe is the culprit. I changed the login to be the IWAM local user, made sure the password was valid and tried again. Still the same error. It tried to run it as my system admin account just to check and I received the same error. Now my question would be, what user do I need to change it to? The trace still says that I am trying to use SQL authentication when the server is set to Windows, but the startup log clearly states MIXED mode. I think I am using classic ADO. should I be using something else? IS there an easier way than ASP.NET to connect to my database? it is really a very simple DB, that lets users enter a request and to track those requests until completion.

[AndreRet]

I regretfully have to say that I am at a loss as well. I thought it might have been the user/admin privileges that was the culprit, but I have not dealt with SQL2008 in a while now.

Try and post your problem on this site under Databases. I am sure there are someone that will know the answer.

Sorry I can not be of assistance further, good luck in solving your problem.

[cptspock]

Actually you helped me quite a bit. I have gotten rid of the login failure. It turned out to be the ODBC connection login. It was conflicting with the userid that my script was using. At least I think that is what the problem was. I get no login failures not but the ASP script returns a blank page. Progress though. I have an ASP Database book I am reading at the moment.

Thanks again for your help. I learned a lot by reading your post.

[AndreRet]

Only a pleasure. Please mark the thread as solved unless you still want to post more questions re your problem here, thanks.

[cptspock]

BTW, it turned out to be the actual VB Script embedded into the HTML. Once I removed the VBscript and just loaded the HTML code. Everything went to working just fine. I wish I new VB so I could figure it, the guy that wrote it in 2007 must have done so for a reason.

thanks again for the help.