How to save your SharePoint password in Windows
A poster asked me a wonderful question: “How do I get Access [linked to SharePoint] to remember my login credentials so I don’t have to login everytime?” The answer to the question isn’t simple as it should be and hence, I felt an article on the subject would be helpful for those who want to automate logging.
For people who have an account with Office365, remembering the account password is quite straightforward. Office365 has their own custom security and thus we get a different login form when we open an Access database that depends on Office365 (whether as a published web database or containing linked tables to lists on the site):
As you can see on the screenshot, you can simply check the checkboxes, “Remember Me” and “Keep me signed in” if you don’t want to get prompted for password again. Simple!
Great! But I’m not on Office365…
When you’re using a SharePoint server that is maybe hosted somewhere else or on your network, and SharePoint is using its default setting, you’ll see a login like this:
(on Windows XP)
(on Windows 7)
The important thing to note here is that it’s not an Access thing. Access is basically calling Internet Explorer to authenticate for you. So it’s the Internet Explorer setting what we want to work with, and hence why you wouldn’t find anything to do with managing authentication within Access.
“Remember Me” doesn’t mean what it says
A more accurate statement would be probably “Remember Me, if you’re allowed to”. People are understandably frustrated when they check the box then next time they still get challenged. What’s going on? Well, in name of security, Internet Explorer may not be permitted to save the password unless you make some changes to the settings. By default, it tries to automatically authenicate for Intranet, which isn’t appropriate in this case. Therefore you need to change it so that it uses username/password. To do this, we start with….
Start Menu -> Control Panel -> Internet Options:
On the dialog, select Trusted sites zone, then click the Sites button
On the dialog, clear the checkbox for “Require server verification…” and then enter the host name of your SharePoint server. You don’t need to enter the full address of where your Access application is published to or linked to, just the base address. So if your Access application has a SharePoint list linked to http://mySharePoint.local/sites/MySite, then you would just enter “mySharePoint.local”
Click “Close” to return to Internet Properties dialog, then click Custom Level.
Scroll all way to the bottom then select “Authenicate with current username and password”
Once you’re set the radio button, click OK, then OK again on Internet Properties dialog to close both dialogs and save the changes. Back on Control Panel, we go to User Account.
Note: From this point on, the steps diverge between Windows XP and Windows 7 — for those on Windows 7 scroll down further to see the steps for Windows 7.
On the Advanced tab, click Manage Passwords
Click the “Add” button.
On this page, enter the same server host name that you entered for sites dialog previously, then the username and password you use to log in to your SharePoint host.
Click OK, then Close, then OK. You’re done with establishing the identity. You can now go to your Access application, login in then check the “Remember Me” this time and you won’t be challenged next time you run your Access application.
For Windows 7 users
As mentioned the last part of saving passwords diverge when you get into User Accounts. In User Accounts, you would select Manage Credentials.
On Credential Manager, select “Add Generic Credentials”
You would then enter the same hostname of your SharePoint server that you entered in Sites for Trusted Sites and your username/password you use to log in.
At this point, you can now run your Access Application, log in and check the “Remember Me” and it’ll actually remember next time you run your Access application.
Caveats
The steps I have outlined here are essentially the same steps that one would take to remember the login for any other SharePoint function. If you have your SharePoint server hosted somewhere other than Office365, they will likely have specific instructions that may differ slightly in allowing you to use the “Remember Me” functionality. Thus, it may be good idea to consult with them first. Searching using “Remember Me SharePoint Internet Explorer” keywords usually will yield good results.
Furthermore, if you google a bit on the subject, you’ll find different methodologies. Some may recommend using Intranet zone instead of Trusted Sites zone in the Internet properties – there is indeed a subtle difference in behaviors between those two zones, so if you find that the above instructions does not work for you, give Intranet zone a try. This appears to be a version-specific issue for the Internet Explorer as well.
Can I use VBA to automate this?
This isn’t a final pronunciation but my guts says “No” on this subject. As mentioned, this is an Internet Explorer thing and Access needs to get the token after authentication, so while I believe there may be APIs that could allow you to simulate logging in, you’d be faced with problem of feeding that token to Access. One could argue this is a good thing since it makes it less likely that you’ll be hijacked and logged into someone else’s site. After all, saving passwords are big security issues (e.g. it may mean someone could just waltz in, sit in any one of the workstation and they have access to the data). You could use the information above to at least get your users set-up at the installation time, so it’ll be an one time thing.
If the article has helped or didn’t work, by all means, let us know in comments below!
10 Comments
Thank you very much for showing a way to solve this indeed annoying issue. I just wanted to add that your method also works in another scenario:
We run Access Frontend Applications via RemoteApp (a great new feature coming with Win Server 2008) and the only thing that prevented these Apps to work just like a locally installed software was this repetitive password input…
That’s awesome to know about. Thanks for sharing, Yasin!
Sir,
why you don’t put the url of the server in the intranet zone? that zone is used by default to send your credentials to a server in your local network. With trusted you need to use password manager, with intranet zone, your logged on credentials are used.
regards
Jack
Excellent question!
The thing is that it depends on what particular set up you have. For example, in my testing, I was using a remote SharePoint server in a different domain and therefore couldn’t have used my Windows login which doesn’t match up with what SharePoint uses. When I used intranet zone, I’d still get prompted for the login and when I move it in Trusted site, it works for me. Then there’s also the fact that SharePoint server can be configured to use different authentication schemes. I’ve already noted in my article toward the end that if you have it hosted, odds are that they already have a specific instruction appropriate for their site and may tell you to use Intranet instead of Trusted zone. At any rate, it’s not a “one size fits all” solution. Hopefully, at least, the article provides enough information for readers to work out the specifics that they need to get the login remembered.
Thanks again for the thoughtful comment, Jack!
Ben, this is good stuff! Thanks for posting these details. I’m hard pressed to find much detail on the topic of integrating SharePoint 2010 and Access 2010. But maybe the reason my googling of this particular issue did not return much results is the idea you present in this article that this is an IE thing and not Access or even SharePoint issue. Makes sense, I think (as much as it can to a non-networking guy).
The Local Intranet option is the only option I could get to work as following the Trusted Sites options produced the following error at the User Accounts –> Manage Passwords –> Add step: “Windows cannot save the logon information. Make sure the information is correct and that all required fields are completed.”
Currently I run kick off Access 2010 macros (because they are exposed the the command prompt) that contain procedure calls (VBA) that perform ETL processes on SharePoint 2010 Lists. Eventually we will be given some Office 2010 server space to host these “ETL” jobs. So its crucial we avoid getting prompted even the first time which your Local Intranet instructions seemed to solve. But also the Local Intranet option remains more attractive to me as I am not sure what rights or how flexible the server team will be in following the Trusted Sites option.
Obviously we’ll have to give the machine access/privileges to the the SharePoint site as well, but please do respond with any details, warnings, lessons learned, etc.
Cheers.
Gronkey,
I’m glad to hear you got it working using the Intranet site. I’m going to climb out on the limb and guess that your SharePoint was in the same domain and therefore you could use your Windows login to authenticate yourself so the extra step of saving user id/ password would be possibly unnecessary. As I told Jack, my steps were based on a SharePoint in a different domain (e.g. you’re trying to reach it from your home rather than from your office) so you may actually find the extra steps appropriate for external access but unnecessary for internal use. Thanks for pointing it out.
Generally, I’m inclined to say that as long the server is in the same domain, Intranet is likely more appropriate than Trusted Sites. The only consideration you have to give is if you’re using a laptop which may be in and out the domain and therefore may need to work in both contexts or have your VPN configured to handle the authentication correctly. (in one workplace I worked, we had to tweak a DNS setting on the VPN to suffix a certain ending so that the VPN would be able to correctly forward requests for internal sites not available externally.)
Thanks for sharing, Gronkey. Much obliged!
Also, if you (or anyone else) wants, feel free to leave behind what other questions you may have about SharePoint/Access integration and I may try to provide additional information.
Dear Mr. Clothier:
I have been struggling to find an answer to a problem I am having with Access/Office365 Sharepoint. Questions posted on forums have gone unanswered – I wonder if you can help:
I am an amateur Access developer. I want to use Access and store my data in linked lists on my Office365 Sharepoint site. I have signed up for a trial Office365 account. I have, without much trouble, created test Access applications which use Sharepoint linked lists. And they worked. But…
They only seem to work if I am (or have recently) logged onto/into my Office365 Sharepoint account. If I try to just use the Access app, it throws error messages about not finding the data, not being able to link to the site, etc. Also, I am not presented with a login screen.
One partial answer on a Sharepoint forum suggests the following:
“When connecting to SharePoint from anything, it uses a SAML token; this token will close after a set amount of time unless there is activity within SharePoint itself. I can’t remember exactly how long the token lasts for sure I believe it is around 8 hrs.” Unfortunately, this means little to me, and he seems to have dropped the thread.
It seems quite self-evident to me that if I want to create an Access application that uses a linked Sharepoint list, the application should be able to open, run, and link to the list automatically. But this doesn’t seem to work? I’d love some clear descriptions of how/why this isn’t working and how I can make it work.
I wonder if you can suggest a link or resource?
Thanks.
(running Office365 with Access 2003/2007 and IE8)
This does sounds odd and not expected of Office365 since as pointed out, it’s very simple to get set up with Office365. It’s very possible that it could be due to running older version of Access, something I’ve not explicitly tested myself (only used 2010 with Office365).
I believe that because you paid for Office365, you should be able to get support from Microsoft for this issue. Try and post an issue in their community forums and see if they can resolve this issue.
Dear Mr. Clothier:
Thanks for your help! I’ve been investigating this further, and in my testing, if one waits long enough, (i.e. overnight, or more than 8 hours), you will be automatically logged out of your Office365 account, even if “Keep me signed in” is checked.
If I could trouble you with one more question? At the beginning of your article you seem to suggest that a login screen will open up from within an Access application. Specifically this sentence: “…and thus we get a different login form when we open an Access database that depends on Office365…”.
1. Am I interpreting this sentence correctly?
2. If so, I don’t see this behaviour with Access 2007, and don’t own Access 2010 – is it possible this is a functionality available only in Access 2010?
I appreciate your help! Thanks!
Thank you for the excellent questions!
#1) Correct, at least in all O365 setup I’ve seen, a custom login form was used.
#2) I have to confess that I’ve not tried this with Access 2007, since all of O365 I’ve worked with used Access 2010.
Here’s a thought – suppose you download the free Access 2010 runtime and run your application using that runtime — do you get that custom login form?