I not really sure if this is a Perl/DBI question, an Access one, or SQL Server, but here goes.

I have an SQL Server database to which I only have read access (and even this was a struggle!). To make this useful, Ive frontended it with an Access database, with a table linked to each of the tables in the true database. This then allows such things as queries to be created and saved, which I cannot do with the raw database. The SQL database is set up with user entered login credentials, so they first time Access attempts to touch the data, I'll get prompted for authorisation, but other than that its pretty transparent (I'm no SQL Server expert, but don't believe I have any ability of changing this without more access to the Server database).

I now need a bit more programatic access, so have been looking at doing this with Perl/DBI. Ideally I would like to bury the bulk of the database complexity in an Access query, and use DBI to access that, but the login credentials on the linked tables are causing me a bit of grief. DBI successfully connects to the Access database, prepares the query fine, but when they query is executed it immediately fails with no read access to the first of the SQL Server tables it tries to touch.

This is not surprising, because authentication credentials would normally be required at this point, but I can't figure out how to provide them. Providing them at database connect time doesn't help, because then the credentials are provided to the Access database, which doesn't need them, rather than to the database at the back end of the linked tables, which does.

Does DBI support doing this? If all else fails, I can code the complete query into the select clause and go straight to the SQL Server database, but its going to be a huge SELECT clause! Any other ideas?

Thanks and Regards
Andrew

In reply to DBI, ODBC, Linked table and authentication by iwik

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.