| Author |
Topic |
|
Huligan
Yak Posting Veteran
66 Posts |
 Posted - 2006-12-10 : 21:27:29
|
| 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
12543 Posts |
Posted - 2006-12-10 : 21:54:15
|
| 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. |
 |
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-12-11 : 09:16:36
|
| 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 09:19:40
|
| 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 LarssonHelsingborg, Sweden |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-12-11 : 09:38:26
|
| 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") = 15Application("THISONE_CommandTimeout") = 30Application("THISONE_CursorLocation") = 3Application("THISONE_RuntimeUserName") = "sa"Application("THISONE_RuntimePassword") = "PasswordHere"Is there something I'm missing? Thanks again for your help.Les |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 09:46:03
|
| Have you used Surface Area Config to allow access to the database?Peter LarssonHelsingborg, Sweden |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-12-11 : 11:02:22
|
| 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 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-12-11 : 11:04:30
|
| 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. |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-12-11 : 11:49:27
|
| 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 SystemThis 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 |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-12-11 : 13:19:32
|
| Check this http://www.connectionstrings.com/?carrier=sqlserver2005 |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-12-11 : 13:57:27
|
| 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") = 15Application("THISONE_CommandTimeout") = 30Application("THISONE_CursorLocation") = 3Application("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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 14:11:12
|
| 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 LarssonHelsingborg, Sweden |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-12-11 : 14:22:48
|
| You are absolutely correct and it's a policy of ours. Thanks again for everyone's help.Les |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-12-11 : 14:38:33
|
| 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. |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-12-11 : 15:14:14
|
| 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 |
|
|
|
|
Connecting classic ASP website to SQL Server 2005