Community
Showing results for 
Search instead for 
Do you mean 
Reply

OLEDB Connection String

Accepted Solution Solved
Copper Contributor
Posts: 51
Country: United States
Accepted Solution

OLEDB Connection String

I'm trying to connect to an ACT Premium 2010 DB using OLEDB and cannot piece together a working connection string.  I have tried doing it in code from Allen's sample code in the download's section and also by building a data source using the VB data source dialog and the description in the Whitepaper on Reporting and Data Access Methods.  Both are giving me permissions errors with slightly different wording.  Below is the VB code I have and the error message.  I would appreciate any help getting this to work.

 

    '* Declarations Section

    Const ACTPadFile As String = _
      "C:\Users\Public\Documents\ACT\ACT for Windows 12\Databases\ACT2010Demo.pad"

 

    '* Event Code

        Dim actCon As New OleDbConnection

        actCon.ConnectionString = _
          "Provider=ACTOLEDB2.0;Data Source=" & ACTPadFile & _
            ";User ID=Chris Huffman;Password="""";Persist Security Info=True"

 

        actCon.Open()  '<<<< Error occurs here
        actCon.Close()
        MessageBox.Show("We got this far")

 

Error Message: No error message available, result code: DB_SEC_E_PERMISSIONDENIED(0x80040E09).

 

Any assistance will be appreciated.


Accepted Solutions
Solution
Accepted by topic author lwestatbus
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 51
Country: United States

Free at Last (At Last)

OK.  I've finally got it working and it is for real.  If you see the code in the message two before this it is working completely with a modified connection string (see below) and a configuration step.

 

The connection string we are now using is:

 

  conACT.ConnectionString = _

    "Provider=SQLOLEDB;Data Source=REDDEV\ACT7;Password=xxxx;User ID=actreader;Initial Catalog=ACT2010Demo"

 

We were never able to get the ACT OLEDB connectivity to work but stumbled into a hybred of this connection string and then put together some information from Allen (aduet) that got us working in this direction.  In order for this connection string to work we had to use the ACTReader.exe utility found in the ACT installation folder on the server.  This brought up a simple dialog that let us specify a new (and known) password for this account.  It is important to realize that actreader is actually a built in account in the ACT SQL Server DB with read only permissions on the DB table.  This means that using the account requires knowledge of the DB structure (look for Bill's post elsewhere on the site with a link to the DB documentation) and the connection does not have access to the business objects abstraction layer built into the ACT OLEDB accounts.  This is OK for us.  We'll be writing straight SQL anyway and also need the flexibility to write our SQL to take advantage of indices built into the ACT DB (also detailed in the DB documentation). 

 

People wishing to connect to ACT from external programs need to recognize the fundamental difference between using the ACT OLEDB capability and the ACTReader capability and also that both of these approaches are Read Only.  Data manipulation requires the Framework with its own set of capabilities.  The Framework also allows queries but I'm an old SQL hand and am much more confident at my ability to get the data I need with SQL that I know the approach here is what we will be doing for all retrievel. 

 

We now have the ability to create records in ACT via program interface using the Framework (see my post entitled "It's Alive") and now we can write queries in SQL using an OLE DB connection (but not the ACT OLE DB).  I'm pretty confident we can do anything we need with this capability.  The rest is just learning syntax.

 

Many thanks to aduet and mwood for their help in this process.

View solution in original post


All Replies
Nickel Elite Contributor
Posts: 937
Country: USA

Re: OLEDB Connection String

In my example @
 
 
I use a .UDL file to retain connection info (not a pad file).  This defines and holds connection info for an OLEDB connection.
 
 
Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

If you look at my constant and the structure of the rest of the connection string you will see that they are essentially the contents of your .udl file except for the localization of the pad location.  Your .udl file pointed to the pad file.  To be honest, I don't know how to incorporate a .udl file in VB but the connection string elements in your .udl were very familiar.

 

I have also tried with zero success to build a connection using the Add New Data Sources... capability but there is just no match in anything I can find on the forums to what I see inside VB.  I have found the articles on using OLEDB in the Sage KB but they are all based on using Excel and I just don't have the same interface.

 

This shouldn't be hard.  Do I need a different user ID?  You had used "Chris Huffman" with a blank password in your .udl.

Nickel Elite Contributor
Posts: 937
Country: USA

Re: OLEDB Connection String

Ok, so your almost there but with one mistake:

 

You don't need the .pad file or the UDL file reference if you are going to specify your OLEDB connection tring in code as you are doing here.  BUT you need to provide the SQL Server name as the Data Source value if you do this.  The SQL Server DB name is INSIDE the pad file but you'd need to parse that file to get it so it's probably just easier for you to specify it as a constant in your code ala:

 

Const ACTDB As String = "MYSQLSERVERNAME\ACT7"

 

then your connection code would be:


"Provider=ACTOLEDB2.0;Data Source=" & ACTDB & _
            ";User ID=Chris Huffman;Password="""";Persist Security Info=True"

 

 

A few additional suggestions - make user ID and Pass word as variables and have your application on launch retrieve username and PW from users so you can pass into these variables.

 

 

Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

I see what you are saying but it still isn't working.  I'm still getting the same error.

 

BIG QUESTION: Is Chris Huffman a user of the database????  The OLEDB connection connects directly to the DB, doesn't it whereas the SDK/Framework connects through ACT. 

 

