Unoffical empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

Topic Options
#288050 - 11/10/2006 08:22 Connection string problem to MS-SQL2005
Taym
carpal tunnel

Registered: 18/06/2001
Posts: 2504
Loc: Roma, Italy
This is driving me crazy. I am having a problem when connecting from an ASP page to a DB in MS SQLServer 2005. We just migrated to 2005, restored all dbs successfully. These databases are all accessible by means of an "sa" account, valid as db owner on the whole SQL server.

ALL applications passing the login info (sa+password) via web.config files are working correctly. Here is an example of such web.config file:
Code:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="DBUser" value="sa"/> <!-- The user for the database-->
<add key="DBPass" value="**********"/> <!-- The password for the database-->
<add key="DBName" value="numenorUNI"/> <!-- The database name -->
<add key="DBServer" value="."/> <!-- The IP address of the database server machine, and its port number (default is 1433) -->
<add key="DBOwnerPrefix" value="dbo."/> <!-- The database table own, default is "dbo." -->
</appSettings>
[...]





The only two applications accessing the databases from ASP pages using JS, instead, are returing this error:

Code:

Microsoft OLE DB Provider for SQL Server error '80040e4d'

Invalid authorization specification

/VIRINDOR/login.asp, line 38



Lines 36~38 in login.asp are:
Code:

strSource = "Provider=SQLOLEDB.1; "+Application("mdbPath");
objConnection = Server.CreateObject("ADODB.Connection");
objConnection.Open(strSource);



and they refer to this line in the global.asa file:

Code:

Application("mdbPath") = "Data Source=.; Initial Catalog=MainDB; User ID=sa; Password=*********;";



Can anybody help? There must be something in the connection string which is not good with SQL2005. All these worked on SQL2000...
_________________________
= Taym =
MK2a #040103216 * 100Gb *All/Colors* Radio * 3.0a11 * Hijack = taympeg

Top
#288051 - 11/10/2006 10:20 Re: Connection string problem to MS-SQL2005 [Re: Taym]
g_attrill
old hand

Registered: 14/04/2002
Posts: 1172
Loc: Hants, UK
I've done a couple of google searches and one possibility is that SQL Server is running in a system account, which doesn't allow remote logon? So either setting it to run in it's own account might work, or as a quick fix possible used shared memory by changing the Data Source to "(local)".

Top
#288052 - 11/10/2006 10:36 Re: Connection string problem to MS-SQL2005 [Re: g_attrill]
Taym
carpal tunnel

Registered: 18/06/2001
Posts: 2504
Loc: Roma, Italy
Thank you. Unfortunately, that is not the case. It is actually running with its own account, not system account. Anyway, if that was a problem, it would not explain why sa can access the database when doing so from the web.config file.
_________________________
= Taym =
MK2a #040103216 * 100Gb *All/Colors* Radio * 3.0a11 * Hijack = taympeg

Top
#288053 - 11/10/2006 10:38 Re: Connection string problem to MS-SQL2005 [Re: Taym]
Phoenix42
veteran

Registered: 21/03/2002
Posts: 1424
Loc: MA but Irish born
SQL2000 use a particular TCP port, SQL2005 asked you to select one at install time.

We put the following reg key on our clients, don't know if this is the best solution, but it is what we're doing.
[HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\CONNECTTO]
"%DATABASE_SERVER%"="DBMSSOCN,%DBSERVER_IP%\\%DB_NAME%,%PORT_NUMBER%"

Bits between the %% are what you'll need to change

Top
#288054 - 11/10/2006 10:46 Re: Connection string problem to MS-SQL2005 [Re: Phoenix42]
Taym
carpal tunnel

Registered: 18/06/2001
Posts: 2504
Loc: Roma, Italy
Phoenix42, I am using port 1433, default, as in SQL2000, and also the SQLSERVER is on the same machine than the ASP pages accessing it, so I don't think the port is what is causing the authentication failure, since I am not connecting via tcpip. Also, consider that other applications are using the same sa account successfully.

This is a further test I made:

In the connection string above, I changed "Data Source=.; ..." to "Data Source=blablabla; ..." (blabla=meaningless, non existent server), and I still got the SAME error message:

Code:

Microsoft OLE DB Provider for SQL Server error '80040e4d'

Invalid authorization specification

/VIRINDOR/login.asp, line 38



That, I think, suggests that this error is generated even before the sa login is used to access the database. It really seems that it is the connection string syntiax that is somehow wrong, but I don't see where. I wonder if SQL2005 requires some different syntiax?
_________________________
= Taym =
MK2a #040103216 * 100Gb *All/Colors* Radio * 3.0a11 * Hijack = taympeg

Top
#288055 - 11/10/2006 11:29 Re: Connection string problem to MS-SQL2005 [Re: Taym]
Roger
carpal tunnel

Registered: 18/01/2000
Posts: 5683
Loc: London, UK
Did the default for Trusted_Connection change? I can't find anything in the documentation that says so, but try explicitly setting it to false in the connection string.
_________________________
-- roger

Top
#288056 - 11/10/2006 11:43 Re: Connection string problem to MS-SQL2005 [Re: Roger]
Taym
carpal tunnel

Registered: 18/06/2001
Posts: 2504
Loc: Roma, Italy
Roger, how shoudl I modify this? Is this what you mean:

Application("mdbPath") = "Data Source=.; Initial Catalog=MainDB; Trusted_Connection=False; User ID=sa; Password=*********;";


Edit:
... in which case, it did not work


Edited by taym (11/10/2006 11:46)
_________________________
= Taym =
MK2a #040103216 * 100Gb *All/Colors* Radio * 3.0a11 * Hijack = taympeg

Top
#288057 - 11/10/2006 11:47 Re: Connection string problem to MS-SQL2005 [Re: Taym]
Roger
carpal tunnel

Registered: 18/01/2000
Posts: 5683
Loc: London, UK
Quote:
Is this what you mean


Yes.

Quote:
... in which case, it did not work


Bummer.
_________________________
-- roger

Top
#288058 - 11/10/2006 11:49 Re: Connection string problem to MS-SQL2005 [Re: Roger]
Taym
carpal tunnel

Registered: 18/06/2001
Posts: 2504
Loc: Roma, Italy
Hey, now that I think about it, the working applications are ASP.NET, while these two malfunctioning ones are simple ASP ! Maybe there's something related to that?


Edit:
I also tried Trusted_Connection=No


Edited by taym (11/10/2006 12:15)
_________________________
= Taym =
MK2a #040103216 * 100Gb *All/Colors* Radio * 3.0a11 * Hijack = taympeg

Top
#288059 - 11/10/2006 13:01 Re: Connection string problem to MS-SQL2005 [Re: Taym]
Taym
carpal tunnel

Registered: 18/06/2001
Posts: 2504
Loc: Roma, Italy
Ok,

I'm officially an idiot.

Since the two applications were not... "applications" in IIS due to me being an idiot and forgetting to set them as such and not noticing every one of the 3 thousand times I checked IIS cfg, global.asa was obviously not being read.

Thank you all, as usual, for you help and generosity. You definitely helped me think in the right direction: when I realized that no matter what I changed in global.asa I always got the same error, I finally saw the light.

Alright, I'm off for a coffee. Thanks again.
_________________________
= Taym =
MK2a #040103216 * 100Gb *All/Colors* Radio * 3.0a11 * Hijack = taympeg

Top