Export (0) Print
Expand All

Self-service data access using Windows PowerShell

SharePoint 2013
 

Applies to: SharePoint Server 2013 Enterprise

Topic Last Modified: 2014-07-08

Summary: Automatically grant users database access by using Windows PowerShell, a SharePoint list, and Task Scheduler.

This scenario describes how to use a Windows PowerShell script running as a scheduled task to automatically process user requests for access to data sources.

ImportantImportant:
This scenario applies only to SharePoint Server 2013.

In this article:

This article uses the AdventureWorks sample data set to show an example of automating database access requests using Windows PowerShell. By using Windows PowerShell, you can encapsulate your business processes into a script to make sure that they are consistently applied. Using Windows PowerShell also saves on administration time and allows specific Active Directory functions to be performed without the direct involvement of a domain administrator.

This article provides one example of how to use Windows PowerShell to automate governance processes in an organization. You may want to modify the script and processes to meet your specific needs.

You can install AdventureWorks and follow the steps in this article to create a working prototype, or you can use your own data sources instead of AdventureWorks.

This article describes how to:

  1. Create a SharePoint list where users can request access to data sources.

  2. Create a Windows PowerShell script that, running as a scheduled task, scans the SharePoint list, and adds users to the appropriate Active Directory group to give them the requested data access.

Before starting, note the following:

  • You will need a SharePoint site where you can create a SharePoint list. The procedures in this article assume that you are using a site with the BI Center template, though a Team site will work also. If you want to configure a site with the BI Center template, see Configure a Business Intelligence Center in SharePoint Server 2013.

  • The examples in this article use the AdventureWorks sample data set. You can use AdventureWorks or your own data sources. If you want to install AdventureWorks, see Configure AdventureWorks for Business Intelligence solutions.

  • You will need to create Active Directory accounts and groups as part of the procedures in this article. One of the accounts will need to be added to the Domain Admins group. We recommend using a test environment where you have direct access to a domain controller. Alternatively, you can work with your domain administrator to get the needed accounts and groups configured.

NoteNote:
If you have followed the steps in related articles in this series, some of the users and groups that you are asked to create may already exist in your environment. If that is the case, you can use the existing users and groups. There is no need to recreate existing users or groups.

In this section, we will:

  1. Configure the needed Active Directory accounts and groups.

  2. Configure data access for the Active Directory groups in SQL Server and Analysis Services.

  3. Create a SharePoint list for collecting data access requests.

Completing these steps will provide the infrastructure needed for the Windows PowerShell automation and task scheduling described in the following section.

This video shows the steps involved in setting up accounts and groups and creating a SharePoint list, as described in this article. (Time: 12:25.)

Video: Initial setup

Video (play button) icon

Create an Active Directory group for each data source to which you want to grant access. For AdventureWorks, we’ll create Active Directory groups for the following data sources:

  • AdventureWorks database   We’ll create a group called AdventureWorksDBUsers

  • AdventureWorks data warehouse   We’ll create a group called AdventureWorksDWUsers

  • AdventureWorks OLAP cube   We’ll create a group called AdventureWorksOLAPUsers

Use the following procedure to create an Active Directory group. Follow the procedure for each of the AdventureWorks groups listed above or each of your own data sources.

To create an Active Directory group
  1. Log on to a domain controller as a domain administrator.

  2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the domain node.

  4. Right-click Users, click New, and then click Group.

  5. On the New Object – Group dialog box, type a name for the group in the Group name text box, and then click OK.

For this scenario, we also need a domain administrator account. This account will be used to run a scheduled job that will add users to the Active Directory groups that we created above. You can use an existing domain administrator account, but we recommend that you create a new one specifically for this purpose. We’ll refer to this account as the domain proxy account.

Use the following procedure to create an Active Directory account.

To create an Active Directory account
  1. In Active Directory Users and Computers, right-click Users, click New, and then click User.

  2. In the Full name box, type a name for the user account (for example, ADProxyAccount).

  3. In the User logon name text box, type a logon name.

  4. Click Next.

  5. Type and confirm a password for the account.

  6. Choose the password options appropriate for your organization, and then click Next.

  7. Click Finish.

Once the account has been created, you must add it to the Domain Admins group in Active Directory. Use the following procedure to add the account to the Domain Admins group.

To add an account to the Domain Admins group
  1. In Active Directory Users and Computers, under Users, double-click the Domain Admins group.

  2. On the Members tab, click Add.

  3. Type the name of the account that you just created, and then click OK.

  4. Click OK.

Once you have completed the Active Directory configuration steps, the next step is to grant the appropriate database access to the new Active Directory groups.

