ASP DSN/DSN-less connection to SQL database

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?

John
Johnny6000Asked:
Who is Participating?
 
justinbilligConnect With a Mentor Commented:
for dsnless

(SQLServer) should be {SQLServer}


Check here for connection string examples

http://www.carlprothman.net/Default.aspx?tabid=81
0
 
jitgangulyConnect With a Mentor Commented:
You got to specify userid, password

like

 objConn.Open "DSN=Remote;uid=myuid;pwd=mypwd"
0
 
deighcConnect With a Mentor Commented:
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>
0
 
Johnny6000Author Commented:
To deighc and justinbillig:

These look  promising but I have to put it on hold for a few days.  John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.