Creating a OBDC Connection in SQL Server 2000

Posted on 2005-04-27
Medium Priority
Last Modified: 2008-01-09
I am trying to resurrect an ASP application. I have installed the application files on a Windows 2003 Server running SQL Server 2000 Enterprise (mixed mode authentication). I am having problems making a DB connection from the ASP pages.

Here is the script that is trying to make the connection in the DB:

Set oDB31 = Server.CreateObject("ADODB.Connection")
oDB31.Open ("DSN=focus123;UID=focus;PWD=focus")
Set objRS31 = Server.CreateObject("ADODB.Recordset")

The script says I need to specify a UID and Password. When setup a ODBC connection for the USER DSN (focus) in the OBDC Data Source Administrator, I think I need to specify the authentication of the login ID as the “With SQL Server authentication using login ID and password entered by the user” which I do. There is also a check in the box for “Connect to SQL Server to obtain default settings for the additional configuration options” Which I put in the “focus” ID and password. The test connection then fails????? Default database point to the focus123 DB.

I have setup a user account for “focus” in the SQL Server focus123 DB and gave it full permissions.

The ASP page error is:

Microsoft OLE DB Provider for ODBC Drivers error ‘80004005’
[Microsoft][ODBC Driver Manger] Data source name not found and no default driver specified…….

Any help would be much appreciated!!

Question by:Cyberzones
LVL 18

Accepted Solution

mirtheil earned 280 total points
ID: 13877497
You might want to try a "SYSTEM" DSN rather than a USER DSN.  I don't believe IIS/ASP can, by default, access USER DSNs. You might also need to reset IIS (using IISRESET).  

Assisted Solution

ajaypappan earned 100 total points
ID: 13877822
to connect to micrsoft sql server using asp

Dim adOpenForwardOnly, adLockReadOnly, adCmdTable
adOpenForwardOnly = 0
adLockReadOnly = 1
adCmdTable = 2

Dim objConn, objRS

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objConn.Open "Provider=MSDASQL;" & _  
           "Driver={SQL Server};" & _
           "Server=SOLEXP-SQL1;" & _
           "Database=Sales;" & _
           "Uid=suser;" & _

objRS.Open Tablename, objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

Author Comment

ID: 13877859
I setup a SYSTEM DSN for "focus123" and tried to connect to the DB from the ASP Page and this is the new error:

Microsoft OLE DB Provider for OBDC Drivers error '80040e4d'
[Microsoft][OBDC SQL Server Driver][SQL Server] Login failed for user 'RS1\USR_RS1'

It looks like it found it but I am stuck with Windows NT authentication using the network login ID which failed here. Is there anyway I can configure the SQL Server authentication to work with the UID of "focus"?
Independent Software Vendors: 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!


Expert Comment

ID: 13877921
LVL 12

Assisted Solution

geotiger earned 60 total points
ID: 13879465

It seems that you have two instances of SQL server on the same computer, so you need to find out which port is associated with which instance and make sure they are using different ports.


Assisted Solution

superfly18 earned 60 total points
ID: 13881870
1:  Check this article and see if it helps http://support.microsoft.com/kb/q238971/

2.  Try setting using a DSN-Less Connection (e.g)
set cnn = server.createobject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=databasename "

3.  If 2 works and you still want to use a DSN, try a different driver...

Expert Comment

ID: 13885186
I would prefer a DSN less connection. The code above i wrote is for DSN less connection....u can use that too

Author Comment

ID: 13885519
I fixed it. Yeahhh!!

First I used the Login Wizard in SQL Server Enterprise Manager to create a new user called "focus" with the password as the same. This was different than just going to the database and adding a User with the name "focus" as I did before.

Then I created a System DSN called focus123 and setup for SQL Authentication using the user "focus". Tested it and it worked!!

Thanks for everybodys help!!

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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