Each data source to which you want to grant user access will need a login in SQL Server or a role in Analysis Services for the corresponding Active Directory group. For AdventureWorks, the databases require the following logins:

  • AdventureWorks database   A login for the AdventureWorksDBUsers Active Directory group

  • AdventureWorks data warehouse   A login for the AdventureWorksDWUsers Active Directory group

Use the following procedure to create a SQL Server login. Follow the procedure for each AdventureWorks databases or each of your own databases.

To create a SQL Server login
  1. Log in to the computer running SQL Server as a SQL Server administrator.

  2. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.

  3. On the Connect to Server dialog box, choose Database Engine from the Server type dropdown list, and then click Connect.

  4. Expand the Security node.

  5. Right-click Logins, and then click New Login.

  6. Click Search.

  7. On the Select User or Group dialog box, click Object Types.

  8. On the Object Types dialog box, select the Groups check box, and then click OK.

  9. On the Select User of Group dialog box, type the name of the Active Directory group for which you want to create the login (for example Contoso\AdvenureWorksDBUsers), and then click OK.

  10. In the Select a page pane, click User Mapping.

  11. Select the check box for the appropriate database (for example, AdventureWorks2012), and then under Database role membership, select the db_datareader check box.

  12. Click OK.

You must also create an Analysis Services role for access to any Analysis Services cubes. For AdventureWorks, the cube requires a role for the AdventureWorksOLAPUsers Active Directory group.

Use the following procedure to create an Analysis Services role. Create a role for the AdventureWorks OLAP cube or for your own Analysis Services cube.

To create an Analysis Services role
  1. Log in to the computer running SQL Server as a SQL Server administrator.

  2. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.

  3. On the Connect to Server dialog box, choose Analysis Services from the Server type dropdown list, and then click Connect.

  4. Expand Databases.

  5. Expand the AdventureWorksDW2012Multidimensional-EE database.

  6. Right-click Roles, and then click New Role.

  7. On the General page, type a name (for example, General Access) in the Role name text box.

  8. Select the Membership page, and then:

    1. Click Add.

    2. On the Select User or Group dialog box, click Object Types.

    3. On the Object Types dialog box, select the Groups check box, and then click OK.

    4. On the Select User of Group dialog box, type the name of the Active Directory group for which you want to create the login (for example Contoso\AdvenureWorksOLAPUsers), and then click OK.

  9. Select the Cube page, and then choose Read from the Access dropdown list for both cubes.

  10. Click OK.

Once SQL Server and Analysis Services have been configured with the needed logins and roles, the next step is to create a SharePoint list to use for data access requests.

In the SharePoint list, we need fields that can be used to define a database access request. This includes the alias of the user requesting access, the data source that they wish to access, and an area for them to specify the business justification for the request. We also need a field that the Windows PowerShell script can use to track the status of the request, as well as a field where logging information can be stored.

Use the following procedure to create the SharePoint list. You can add additional fields if you need them, but be sure not to omit any fields or the Windows PowerShell script will not work.

To create a SharePoint list
  1. Go to the site where you want to create the list.

  2. In the left navigation, click Site Contents.

  3. Click add an app.

  4. Click Custom List.

  5. In the Adding Custom List dialog box, type Self-service Data Access in the Name text box, and then click Create.

    NoteNote:
    You can choose a different name, but you will need to update the list name in the Windows PowerShell script provided later in this article.
  6. On the Site Contents page, click the Self-service Data Access list.

  7. On the list page, on the List tab in the ribbon, click List Settings.

  8. On the list settings page, under Columns, use the Create column link to create columns as described in the following table.

     

    Field name Description Options

    Status

    The status of the request.

    Choose the Choice (menu to choose from) option and type New, Succeeded, and Failed for the available choices in the Type each choice on a separate line box. Specify a Default value of New.

    Data Source

    A list of the available data sources.

    Choose the Choice (menu to choose from) option and type AdventureWorks DB, AdventureWorks DW, and AdventureWorks OLAP for the available choices in the Type each choice on a separate line box. (If you are using your own data sources, type those here instead of the AdventureWorks options.)

    User alias

    The alias of the user for whom data access is being requested.

    Use the Single line of text option, and make sure this is a required field by selecting Yes under Require that this column contains information.

    Business justification

    This is an optional field in case you want to keep a record of why a user is requesting data access.

    Use the Multiple lines of text option for this field.

    Log

    A log generated by the Windows PowerShell script describing the actions undertaken by the script.

    Use the Multiple lines of text option for this field.

In order for our Windows PowerShell script to work, the domain proxy account requires read/write access to this list. This is most easily accomplished by granting the account Edit access to the site where the list is located.

To grant access to a site
  1. On the site where the list is located, click Share.

  2. Type the user account for the domain proxy account (for example, contoso\ADProxyAccount).

  3. Click Show Options.

  4. From the Select a group or permission level dropdown list, choose [Edit].

  5. Click Share.

