ASP DSN/DSN-less connection to SQL database

Posted on 2005-04-19
Last Modified: 2012-05-05
I have tried to make a DSN and a DSN-less connection to SQL databases                                                    both on my local machine and on my network.  I *can* make a connection through Microsoft Query Analyzer to both databases through Windows authentication.

The ASP code is as follows:

1) For the DSN-less connection
    Dim objConn    
    Dim objRec
    Dim strConnect

    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRec = Server.CreateObject("ADODB.Recordset")
    strConnect= "Driver=(SQLServer);Server=Remote;DataBase=Warehouse;UID=myname;PWD=mypassword;"
    objConn.Open  strConnect

    objRec.Open "Select top 10* from myTable", objConn, 0,1,2

For the local machine substitute:
       strConnect = "Driver=(SQL Server);Server=(local);DataBase=localDB;UID=myname;PWD=mypassword;"

2) For the DSN connection I connect the ODBC to the remote database, naming the DSN "Remote" and to the local database naming it "Local".  Both Test connections succeed.

I use the following code:

    Dim objConn    
    Dim objRec
    Dim strConnect

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

  'For remote connection
        objConn.Open "DSN=Remote"
   'For local connection
        objConn.Open "DSN=(local)"


In all cases I get back the error message:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I know the data source is there, and I *did* mention the default driver.

What's going on? Why can't I make this connection?

Question by:Johnny6000
    LVL 20

    Assisted Solution

    You got to specify userid, password


     objConn.Open "DSN=Remote;uid=myuid;pwd=mypwd"
    LVL 15

    Accepted Solution

    for dsnless

    (SQLServer) should be {SQLServer}

    Check here for connection string examples
    LVL 15

    Assisted Solution

    You say that you can connect with the Query Analyzer using Windows authentication.

    But you connection string is passing a user name and password, which means that you're trying to connect using SQL Server authentication. This is OK but it assumes that you actually have some logins created in SQL Server.

    You CAN use Windows authentication in a connection string, so maybe you should try that just as a test.
    Also, for performance reasons, you should use the OLE DB provider for SQL Server.

    I use the following connection string to connect to SQL Server using Windows authentication:

    Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<database name>;Data Source=<name or ip of server>

    Author Comment

    To deighc and justinbillig:

    These look  promising but I have to put it on hold for a few days.  John

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now