Here is my current code:

 

  actCon.ConnectionString = _

    "Provider=ACTOLEDB2.0;Data Source=MachineName\ACT7;" _

    & "User ID=Chris Huffman;Password="""";Persist Security Info=True"

 

I have confirmed that "MachineName\ACT7 exists as a Database Engine ServerName on the local SQL Server so that is good.  But I'd be willing to bet that when I connect to the Framework using "Chris Huffman" that ACT is connecting to the DB as another identity.  Am I going to need the Reader utility to make this work? 

 

I also tried specifying an Initial Catalog as in:

 

  actCon.ConnectionString = _

    "Provider=ACTOLEDB2.0;Data Source=REDDEV\ACT7;" _

    & "Initial Catalog=ACT2010Demo;" _

    & "User ID=Chris Huffman;Password="""";Persist Security Info=True"

 

 

Employee
Posts: 1,163
Country: USA

Re: OLEDB Connection String

If your application is logging in to the framework anyway, then you could try:

 

 OleDbConnection newConn = new OleDbConnection(afw.CurrentACTOLEDB2);

 

If it's not though then this obviously isn't an option. 

Matthew Wood
Act! SDK Support
Community Moderator
Nickel Elite Contributor
Posts: 937
Country: USA

Re: OLEDB Connection String

[ Edited ]

Good questions here.

 

1.  When you connect via an OLEDB2 ACT! connection do you use the security context of an ACT! user or a SQL user - the answer is an ACT! user.  The OLEDB 2 provider's connection string looks like a typical database connection, but security is absrtacted to application security not SQL server security.

 

2.  ACTReader account is a SQL Server user account - you "could" create an OLEDB, or ODBC connection directly to the SQL server using that account, but there's pluses and minuses to doing this - pros: you have the ontext of a SQL server user so you could in theory do things like run distributed queries, also you have access to the entire ACT! schema.  cons: The entire schema is hard to understand (OLEDB2 abstracts it nice and tidy for you), also any queries ignore user level security.

 

I really like Mike's suggestion - if you are already logged into the framework you could just pass the OLEDB2 object as the connection string.

 

In the example I use in code I pass the .udl file as the connection string.  You can create your own UDL file and manage access that way as well.

 

 

One change to your code: - use database instead of initial catalog ala:

 

actCon.ConnectionString = _

    "Provider=ACTOLEDB2.0;Data Source=REDDEV\ACT7;" _

    & "Database=ACT2010Demo;" _

    & "User ID=Chris Huffman;Password="""";Persist Security Info=True"

 

 

Message Edited by alduet on 06-03-2010 03:31 PM
Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

As always, I appreciate the responses.  I tried both approaches as illustrated in the code below but am still getting the same error reported in the original post, DB_SEC_E_PERMISSIONDENIED(0x80040E09).  In all cases the error happens on the connection .Open method.  I also tried mwood's approach and also that approach leaving out the open statement thinking possibly that the framework logon approach created an already open connection.

 

Allen, I suspected what you said about the abstraction contained in the views underlying the framework.  I've already been studying the DB layout and have a good handle on the approach but I'd prefer to stick with one of the recommended approaches.  Also, I don't see any particular advantage during testing of putting the connection string in its own file.  If I can get this to work then I'll make it a resource in the application and use it throughout. 

 

I did some research on the error I'm receiving and I found a couple of postings from some years ago indicating that this error can occur when the connection doesn't have write permissions to the DB log file.  I have been dancing around so many approaches to making this work that I am willing to believe that something outside the framework and the oledb functionality is at fault, i.e., permissions at the database level or the intervention of a troublesome poltergeist.

 

Anyway, here are the two approaches I tried.  Both approaches were tried independently by commenting out the one I wasn't testing.

 

 Private Sub btnODBC_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles btnODBC.Click

'*********************************************************

'* Connect to ACT DB using ODBC and load combo box

'*********************************************************

 

  '* APPROACH #1 USING FRAMEWORK LOGON

  '* Login to the sample database using default administrator

  ActFwk.LogOn(ACTPadFile, "Chris Huffman", "")

 

  Dim actCon As OleDbConnection = New OleDbConnection(ActFwk.CurrentACTOLEDB2)

  actCon.Open()   <<< ERROR OCCURS HERE

  actCon.Close()

  MessageBox.Show(
"We got this far")

  ActFwk.LogOff()

 

 

 

 

  '* APPROACH #2 USING ALLEN'S SUGGESTION RE THE CONNECTION STRING 

  actCon.ConnectionString = _

    "Provider=ACTOLEDB2.0;Data Source=REDDEV\ACT7;" _

    & "Database=ACT2010Demo;" _

    & "User ID=Chris Huffman;Password="""";Persist Security Info=True"

  ' MessageBox.Show(actCon.ConnectionString)

 

  actCon.Open()   <<< ERROR OCCURS HERE

  actCon.Close()

  MessageBox.Show(
"We got this far")

 

End Sub

 

Thanks,

 

Larry

Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

Here is the link to a Microsoft KB article about this error: http://support.microsoft.com/kb/890307

 

 

Here is another link from a guy who builds ACT add-ons.  He is suggesting using the ACTDiag.exe tool and rebuilding a number of resources.

http://durkinaddons.com/actaddonforum/Default.aspx?g=posts&m=706

 

Larry

Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

We tried all of the ACTDiag.exe tools listed in the DurkinAddOns link and they did not change the behavior of the program (we are still getting the errors).

 

We are also unable to build system ODBC connections nor Data Sources from within the VB application. 

 

I am feeling like an idiot in front of my client now.  Any help, especially from anyone who has been through this process before and solved it, will be greatly appreciated.