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

DanishCoderAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
When using ASP, don't use Windows Authentication, instead use SQL Server Authentication, as in:
Driver={SQL Native Client};Server=ServerName;Database=DataBaseName;Uid=Username;Pwd=Password;

Or better still use OLEDB:
Provider=SQLNCLI10;Server=ServerName;Database=DataBaseName;Uid=Username; Pwd=Password;
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DanishCoderAuthor Commented:
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?
0
 
DanishCoderAuthor Commented:
A little screenshot of my object explorer :-)

Untitled-2.jpg
0
 
Anthony PerkinsCommented:
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.
0
 
DanishCoderAuthor Commented:
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

0
 
Anthony PerkinsCommented:
>>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.
0
 
DanishCoderAuthor Commented:
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!
0
 
WebAlCommented:
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.
0
 
Anthony PerkinsCommented:
Please post a new thread and you will find this community in general very responsive.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.