asked on

Connecting to MSSQL 2005 from Classic ASP

Dear experts,

I have a sad problem ;-)
I'm unable to connect to my database from my local computer using Windows Authentication.
I have tried the code below, but I always get this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.

I've tried to look for this error, and found an answer, but it didn't help me.

Do I need to change some settings in the IIS?

Anyone in here that can help me?

Every answer will be appreciated.

FROM CONNECTIONSTRING.COM:
Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;
 
 
THE WAY I TRY TO CONNECT:
 
set Conn = Server.CreateObject("ADODB.Connection")
DSN = "Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxx.mdf;User Instance=true;"
Conn.Open(DSN)
 
 
THIS CONNECTION WORKS, BUT IT IS EXTERNAL AND SLOW:
 
set Conn = Server.CreateObject("ADODB.Connection")
DSN = "DRIVER={SQL Server}; SERVER=xxx.xxx.xxx; DATABASE=xxx_data; UID=xxx; PWD=xxx"
Conn.Open(DSN)

Open in new window

Raja Jegan R

I suspect the connection strings because one of your other Connection string works fine.

Try out the connection strings below:

http://www.connectionstrings.com/sql-server

Raja Jegan R

For SQL Server 2005..

http://www.connectionstrings.com/sql-server-2005

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanishCoder

ASKER

Hi,

When I log in through my Object Explorer I need to use Windows Authentication.
I only have two servers...the one at my webhost and the one at my server called DANIEL-PC\SQLExpress.
To me that means that I also have to use Windows Authentication when I try to connect.

Do I have to change some settings somewhere?

DanishCoder

ASKER

A little screenshot of my object explorer :-)

Untitled-2.jpg

Anthony Perkins

I am confused. Your question it titled "Connecting to MSSQL 2005 from Classic ASP", but now you are asking about "When I log in through my Object Explorer I need to use Windows Authentication."

So which is it?
If it is ASP you should use SQL Server Authentication.
If it is using SSMS then either one can be used, but Windows Authentication is preferred.

DanishCoder

ASKER

I'm sorry about that, acperkins.

When I log in I'm using what's shown at the picture.

I just want to connect from my asp script using IIS7. First of all, do I need to set any properties in IIS?
Since I connect through Windows Authentication when I log on, I think it's the same way in my asp script.

I'm fighting three errors, when I try something.

1)
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.

2)
[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE DATABASE permission denied in database 'master'.

3)
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified.

I have tried this so far:

Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL 
 
Server\MSSQL.1\MSSQL\Data\xxx.mdf;Database=xxx; Trusted_Connection=Yes;
 
Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL 
 
Server\MSSQL.1\MSSQL\Data\xxx.mdf; Database=xxx;Trusted_Connection=Yes;
Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxx.mdf;User Instance=true;
 
Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxx.mdf; Database=xxx;Trusted_Connection=Yes;
 
Driver={SQL Native Client};Server=.\SQLExpress; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xxx.mdf;Database=xxx;Trusted_Connection=Yes;

Open in new window

Anthony Perkins

>>Since I connect through Windows Authentication when I log on, I think it's the same way in my asp script.<<
No, it is not. Please use SQL Server Authentication when connecting through ASP.

>>Microsoft][ODBC SQL Server Driver][SQL Server]CREATE DATABASE permission denied in database 'master'.<<
That is because you are not using the correct connection string. You do NOT want to attach a database, you simply want to connect to an existing database.

DanishCoder

ASKER

acperkins,
Thanks for kicking me...it seems to work now. You are the man!
I created a sqlserver and both of your connectionstrings work!
Again many thanks!

WebAl

acperkins,
Can you please clarify why you say 'Please use SQL Server Authentication when connecting through ASP'? I have a connection string that works with a UID and PWD, but not when I try to use Integrated Security. Maybe your clarification could help me.

Thanks.

Anthony Perkins

Please post a new thread and you will find this community in general very responsive.