vb6 ADODB connection string to sql server 2008

Question

I recently migrated a database from sql server 2005 to 2008 on windows server 2008. Clients connect fine from their XP machines and so does the SQL Management Studio 2008. I have also tested a remote connection using LINQPad which worked fine.

However on my VB6 app, the connection string seems to give me problems. Any ideas what I'm doing wrong?

    Dim strUserName As String
     Dim strPassword As String
     Dim sProc As String

     sProc = "Class_clsAdoFnx_Initialize"

        Me.DatabaseName = "db_app"




 'Connect to SQL Server

    strUserName = "admin"
    strPassword = "mudslinger"

    Set cSQLConn = New ADODB.Connection
    '**Original connection String
    'cSQLConn.CommandTimeout = 0
    'cSQLConn.ConnectionString = " PROVIDER=SQLOLEDB" & _
    '    ";SERVER=NET-BRAIN" & _
    '    ";UID=" & strUserName & _
    '    ";PWD=" & strPassword & _
    '    ";DATABASE=" & Me.DatabaseName

    '***First attempt, no dice
    'cSQLConn.ConnectionString = "Provider=sqloledb;" & _
    '       "Data Source=NET-BRAIN;" & _
    '       "Initial Catalog=DB_APP;" & _
    '       "User Id=admin;" & _
    '       "Password=mudslinger"
    'cSQLConn.Open

    '***3rd attempt, no dice 
    cSQLConn.Open "Provider=sqloledb;" & _
           "Data Source=NET-BRAIN;" & _
           "Initial Catalog=db_app;" & _
           "User Id=admin;" & _
           "Password=mudslinger", "admin", "mudslinger"

thanks in advance.

UPDATE: Here is the string I generated using my test.UL file

[ODBC] Provider

Provider=MSDASQL.1;Password=logmein;Persist Security Info=True;User ID=sa;Extended Properties="DSN=NET-BRAIN;UID=admin;PWD=mudslinger;APP=Microsoft® Windows® Operating System;WSID=BPOOR-16D68FBC7D;DATABASE=DB_App;Network=DBMSSOCN";Initial Catalog=DB_App

Here is the same UL file using the SQL Native provider:

"Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=admin;Initial Catalog=DB_APP;Data Source=NET-BRAIN;Initial File Name="";Server SPN="""

--received the error: Error in Ado Call... There was an error in Class_clasAdoFnx_initialize 3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. --Error: Class_clsAdoFnx_Initialize 3709 Requested operation requires an OLE DB Session object, which is not supported by current provider.

tried [oledb] for sql server provider option "Provider=SQLOLEDB.1;Password=mudslinger;Persist Security Info=True;User ID=admin;Initial Catalog=db_app;Data Source=net-brain"

error: -2147217900 Login failed for user 'admin'

UPDATE2 : After isolating the open connection string, it turns out the connection is opening and the stored procedure I was using to test with was failing.

Answer 1

Following Using ADO with SQL Server Native Client to enable the usage of SQL Server Native Client, ADO applications will need to implement the following keywords in their connection strings:

Provider=SQLNCLI10
DataTypeCompatibility=80

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI10;" _
         & "SERVER=NET-BRAIN;" _
         & "Database=DB_APP;" _ 
         & "DataTypeCompatibility=80;" _
         & "User Id=admin;" _
         & "Password=mudslinger;"

con.Open