Creating a OBDC Connection in SQL Server 2000

Posted on 2005-04-27
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

    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).  
    LVL 3

    Assisted Solution

    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

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

    Expert Comment

    LVL 12

    Assisted Solution


    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.

    LVL 1

    Assisted Solution

    1:  Check this article and see if it helps

    2.  Try setting using a DSN-Less Connection (e.g)
    set cnn = server.createobject("ADODB.Connection") "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...
    LVL 3

    Expert Comment

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

    Author Comment

    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

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    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…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now