Cannot connect to MSSQL2005

[MiniEggs]

Hi

I'm trying to connect a ASP Classic 3.0 script to a MSSQL2005 DB

Here is the connection code

<%
Dim conSQL

Set conSQL = Server.CreateObject("ADODB.Connection")

strConn = "Provider=SQLOLEDB; Data Source=(local); Initial
Catalog=MyDatabase; Integrated Security=SSPI;"

conSQL.Open strConn

If conSQL.State = adStateOpen Then
Response.Write "Connection to " & conSQL.DefaultDatabase & " created
succeassfully"
else
Response.Write "Connection failed"
end if

conSQL.Close

Set conSQL = Nothing

%>

But I get this error

Microsoft OLE DB Provider for SQL Server error '80004005'

Cannot open database "MyDatabase" requested by the login. The login failed.

I've tried using server name, ip address etc but still no luck

Does this error mean it COULD connect to the server but cant open the
database for some reason. Or it could not connect to the server ?

I can open the database in SSMS fine

Thanks

[Bob Barrows]

MiniEggs wrote:
> Hi
>
> I'm trying to connect a ASP Classic 3.0 script to a MSSQL2005 DB
>
>

> But I get this error
>
> Microsoft OLE DB Provider for SQL Server error '80004005'
>
> Cannot open database "MyDatabase" requested by the login. The login
> failed.
>

> Does this error mean it COULD connect to the server but cant open the
> database for some reason.

Probably.
Check the Event Log on the machine on which SQL Server is running to verify.
You can also check the SQL Server logs as well.

>
> I can open the database in SSMS fine
>

So ... ? That's probably not relevant :-)

Your credentials might not be the ones being used to connect to SQL Server.
If your site is using Basic Authentication, or you have Anonymous turned on,
then the credentials being used are those of the IUSR_MachineName account.

My general practice is is to create a limited-rights SQL login for use in my
ASP applications.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
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"

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
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"

[Bob Barrows]

MiniEggs wrote:
> Hi
>
> I'm trying to connect a ASP Classic 3.0 script to a MSSQL2005 DB
>
>

> But I get this error
>
> Microsoft OLE DB Provider for SQL Server error '80004005'
>
> Cannot open database "MyDatabase" requested by the login. The login
> failed.
>

> Does this error mean it COULD connect to the server but cant open the
> database for some reason.

Probably.

Check the Event Log on the machine on which SQL Server is running to verify.
You can also check the SQL Server logs as well.

>

> I can open the database in SSMS fine
>

So ... ? That's probably not relevant :-)

Your credentials might not be the ones being used to connect to SQL Server.
If your site is using Basic Authentication, or you have Anonymous turned on,
then the credentials being used are those of the IUSR_MachineName account.

My general practice is is to create a limited-rights SQL login for use in my

ASP applications, and use "...;user id=aspaccount;password=xxxxxxx; ..."
instead of "...; Integrated Security=SSPI;"

[Bob Barrows]

MiniEggs wrote:
> Hi
>
> I'm trying to connect a ASP Classic 3.0 script to a MSSQL2005 DB
>
> Here is the connection code
>
> <%
> Dim conSQL
>
> Set conSQL = Server.CreateObject("ADODB.Connection")
>
> strConn = "Provider=SQLOLEDB; Data Source=(local); Initial
> Catalog=MyDatabase; Integrated Security=SSPI;"
>
>

> But I get this error
>
> Microsoft OLE DB Provider for SQL Server error '80004005'
>
> Cannot open database "MyDatabase" requested by the login. The login
> failed.

It's just occurred to me to ask a "DUH" question ... can we assume that the
instance of SQL Server that contains "MyDatabase" is running on the same box
as your website? "Data Source=(local); " connects to a sql server on the
same box as your website.

[MiniEggs]

Bob

Thanks for your replies

To give you a "DUH" answer yes it is. It's just a test server at the moment

I will try setting up a user as you suggest although i've not done this on
SQL before so I may be back !

Thanks!

"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OvKBy6da...@TK2MSFTNGP04.phx.gbl...

[Dan]

This might be a silly question, but did you run the Surface Area
Configuration tool to allow connections? Which protocols have you enabled,
and in which order do you have them?

Dan

"MiniEggs" <b...@a.com> wrote in message
news:eNSFrCfa...@TK2MSFTNGP02.phx.gbl...

[MiniEggs]

Dan

Yes it is configured to allow remote connections using this document

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

But still having no luck

I can't quite believe I cant find a page on google on how to do this sort of
setup as there must be millions of them out there

I'm sure i'm doing something wrong but still cant get it to connect.

and yes everything is local on the same box

Cheers

Andrew

"Dan" <ne...@worldofspack.com> wrote in message
news:%23HpLjTf...@TK2MSFTNGP05.phx.gbl...

[Dan]

In that case it's likely to the as Bob's first reply, in that the
permissions for the IIS application pool user account don't allow access to
SQL Server. I've got multiple sites and multiple servers running classic ASP
under IIS6 with SQL Server 2005 as the backend database, and I've not had
any problems connecting to it at all. However, none of my sites use the
IUSR_ account, they each have a dedicated user account and permissions set
as appropriate in the SQL Server databases (to stop one site from accessing
data specific to another site).

Dan

"MiniEggs" <b...@a.com> wrote in message

news:O5ups6Fb...@TK2MSFTNGP02.phx.gbl...