Can MS Access display the user authentication that its backend SharePoint requires?

[Brilstern]

This question is similar to @twinnyfo's question here, I am trying to access a database that has some credentials requirements due to the backend SharePoint linked tables.

The database links to 6 tables that sit on SharePoint that uses a smartcard to verify access, specifically the card and pin. After you authenticate, the site becomes available. When you access the database without first logging into the SharePoint site, it freezes, which I assume is it waiting for some kind of credentials as it tries to authenticate to the SharePoint site.

The SharePoint site uses the same credentials the windows box used to login with, but it does not support using the authenticated session from windows, rather creates its own session with the same credentials.

My question: Can I call the user interface that SharePoint does to authenticate, so that the database does not first require the user to authenticate to the SharePoint site?

[twinnyfo]

Stevan,

I don’t know if this is an answer, but I have encountered similar problems when trying to save items to a SP library from Access. In order for the Access system to connect directly with SP Libraries, one must first connect to that library (i.e. simply go to that library via SP—or in my case, navigate to that library using WIndows Explorer to make the connection). Then Access can send items to that Library.

I would “think” that if the user was connected to that SP site and then opened the Access DB that things would work as expected. It’s an extra step, I know, but I think it has to do with how SP authenticates (and then times out) in connecting other system services to its libraries.

Again, don’t know if this is an “answer” or simply my workaround. SP must access the user’s system in some way to authenticate, I just don’t know enough about that aspect to speak intelligently about it.

[Brilstern]

Hey twinnyfo (hope all is well!)

Yeah, I came to the same conclusion. If the user opens the SP portal via a web browser first, there are no hiccups and the MS Database works as it should, obviously authenticating with the session credentials already initiated by the user. Although slightly annoying, it's not the worst work-around. What I would really like to explore is; force the MS Database to request user authentication in lieu of the SP portal page on open. But per our discussion, not really sure on how to approach it.

[twinnyfo]

I know I am way outside my wheelhouse, here, but even if you could access information in the Active Directory, listing all your authenticated users, I think you can only engage those protocols once you actually try to access SharePoint. It seems like a silly workaround, I know, to access the SharePoint page prior to the DB taking you there.

I look at it similarly to share drives (mainly because I use my SP libraries to save files (like a share drive). If I save something to my share drive, because all my users (and me) have permissions in AD on that drive/folder, I can save files there. "One would think" that trying to save something to SharePoint would work the same way if a user had permissions in AD for a particular SP Page/folder/library.

I understand what MS is trying to prevent, but in my opinion, permissions are permissions....

Sorry I can't be of more hepp!

[Brilstern]

Lol, it is all good.

Unfortunately my situation is even more complex. Although the SP uses the same smart card for creds, it doesn't use the same AD structure or accounts as the domain. It is a multi-domain SP. So I am in the "must open browser" first bucket... I tried programmatically opening the SP portal from the first form open in my DB (prior to any forms that query data); but it doesn't authenticate properly, and then the user manually has to refresh the login page and then they get the cred prompt. Didn't really make the work-around any easier.

[twinnyfo]

HAHAHAHAHAH!!!!

Sorry, I know it's really not funny!


HAHAHAHAHAHA!

You gotta love Microsoft!

[Brilstern]

Haha, well at least I can laugh with you! :)

Got to love it...

[Brilstern]

twinnyfo,

Revisited this today out of curiosity and made "some" progress. I created a public function that refreshes the linked tables, and ran it as part of my AutoExec.

Exhibit A:

Public Function TableRelinkSample()
      CurrentDb.TableDefs("<TableName>").RefreshLink
End Function

But... this is a design function and opens the database in design mode. So now I am at a crossroads on how to get out of design mode to avoid the below error. Any thoughts? Still researching this right now.

"The database has been places in a state by user 'Admin' on machine 'XXX' that prevents it from being opened or locked."

[twinnyfo]

Stevan,

Whenever I publish changes to my DB, I change the links from a local "working" copy of the data to the network "official" data. The DB has been compiled already, so I wonder why you are getting this error.

I just now tested using a SharePoint List and came up with no errors, using a compiled (.accde) DB with no errors.

Would you not be able to run this connecting activity "in the background" when someone is headed toward using the SP list?

[Brilstern]

Hey twinnyfo,

Update #2

So... I (don't make fun of me) usually avoid splitting my databases... but I decided to for many reasons, to include updating the tables without getting this error.

To address your thoughts.

When a user opens the (now front end) database, it:

opens a flash form for 3 seconds and closes
opens a user access agreement prompt, user clicks ok, prompt closes
opens a home form (switchboard) which is where the user has a lot of buttons to manage data on all the tables (buttons open forms)

Pre-RefreshLink function; the database would lock up trying to open the home form if the user had not opened SharePoint to the site that stores the linked tables.

Post-RefreshLink function; the SharePoint authentication form pops up and forces the user to authenticate then closes prior to opening the home form

Not sure why, but refreshing the link on the tables forces the front end into exclusive mode.

Good thing: I can give each user new front end and then I don't have to worry about it.

Preference: update tables on back end and not have front end go into exclusive mode.

Due to continuous updates, I have not been compiling, as we are still building out how we use the database on a day to day basis.

[Brilstern]

So I just educated myself...

using backend... smart; for this effort, does nothing

The SharePoint tables are not "in" the backend caused they are linked to the SharePoint. (Duh, I somehow wasn't thinking about that)

So now I am back to my original problem.

How would I force the front end to refresh the links to the SharePoint tables each time the database is opened to ensure they are authenticated, without them opening the front end (or their copy) in exclusive mode?

[Brilstern]

So... The problem might have been something to do with caching./?

I did this:

Access Options -> Current Database -> Caching Web Service and SharePoint Tables.
Uncheck all.

And the problem no longer exists.

Thoughts?

[twinnyfo]

YOU

ARE

A

GENIUS

!!!!

I have no clue!

:-)

[Brilstern]

Haha, not quite..

So. In the end, for those reading, the solution to the orginial issue of authenticating to SharePoint:

CurrentDb.TableDefs("<TableName>").RefreshLink

Do this as part of your AutoExec to ensure that the user has authenticated prior to connecting to online datasets.

To avoid design mode/exclusive access:

Access Options -> Current Database -> Caching Web Service and SharePoint Tables. 
Uncheck all.

Hope this helps the next reader!