9 Replies Latest reply: Mar 26, 2010 10:54 AM by gpending RSS

    Connecting to SQL Server using Native Client

      I am trying to create a form and am starting off with adding a dropdown box populated from a SQL Server table. I created the connection using the SQL Native Client and Windows Integrated Security. That worked fine and when I go to the properties of the drop down and configure the binding it shows me the fields. So far so good

      When I go to PDF Preview I get a message saying "Connection for Source DataConnection failed because the environment is not trusted."

      Have looked through the forums and there are a few messages but all seem to appertain to setting up a DSN. We have a few hundred users and I don't want to have to setup DSN's I want to use native SQL and windows authentication.

      Is this possible?

      Best Regards

      Richard
        • 1. Re: Connecting to SQL Server using Native Client
          Community Member
          Typically, in order for the PDF to connect to a database source there needs be a DSN set up on the client. I believe if you certify your PDF, and the cert chains to a root that is trusted by the client machine, you can do what you're trying.

          In situations like yours I usually prefer to have 3 tiered architecture, where the PDF sends a request to a web service, which queries the database, and responds to the PDF instead of having the PDF connect directly to the database.

          Chris
          Adobe Enterprise Developer Support
          • 2. Re: Connecting to SQL Server using Native Client
            Community Member
            Thanks for the quick response.

            Not sure I agree with the "typically needs a DSN" as this seems an unnecessary step when there is a native driver for SQL that doesn't require it, however that's just my opinion. Thats my last resort as it creates extra maintenance.

            Certifying was my thinking and I read some of the help on certifying but ran into two problems.
            First couldn't find any info on how to do it for a Designer form.
            Second even if you saved the form and went into Acrobat Pro, as that seemed to be the only place to certify, the "save as certified document" was greyed out. Tried all sorts of permutations but if the doc came from designer with a data connection you couldn't certify it. If you know where there is any info on how to do this I would much appreciate it.

            Hear what you say about web service but I wanted a form system that I could let power users in the company create and manage. A web service is going to require programmers so I'd probably go the DSN route for simplicity.

            Thanks

            Richard Ames
            • 3. Re: Connecting to SQL Server using Native Client
              Community Member
              Sorry, I wasn't clear, what I meant by "typicallyl needs a DSN" is if you don't have a local DSN it won't work unless you do extra work.

              To certify an XFA form, add a signature field in Designer, save it as a PDF, open the PDF in Acrobat, click the sig field, choose the option to certify and follow the prompts.

              Chris
              Adobe Enterprise Developer Support
              • 4. Re: Connecting to SQL Server using Native Client
                Community Member
                Not sure what an XFA form is. I searched Acrobat help without getting any hits, got a few hits in Designer help talking about an accesor which appears at quick glance to be an object for use in scripts/programs.

                Assuming you just meant a dynamic PDF form. I tried my original test form without success and so I created a new blank form in designer with fill and submit as the method. I added a signature field and saved as a dynamic PDF form. opened the PDF in Acrobat right clicked the signature field and chose the option to certify when prompted. Used my self signed Digital ID and then saved the form. Went back into Designer and added a data connection using the SQL native client, tested the connection and then added a drop down box and set the binding to use one of the fields which were displayed to choose from. Went to preview pdf and got the same environment not trusted error message.

                Any more ideas? Has anybody written a white paper or instructions anywhere on creating forms to work with SQL Server?

                Thanks

                Richard
                • 5. Re: Connecting to SQL Server using Native Client
                  Community Member
                  An XFA form is any form created by Designer, whether dynamic or static.

                  Certifying the PDF needs to be the last thing you do. If you certify the PDF, then open it in Designer it is converted back to an XDP, then when you save it out as a PDF again, it's a new PDF and will no longer be certified.

                  The credential used to certify the PDF needs to chain to a root that is set as a trusted identity in Acrobat. Not sure if you can use a self signed cert for this, but you may be able to add your self signed cert as a trusted identity in Acrobat and have it work.

                  Chris
                  Adobe Enterprise Developer Support
                  • 6. Re: Connecting to SQL Server using Native Client
                    Community Member
                    Tried all of the above without success so gave up on using the SQL native client direct.

                    I created an new form and this time created a DSN using the SQL native Client. Then created the data connection using the microsoft OLEDB provider for ODBC drivers and selected the DSN. Tried it with or without putting my username and password in as the DSN was set for windows authentication. Did not certify it but this does not bring up the error message "Connection for Source DataConnection failed because the environment is not trusted." and I can bind the drop down to one of the fields. However when I go to pdf review it does not show anything in the dropdown box which the previous flavour did and if I click on the drop down I get a beep but no message and nothing happens. Thinking there was limited functionality in the preview I tried closing and opening in Acrobat but same result.

                    What does the beep mean? an error message would be really useful.

                    Thanks

                    Richard
                    • 7. Re: Connecting to SQL Server using Native Client
                      Community Member
                      Any answers on this yet? I've been struggling for days now with a similiar scenario with a SQL ODBC connection and dropdowns. All I get is that frustraiting error beep when I click the dropdown.
                      • 8. Re: Connecting to SQL Server using Native Client
                        r2_

                        Could you please elaborate on the 'extra work' method or point me in a direction?  I'm trying to use form to auto-populate some dropdowns from the database for users who are not able to have a DSN setup on their local machine and who are not on the same network.

                         

                        There seems to be a sticking point with the secure environment requirement...

                         

                        Many thanks for your expertise, it is GREATLY appreciated and welcomed!

                         

                        -richard

                        • 9. Re: Connecting to SQL Server using Native Client
                          gpending

                          Hi all,

                          I justed posted a similiar question.  Did anyone ever get a drop down box to populate?  I can get text boxes to populate using DSN but not the drop down box.  If there is a different behavior for drop down's as opposed to text boxes?  I would like to buy the Livecycle but if I haven't solved this by the time the trial runs out I'm not going to buy it.

                           

                          Any ideas, anyone?