Tuesday, April 29, 2014

Retrieve an Active Directory Attribute to set the Default Save Location in Microsoft Office 2010

Every year when we image new computers, I try to work a little harder at making the image as automated as possible. We have a lot of short-term volunteers and students who turn over frequently. Training is also spotty, so the easier and more consistent we can make things, the better.

One thing that we try to do is set the default save location in Microsoft Office (Word and Excel) to our network location for users so that they don't accidentally save files to the C: drive. This has always been a manual task, and as such, it has often been missed. This especially true for student machines where there may be 2 or 3 different students using the workstation over the course of a week. There had to be a better way...and it turns out that there is: LDAP queries in Group Policy Preferences!

This is one of the hidden features of Group Policy Preferences. I stared at it for a while and couldn't figure this out on my own, as it doesn't seem like there is a way to retrieve and store information in a variable using GPP. Here is the key: using LDAP queries, it is possible to retrieve any Active Directory Attribute and store it in an environment variable. We were already storing the path to the user's shared folder as an AD attribute "userSharedFolder", so all I had to was retrieve it and set the appropriate registry keys based on this attribute.

What is userSharedFolder?

UserSharedFolder is an unused ActiveDirectory attribute. In our case, we have been storing the path to the user's shared network folder in this attribute already, in order to facilitate creating and archiving new accounts with Powershell. You can access this attribute manually if you like, using the ADUC attribute editor.

Open Active Directory Users and Computers.
Click on View | Advanced Features.

Open any user object, click on Attribute Editor and scroll down. Attributes are all alphabetized.





In many environments you might instead be using the HomeDirectory (Home Folder) attribute that is exposed by default in ADUC and allows Windows to automatically map a drive to that path.



In our environment, the HomeDirectory attribute is only present in some accounts and is not where we want the user to default saving. If you want to use HomeDirectory instead, you can skip the LDAP lookup below and use the always present  environment variable HomeShare or HomeDrive.

Create the Environment Variable

First, we will retrieve this AD attribute and set it to an environment variable.

Create a new GPO. I removed "authenticated users" from the read permissions for this GPO and added just a few test accounts.

Expand User Configuration, Preferences, Windows Settings, and create a new Environment preference.



Set the action to "Update", choose "User Variable" and give it the name "USERSHAREDFOLDER". This is the environment variable that we will be able to access later to set the appropriate registry keys.

Below we will set the value to %_USERSHAREDFOLDER%. This is the value that we will retrieve from our LDAP query in just a minute.

Name: USERSHAREDFOLDER, Value: %_USERSHAREDFOLDER
Now, click on Common.
Set the option "Run in logged-on user's security context" and Item-level targeting. Optionally, you may want to set "Apply once and do not reapply" if you would like the user to be allowed to change this setting on their own. Click Targeting.

We will set the Filter to "(&(objectClass=user)(sAMAccountName=%USERNAME%))", the binding leave to the default of LDAP (this will bind to the root of your domain controller).

For attribute we will be looking at userSharedFolder, and based on this attribute we will set the value of a temporary environment variable, _USERSHAREDFOLDER. This environment variable will not persist, but it will be used to set the value of the persistent environment variable USERSHAREDFOLDER (as specified on the first screen).

Note: if you are testing this, environment variables set by GPP apparently only apply on a new logon. gpupdate /force won't update the variable unless you allow it to log you out and then log back in.

Use the Environment Variable to set Default Save Location for Microsoft Office

Here are the registry keys that need to be set for each Microsoft Office Application:

--------------------------
Excel 2010
--------------------------
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
Value Name: DefaultPath
Value Type: REG_EXPAND_SZ
Value data: {%USERSHAREDFOLDER%}

For Excel 2013, setting is identical but change 14.0 to 15.0

--------------------------
Word 2010
--------------------------
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Word\Options
Value Name: DOC-PATH
Value Type: REG_EXPAND_SZ
Value data: {%USERSHAREDFOLDER%}

For Word 2013, setting is identical but change 14.0 to 15.0

--------------------------
Access 2010
--------------------------
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Settings
Value Name: Default Database Directory
Value Type: REG_SZ
Value data: {%USERSHAREDFOLDER%}

For Access 2013, setting is identical but change 14.0 to 15.0

--------------------------
Powerpoint 2010
--------------------------
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\PowerPoint\RecentFolderList
Value Name: Default
Value Type: REG_EXPAND_SZ
Value data: {%USERSHAREDFOLDER%}

For Powerpoint 2013, setting is identical but change 14.0 to 15.0

Go back to our GPO and expand User Configuration | Preferences | Windows Settings | Registry.
Create a new Registry preference. Leave Action to Update, and copy the appropriate key paths above (repeat for each application that you use in your environment). We use only Word and Excel: