ASP DSN/DSN-less connection to SQL database

Posted on 2005-04-19
Medium Priority
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

jitganguly earned 100 total points
ID: 13817576
You got to specify userid, password


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

Accepted Solution

justinbillig earned 200 total points
ID: 13817946
for dsnless

(SQLServer) should be {SQLServer}

Check here for connection string examples

LVL 15

Assisted Solution

deighc earned 200 total points
ID: 13822531
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

ID: 13896284
To deighc and justinbillig:

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

864 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