SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Connecting classic ASP website to SQL Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/10/2006 :  21:27:29  Show Profile  Reply with Quote
Hello. I have a classic ASP website that was using SQL Server 8 and I'm trying to connect it to SQL Server 9 (2005). I have installed SQL Server 2005 Standard Edition on my laptop and changed the global.asa. Every page that talks to the database gives the following error.

Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user '****'.

My SQL Server 2005 is set to mixed authentication mode. I'm aware I have lots of changes to make to my ASP code, but I can't get the website to connect to SQL Server 9 (2005). I would appreciate any help. Thanks.

Les

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/10/2006 :  21:54:15  Show Profile  Visit nr's Homepage  Reply with Quote
Have you created the login that the application is trying to use?


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/11/2006 :  09:16:36  Show Profile  Reply with Quote
The entire website already exists in classic ASP and it was using SQL Server 8 (2000). I have to make changes to the website so it will use SQL Server 9 (2005) instead. There are lots of pages that talk to the database. Some of them are logins while others are just views of data (simple and complex). I'm getting the same error whether I'm trying to display data on the page or using a login form. The website uses a web user account to access SQL Server and it's not working. I have created the login account the website is using and I also use it to interface with the database through Enterprise Manager/Management Studio. So I know it works. Thanks for your help.

Les
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 12/11/2006 :  09:19:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The account information used in GLOBAL.ASA must be transferred to the new server as well.
No, the only thing you do know work is that your Windows account can logon.

How about you do as nr says, and add the username and password used in GLOBAL.ASA in SQL Server 2005.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/11/2006 :  09:38:26  Show Profile  Reply with Quote
SQL Server 2005 is installed on the same machine as the web server. The website is using the 'sa' account. I know I shouldn't use the 'sa' account, but I'm only using it for the purpose of getting this thing running. Inside the global.asa, the userID, password, and data source have all been changed to reflect this. Here's the portion of my global.asa I have changed.

Application("THISONE_ConnectionString") = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DBNameHere;Data Source=MachineNameHere;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MachineNameHere;Use Encryption for Data=False;Tag with column collation when possible=False;User Id=sa;"
Application("THISONE_ConnectionTimeout") = 15
Application("THISONE_CommandTimeout") = 30
Application("THISONE_CursorLocation") = 3
Application("THISONE_RuntimeUserName") = "sa"
Application("THISONE_RuntimePassword") = "PasswordHere"

Is there something I'm missing? Thanks again for your help.

Les

Edited by - Huligan on 12/11/2006 09:39:31
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 12/11/2006 :  09:46:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Have you used Surface Area Config to allow access to the database?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/11/2006 :  11:02:22  Show Profile  Reply with Quote
Hello Peter. I visited Surface Area Config and the Remote Connections for the Database Engine are set to "Using TCP/IP only" for "Local and remote connections". I'm not a DBAdmin, but I believe this is what I need. I changed it to "Local connections only", tried it and that didn't work either so I just put it back to its original state. The only other Remote Connection I could change was for Analysis Services and it's set to "Local and remote connections" too.

Am I missing something or is this correct?

Les
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/11/2006 :  11:04:30  Show Profile  Visit nr's Homepage  Reply with Quote
By the error you are getting it is trying to connect so you can use the profiler to see what is happening.
Are you trying to connect to the default instance on the m/c.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 12/11/2006 11:05:47
Go to Top of Page

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/11/2006 :  11:49:27  Show Profile  Reply with Quote
Hello nr. What do you mean by "m/c"? I tried SQL Server Profiler and the trace reported the following.

TextData: Login failed for user 'sa'. [CLIENT 10.0.0.103]
ApplicationName: Microsoft Windows Operating System

This is probably a stupid question, but I know nothing of SQL Server 2005 management. How do I find out by my login is failing? I have confirmed my password is correct about a dozen times?

Les
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 12/11/2006 :  13:19:32  Show Profile  Reply with Quote
Check this
http://www.connectionstrings.com/?carrier=sqlserver2005
Go to Top of Page

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/11/2006 :  13:57:27  Show Profile  Reply with Quote
I found the problem! The connection string for SQL Server 2005 must include the password. This sounds stupid but SQL Server 2000 did not require it so the hundreds of global.asa files I have created never included it. I tried it and it works. Here's what my global.asa should look like.

Application("THISONE_ConnectionString") = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DBNameHere;Data Source=MachineNameHere;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MachineNameHere;Use Encryption for Data=False;Tag with column collation when possible=False;User Id=sa;Password=PasswordHere"
Application("THISONE_ConnectionTimeout") = 15
Application("THISONE_CommandTimeout") = 30
Application("THISONE_CursorLocation") = 3
Application("THISONE_RuntimeUserName") = "sa"
Application("THISONE_RuntimePassword") = "PasswordHere"

Thanks for everyone's help. I appreciate it and learned a good bit about SQL Server 2005.

Les
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 12/11/2006 :  14:11:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Now, when you are set, you should know that using the sa password is a very, very, very, very, very bad idea.
If you are exposed to SQL injection, any user can take control over your database with the same priveleges as you have. In fact, they can do everything to the database such as DROPPING the database, TRUNCATE tables, UPDATE tables with bogus values...

You should have an account who only has DATAREADER and DATAWRITER priveleges.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/11/2006 :  14:22:48  Show Profile  Reply with Quote
You are absolutely correct and it's a policy of ours. Thanks again for everyone's help.

Les
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 12/11/2006 :  14:38:33  Show Profile  Reply with Quote
What do you mean hundreds of global.asa files I thought you were only meant to create one ???

Also MS SQL 2000 required a password also in the connection string, unless its a trusted connection.
Go to Top of Page

Huligan
Yak Posting Veteran

USA
66 Posts

Posted - 12/11/2006 :  15:14:14  Show Profile  Reply with Quote
I have created/developed lots of classic ASP websites and as you probably know Visual Interdev would create those for you (yeah, I know how old Interdev is but keep in mind this was in the past). So I was taking one of those classic ASP websites and updating it so it talked to SQL Server 2005. The website was working with SQL Server 2000 in our development environment and the global.asa was missing password from the connection string, but it did have it in the application portion. So I tried changing just the occurences of data source, user ID, and password that I saw. It didn't work until I added password to the connection string.

Les
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000