[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Connecting to MSSQL 2005 from Classic ASP

Posted on 2009-05-02
11
Medium Priority
?
601 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:DanishCoder
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24286411
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24286413
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1200 total points
ID: 24286510
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DanishCoder
ID: 24286679
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
 

Author Comment

by:DanishCoder
ID: 24286695
A little screenshot of my object explorer :-)

Untitled-2.jpg
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286711
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
 

Author Comment

by:DanishCoder
ID: 24286920
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24287513
>>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
 

Author Closing Comment

by:DanishCoder
ID: 31577184
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
 

Expert Comment

by:WebAl
ID: 24360975
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24364769
Please post a new thread and you will find this community in general very responsive.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question