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

2
votes

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.

2
vb6crystal-reportsodbcsqlncli
this is not an answer to why this doesn't work, but using the code from this comment i could find a way around : stackoverflow.com/questions/204611/… – BigBlackDog

2 Answers

0
votes

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
0
votes

I know you said in your comment that you've got around this already, but I figured I'd throw this out there. :)

You may try using 

Provider = SQLNCLI

instead of,

DRIVER = SQL Server

to see if that makes a difference.

Take a look at the following link. Scroll down on the page until you see the "SQL Native Client 9.0 OLE DB provider" section. They show examples of connections strings using the SQL Server Native Client.

http://www.connectionstrings.com/sql-server-2005

Hope this helps.