Once you have created the SharePoint list and granted Edit permissions to the domain proxy account, the next step is to automate the process of granting access to the data sources by tying the list together with a Windows PowerShell script. The next section describes the process of creating the script and automating it as a scheduled task.

In this section we will:

  1. Create a Windows PowerShell script that reads the SharePoint list and applies the requested permissions in Active Directory.

  2. Create a scheduled task in Windows Server that runs the script at a specified interval.

With the script in place and the scheduled task running, users can add their database requests to the SharePoint list and they will be automatically handled within a short time of being added to the list.

NoteNote:
The script performs functions in Active Directory. In order for the Active Directory Windows PowerShell module to load, the Remote Server Administration Tools must be configured on the server where you are going to run the script. Remote Server Administration Tools is a feature that you can add through Server Manager.

This video shows the steps involved in configuring a Windows PowerShell script that reads the SharePoint list and applies the requested permissions, and configuring a scheduled task to run the Windows PowerShell script. (Time: 10:35.)

Video: Automation and scripting

Video (play button) icon

To automate the process of granting access to the requested data source, we’ll use a Windows PowerShell script. The sample script below performs the following basic functions:

  1. Loads the SharePoint list where users request data access.

  2. Cycles through each list item looking for a status of New.

  3. For each item with a status of New, the script adds the user specified in the User alias field to the Active Directory group associated with the data source specified in the Data Source filed.

  4. The status of the list item is changed from New to Succeeded or Failed depending on the outcome.

  5. A brief log of events is written to the Log field in the list item.

Use the following procedure to create the Windows PowerShell script. Create the script on your SharePoint Server 2013 application server.

