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.

I'm getting Error: -2147217900 Login failed for user 'admin' — phill
– phill, Commented Jun 2, 2010 at 23:28
yes, i tested it locally on the remote server, remotely through the sql server management studio and on an system odbc datasource on a remote xp machine. — phill
– phill, Commented Jun 2, 2010 at 23:52
Are you sure it is 'admin'? Should it maybe be 'sa'? I am not all that familiar with SQL Server, but I thought 'sa' was the default admin account user name. — Mark Wilkins
– Mark Wilkins, Commented Jun 3, 2010 at 16:33
sa is the default sysadmin account, however we waned to use our created user 'admin' who previously worked before migrating the database to sql 2008 — phill
– phill, Commented Jun 3, 2010 at 17:16

volody · Accepted Answer · 2010-06-02 23:28:25Z

3

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

answered Jun 2, 2010 at 23:28

volody

7,2373 gold badges46 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

phill Over a year ago

I get an error in Ado Call.. "There was an error in Class_clsAdoFnx_Initialize 3706 Provider cannot be found. It may not be properly installed."

phill Over a year ago

I also tried setting provider back to SQLOLEDB and received a similar error

volody Over a year ago

is this client computer has 64 bit OS?

phill Over a year ago

Yes it is. It is running on windows 2008 server

volody Over a year ago

Do you have only connection open code when you did your testing?

|

Collectives™ on Stack Overflow

vb6 ADODB connection string to sql server 2008

1 Answer 1

19 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related