DBI, ODBC, Linked table and authentication

iwik has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on DBI, ODBC, Linked table and authentication

Replies are listed 'Best First'.
Re: DBI, ODBC, Linked table and authentication by Narveson (Chaplain) on Apr 01, 2008 at 15:08 UTC
Great question. I happen to think Perl, DBI, and MS Access make a wonderful set of front-end tools for any SQL Server shop (and an opportunity for evangelism). You're right about your fallback option of bypassing Access and going straight to SQL Server with a big select statement. To mitigate the inconvenience, you can develop your query interactively in Access; convert what you have developed into a SQL pass-through query (thus getting instant feedback about whether your SQL code is back-end compatible); and finally paste your working pass-through query into your Perl program. But to answer your question, yes, you should be able to modify your Access database so that DBI can connect to it programmatically. The key is simply to save your authentication credentials in the table links. Try recreating your links from the Access UI, and watch for the "Save password" check box in the Link Tables dialog box. It helps if you can save your ODBC connection in a File DSN.	[reply]
Re^2: DBI, ODBC, Linked table and authentication by iwik (Initiate) on Apr 03, 2008 at 09:59 UTC
Thanks, I've pretty much got it running along the lines of your suggestion; I had a good peek at the SQL Access was generating and munged that into the Perl code. Because the query was itself nested I had to flatten that first... It's a tad uglier than I wanted (in its own way SQL can end up as unreadable as a good regex), but at least its doing what I want. I did have a peek earlier to try and see if there was something in the links that would allow credentials to be defined there, but couldn't find anything. However, I didn't go as far as recreating links, and its always easier to look for something that you know exists, than for a possibility that might not. I'll go back and try that path - I never liked having to supply the credentials in any case, so embedding them in the links would be a plus all round. Thanks for the pointers Andrew	[reply]
Re^3: DBI, ODBC, Linked table and authentication by iwik (Initiate) on Apr 07, 2008 at 21:17 UTC
100% right. I've just got around to having a play, and when setting up the Access link, you have the option of saving the password, which embeds the access credentials in the link properties. There does not appear to be any option of modifying an existing link to do this. So the answer was in Access. This gives me a solution that will work perfectly, and solves another problem at the same time. Thanks for the pointer. Andrew	[reply]