To create a Windows PowerShell script
  1. On the SharePoint Server application server, click Start, click Run, type PowerShell_ISE.exe, and then click OK.

  2. Copy and paste the following script into the Windows PowerShell editor:

    # Add SharePoint and Active Directory cmdlets
    if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
         Add-PSSnapin "Microsoft.SharePoint.PowerShell"
     } 
     if ((Get-Module ActiveDirectory -ErrorAction SilentlyContinue) -eq $null) {
         Import-module ActiveDirectory
     }
    
    # Get-SPWeb for the site where the list is located
    $ListWeb = Get-SPWeb http://contoso-appsrv1/sites/BICenter
    
    # Get the list by name
    $ListName = $ListWeb.Lists["Self-service Data Access"]
    
    # Get the list items
    $ListItems = $ListName.items
    
    # Loop through each list item
    $ListItems | foreach {
    
    $ListItem = $_
    
    # Look for list items with a status of "New"
    if($ListItem["Status"] -eq "New") {
    
        # Clear out the variables for each loop
        $LogText = ""
        $ActionStatus = ""
        $ActionTime = ""
        $DataSourceADGroup = ""
    
    
        # Add-ADGroupMember requires alias with no domain, so remove the domain\ if it exists
        $SharePointUser = $ListItem["User alias"]
        $SharePointUser = $SharePointUser.ToLower()
    
        # Replace contoso in the line below with the name of your domain
        $SharePointUser = $SharePointUser.Replace("contoso\","")
       
        # Determine data source being requested and assign the associated AD group to the $DataSourceADGroup variable
        if($ListItem["Data Source"] -eq "AdventureWorks DB") {$DataSourceADGroup = "AdventureWorksDBUsers"}
        if($ListItem["Data Source"] -eq "AdventureWorks DW") {$DataSourceADGroup = "AdventureWorksDWUsers"}
        if($ListItem["Data Source"] -eq "AdventureWorks OLAP") {$DataSourceADGroup = "AdventureWorksOLAPUsers"}
    
        # Add user request and data source information to the log variable
        $LogText += "User " + $ListItem["User alias"] + " requesting access to " + $DataSourceADGroup + "`n`r"
    
        # Try to add the requested user to the AD group
        try{
            Add-ADGroupMember -Identity $DataSourceADGroup -Members $SharePointUser
    
            # Note success in the log variable
            $LogText += "Adding " + $ListItem["User alias"] + " to " + $DataSourceADGroup + "`n`r"
            
            # Note success in the $ActionStatus variable for later update to the SharePoint list
            $ActionStatus = "Succeeded"
            }
        catch{
            # If Add-ADGroupMember fails, so note in the log variable
            $LogText += "Error: could not add " + $ListItem["User alias"] + " to " + $DataSourceADGroup + ": " + $_ + "`n`r"
            
            # Note failure in the $ActionStatus variable for later update to the SharePoint list
            $ActionStatus = "Failed"
            }
        
        # Get the current date and time
        $ActionTime = Get-Date
    
        # Append the date, time, and final status to the log variable
        $LogText += $ActionStatus + " " + $ActionTime
        
        # Update the list item with the final status and the log text
        $ListItem["Status"] = $ActionStatus
        $ListItem["Log"] = $LogText
        $ListItem.Update()
        }
    }
    
    
  3. Click File, click Save, and save the file to a location on the local disk.

ImportantImportant:
For test purposes, you can save the Windows PowerShell script to a convenient location. In a production environment, have your domain administrator create a folder that requires domain administrator privileges to access and save the script there. This will increase security and help prevent unauthorized changes to the script.

Depending on your environment, you may need to update the following script items:

  • The URL for the site where the SharePoint list is located is set to http://contoso-appsrv1/sites/BICenter. Update this value to the URL for your environment. This is stored in the $ListName variable.

  • On the line $SharePointUser = $SharePointUser.Replace("contoso\","") you must replace contoso with your domain.

  • The Data Source and $DataSourceADGroup values are configured for the AdventureWorks options discussed in this article. If you are using your own data sources, you must update these values to correspond to your data sources.

  • If you named any of the SharePoint list fields differently than those noted in the procedure for creating the list, you must update the script to use the values that you chose. Use the values listed in the procedure for creating the list to find the corresponding values in the script.

Once you have the script saved to your local disk, the next step is to configure the domain proxy account as a script administrator.

In order to successfully run the Windows PowerShell script, the domain proxy account needs permissions to make updates to the SharePoint list by using Windows PowerShell. This is accomplished by using the Add-SPShellAdmin Windows PowerShell cmdlet to give the account shell administrator privileges.

Use the following procedure to configure the domain proxy account as a shell administrator.

NoteNote:
If you previously configured the domain proxy account as a shell administrator in another scenario in this series, there is no need to redo the procedure.
To configure a script administrator
  1. Log in to your SharePoint Server application server as a Farm Administrator.

  2. Click Start, click All Programs, click Microsoft SharePoint 2013 Products, right-click SharePoint 2013 Management Shell, and then click Run as administrator.

  3. In the Windows PowerShell command window, type the following syntax:

    Get-SPContentDatabase | Add-SPShellAdmin -UserName domain\username
    

    For example:

    Get-SPContentDatabase | Add-SPShellAdmin -UserName Contoso\ADProxyAccount
    
  4. Press Enter.

Once the domain proxy account has been added as a shell administrator, it can be used to run the Windows PowerShell script as part of a scheduled task.

By using a scheduled task to run the Windows PowerShell script on a repeating basis, we can automate the addition of the users requesting data access to the appropriate Active Directory group.

Use the following procedure to create a scheduled task.

To create a scheduled task
  1. Log into your SharePoint Server application server as a local administrator.

  2. Click Start, click Administrative Tools, and then click Task Scheduler.

  3. In the Actions pane, click Create Task.

  4. On the General tab:

    1. Type a name for the task in the Name text box.

    2. Choose the Run whether user is logged on or not option.

    3. Select the Run with highest privileges check box.

  5. On the Triggers tab:

    1. Click New.

    2. Select the Repeat task every check box, and choose 10 minutes from the dropdown list.

    3. Choose Indefinitely from the for a duration of dropdown list.

    4. Click OK.

  6. On the Actions tab:

    1. Click New.

    2. In the Program/script text box, type PowerShell.exe.

    3. In the Add arguments (optional) text box, type the path and filename of your Windows PowerShell script (for example, C:\Scripts\SelfServiceDataAccess.PS1))

    4. Click OK.

  7. Click OK.

  8. Type the username and password of the account that you want to run the script. This is the Active Directory domain administrator account that you configured as a Windows PowerShell script administrator.

Once the scheduled task has been created, the setup steps for this scenario are complete. The next step is to test the functionality by adding items to the SharePoint list.

In this section, we add some items to the SharePoint list to test the functionality of the script and the scheduled task.

This video shows some examples of testing this scenario. (Time: 7:46.)

Video: Test and verify functionality

Video (play button) icon

Try the following actions to verify that the script and scheduled task are working properly:

  • Add an item to the list. Check the task in Task Scheduler to see when the task is next scheduled to run. Wait until the task has run, then check the SharePoint list to make sure the Status and Log fields have been updated. Check the appropriate Active Directory group to ensure the specified user has been added.

  • Add an item to the list for each data source that you have (the AdventureWorks database, data warehouse, and OLAP cube in our example). In Task Scheduler, right-click the task, and then click Run (to avoid having to wait for the next scheduled time). Check the SharePoint list and Active Directory groups to ensure you’re getting the expected results.

  • Create two identical list items (two requests for the same user and data source). Run the scheduled task. This should produce an error in the second request since the user was already added to the appropriate Active Directory group in the first request. Note that the log entry for the second item specifies this error, which was returned by the Add-ADGroupMember cmdlet.

  • Have the users who have been granted data access try to access the data sources from Excel.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft