asked on

Creating a OBDC Connection in SQL Server 2000

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!!

ASKER CERTIFIED SOLUTION

Mirtheil

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

ajaypappan

membership

This solution is only available to members.

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

Start Free Trial

Cyberzones

ASKER

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

ajaypappan

try this site..
http://www.crystalsoftware.com.au/datapipe/odbc_setup.html

SOLUTION

geotiger

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

superfly18

membership

This solution is only available to members.

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

Start Free Trial

ajaypappan

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

Cyberzones

ASKER

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!!