Re: Unable to connect to DSN
From: Bob Barrows (reb01501_at_NOyahoo.SPAMcom)
Date: 02/11/04
- Next message: Bob Barrows: "Re: Execute method vs Recordset object"
- Previous message: NiQ: "Re: counting duplicate values"
- In reply to: Thomas Scheiderich: "Re: Unable to connect to DSN"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 11 Feb 2004 06:55:38 -0500
Thomas Scheiderich wrote:
>
> I was only using ODBC, because there is a problem I have not been able
> to solve on setting up UDL file. My W2K is missing something, but it
> won't handle the file correctly. My other W2K machine does it fine.
> I finally got tired of dealing with it, so I just used ODBC.
>
> Just out of curiosity, why do you like OLEDB better? I heard there
> wasn't much difference (I'm sure I am wrong here).
The main reason is here:
http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated_components.asp
There is a great deal of difference: There are two libraries involved when
using ODBC: the OLEDB library (which is the only library that ADO can use to
communicate with databases), and the ODBC library. When you use the OLEDB
provider for your database, the only library involved is the OLEDB library,
which means there is one less layer of software between your application and
the database, leading to more robustness and efficiency.
>
> Also, I am only using my sa account on my lab rat machine. I would
> never use SA on my normal machines. I agree this would be trouble if
> I did.
>
>>
>> Here's a simple OLEDB connection string (I can't bring myself to type
>> "connectionToDatabase" when "cn" will do):
>>
>> cn.open "Provider=sqloledb;data source=<name of your server>;" & _
>> "initial catalog=<name of database>" & _
>> "user id=<some account other than sa>;password=<password>"
>>
>> If I can't convince you that OLEDB is better: Is the DSN a system
>> DSN? It needs to be.
>>
>
>
> The OLEDB file. I tried using the actual data source name as well as
> local
>
> and tried it with and without the initial catalog.
>
>
> <snip of all the html - why did we need to see that?>
> <%
> Set connectionToDatabase=Server.CreateObject("ADODB.Connection")
> connectionToDatabase.ConnectionTimeout = 60
> connectionToDatabase.Open "Provider=sqloledb;data source=(local);" & _
> "initial catalog=CIS" & _
> "user id=SA;password="
The problem here is the lack of a semicolon after "CIS".
I'm hoping you simply censored the password (it's helpful if you use the
string "<censored>", or even "XXXX" instead of leaving it blank. That way we
don';t have to wonder if you've made a mistake.). If you haven't assigned a
password to the sa account, you are asking for trouble: many viruses, (Code
Red comes to mind) were able to work because sa accounts were not assigned
passwords.
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E4D)
> Login failed for user 'IUSR_ALLOSAURUS'.
> /connectDB.asp, line 13
As for your error message, I am very puzzled. Without the semicolon, I got a
different error: Invalid authorization specification
With the semicolon, it works fine:
dim cn
set cn=createobject("adodb.connection")
cn.open "Provider=sqloledb;data source=(local);" & _
"initial catalog=pubs;" & _
"user id=sa;password=<censored>"
if cn.state=1 then
Response.Write "Connection opened"
else
Response.Write "Connection not opened"
end if
on error resume next
cn.close:set cn=nothing
Without the password, I get a login failed for user 'sa'. Are you sure
you're showing us the connection string that produced that error? The only
way I can get your error is if I change it to:
cn.open "Provider=sqloledb;data source=(local);" & _
"initial catalog=pubs;" & _
"Integrated Security=SSPI"
I expect to get this error because no account has been created in SQL Server
for the Internet Guest Account (IUSR_machinename). It is not recommended
that you use integrated security from asp. If you have the recommended
configuration where IIS and SQL Server are on different machines, a domain
account has to be used to connect to the server. It can be difficult to
manage security this way. Go back to the first method (just include the
semicolon I inadvertantly left out of my example)
Bob Barrows
-- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
- Next message: Bob Barrows: "Re: Execute method vs Recordset object"
- Previous message: NiQ: "Re: counting duplicate values"
- In reply to: Thomas Scheiderich: "Re: Unable to connect to DSN"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
