Logon failed, Crystal Reports 9, VB6 , sql server, sqlncli

crystal-reportsodbcsqlnclivb6

i'm trying to build a setup package for a legacy vb6 software. the software itself connects to a sql server via sqlncli (native client). i've packaged all the dependencies and deploy them to a new machine running winxp and office2003.

now, from the target machine i can connect to the database (ms sqlserver 2005) that is running somewhere else using tcp/ip. the legacy software connects to the db just fine and i can manipulate data. but when i try to open a crystal report, things get messy:

i get an error saying "Run-time error '-2147189176(80047e48): Logon failed.
Details: 01000:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect())"

since the reports where designed on a different system, different db and what not … there is a loop before calling the report that resets the db-information for every table :

  For i= 1 To numTables
    Set crTable = crTables.Item(i)
    crTable.SetLogOnInfo dbServer, dbName, dbLogin, dbPasswd
  Next

the ConnectBufferString reads "Connection String=DRIVER=SQL Server;;User ID=user;;Password=;;Database=MY_DB;;Server=192.168.1.3\SQLEXPRESS;;UseDSNProperties=-1"

there doesn't seem to be a way to explicitly set the provider to SQLNCLI, at least i don't see any.

when running the software on my dev-system everything works like it's supposed to.

i hope you guys can help me out with this.
just so we don't have to argue about this : i also think both these technologies (vb6 and cr9) are outdated, but switching is not an option here.

Best Answer

You need to wipe the tables and change the configuration of access to the database, see the example below. In the first line of code I put an example if necessary to change the connection driver, in my case the report was pointing to a PostgreSQL database and programativamente needed change to connect to a SQL SERVER.

myReportObject.Database.Tables(1).DllName = "crdb_ado.dll"       'Connect to SQL with OLEDB
For Index = 1 To myReportObject.Database.Tables.Count
    With myReportObject.Database.Tables(Index)

        .ConnectionProperties.DeleteAll

         .ConnectionProperties.Add "Provider", "SQLOLEDB"
         .ConnectionProperties.Add "Data Source", mySqlIP
         .ConnectionProperties.Add "Initial Catalog", myDB
         .ConnectionProperties.Add "User ID", usr_id
         .ConnectionProperties.Add "Password", pass
         .ConnectionProperties.Add "Integrated Security", 0

    End With
Next
Related Topic