Cannot connect to MSSQL2005

Hi

I am 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 have 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

MiniEggs wrote:Probably.

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.


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;"

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I do not
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
do not check it very often. If you must reply off-line, then remove the

MiniEggs wrote:it is just occurred to me to ask a "DUH" question ...

it is 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.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
do not check it very often. If you must reply off-line, then remove the

BobThanks for your repliesTo give you a "DUH" answer yes it is.

Bob

Thanks for your replies

To give you a "DUH" answer yes it is. it is 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!

This might be a silly question, but did you run the Surface AreaConfiguration

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

DanYes it is configured to allow remote connections using this

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 cannot 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 am sure i'm doing something wrong but still cant get it to connect.

and yes everything is local on the same box

Cheers

Andrew

In that case it is likely to the as Bob's first reply, in that thepermissions

In that case it is likely to the as Bob's first reply, in that the
permissions for the IIS application pool user account do not allow access to
SQL Server. I have got multiple sites and multiple servers running classic ASP
under IIS6 with SQL Server 2005 as the backend database, and I have 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