locked
Access SharePoint list data using ADF RRS feed

  • Question

  • Hi,

    This seem to be the most discussed issue all over, however could not find any solution or relevant documentation to get it working. 

    The set up is quite simple, there is a SharePoint online list and we need to establish a connection to it using Azure data factory. Currently we do have an option to create OData Connection but it only seems to support limited authentication options e.g. Basic and Windows. 

    If we choose the Basic Authentication option, we get to enter username and password and we assume that Office 365 creds needs to be entered there but it comes back with an error.

    SPList data / OData URL format is as below (this works when requested from browser)

    https://tenant.sharepoint.com/sites/some_site/_api/web/lists/getbytitle('somelist')/items

    Can someone please point how to get this working or if anyone ever has managed to get the data out of SharePoint list using OData connection option?


    Bhushan Gawale | RapidCircle


    Thursday, June 13, 2019 6:18 AM

All replies

  • I recently received a video.  Below I will transcribe it.  I will update to improve quality (screenshots, more details).  

    Prerequisites:
    A X509 certificate (.cer, .pfx) - see script at bottom to create cert.
    Be Azure Tenant Admin

    Step 1: Register an application in Azure Active Directory

    Step 2: Bind the certificate to the application registered in Azure Active Directory
    Use the certificate .cer file

    Step 3: Apply permissions for the application
    To do so, go to the App that is registed in AAD. 
    Then Under 'Manage' tab, go to 'API Permissions', then click on 'Add a permission'. 
    You will see a 'Request API permissions' window, under 'Microsoft APIs', click on "Sharepoint" and select 'Application permisssions'.
    Under 'Select Permisssions', expand 'Sites',and select "Sites.FullControl.All" and click on Add 'Permissions' button.

    Step 4: Grant Permissions (Only Azure Tenant Admin can grant this permission)
    Continue from the same API permissions window.  Under 'Grand Consent', click 'Grand admin consent for ______'.

    Step 5: Create Linked Service in Azure Data Factory
    Create a new OData Linked Service.  Set the Authentication type to 'AAD Service Principal with Cert'.
    Service URL:  https://{your site url}/_vti_bin/listdata.svc
    AAD Resource: https://{tenant}.sharepoint.com
    Tenant:  your Directory (tenant) ID
    Service Principal ID: your application (client) ID
    Service Principal embedded cert: upload the certificate pfx file
    Embedded cert password:  The password you used when creating the certificate files.

    Step 6: Preview lists
    Create a Dataset for the above Linked Service
    Create a copy activity for the above Dataset
    Preview Data

    Helpful reference: https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azuread

    Certificate creation script:
    #Requires -RunAsAdministrator
    <#
    .SYNOPSIS
    Creates a Self Signed Certificate for use in server to server authentication
    .DESCRIPTION
    .EXAMPLE
    PS C:\> .\Create-SelfSignedCertificate.ps1 -CommonName "MyCert" -StartDate 2015-11-21 -EndDate 2017-11-21
    This will create a new self signed certificate with the common name "CN=MyCert". During creation you will be asked to provide a password to protect the private key.
    .EXAMPLE
    PS C:\> .\Create-SelfSignedCertificate.ps1 -CommonName "MyCert" -StartDate 2015-11-21 -EndDate 2017-11-21 -Password (ConvertTo-SecureString -String "MyPassword" -AsPlainText -Force)
    This will create a new self signed certificate with the common name "CN=MyCert". The password as specified in the Password parameter will be used to protect the private key
    .EXAMPLE
    PS C:\> .\Create-SelfSignedCertificate.ps1 -CommonName "MyCert" -StartDate 2015-11-21 -EndDate 2017-11-21 -Force
    This will create a new self signed certificate with the common name "CN=MyCert". During creation you will be asked to provide a password to protect the private key. If there is already a certificate with the common name you specified, it will be removed first.
    #>
    Param(
    
       [Parameter(Mandatory=$true)]
       [string]$CommonName,
    
       [Parameter(Mandatory=$true)]
       [DateTime]$StartDate,
       
       [Parameter(Mandatory=$true)]
       [DateTime]$EndDate,
    
       [Parameter(Mandatory=$false, HelpMessage="Will overwrite existing certificates")]
       [Switch]$Force,
    
       [Parameter(Mandatory=$false)]
       [SecureString]$Password
    )
    
    # DO NOT MODIFY BELOW
    
    function CreateSelfSignedCertificate(){
        
        #Remove and existing certificates with the same common name from personal and root stores
        #Need to be very wary of this as could break something
        if($CommonName.ToLower().StartsWith("cn="))
        {
            # Remove CN from common name
            $CommonName = $CommonName.Substring(3)
        }
        $certs = Get-ChildItem -Path Cert:\LocalMachine\my | Where-Object{$_.Subject -eq "CN=$CommonName"}
        if($certs -ne $null -and $certs.Length -gt 0)
        {
            if($Force)
            {
            
                foreach($c in $certs)
                {
                    remove-item $c.PSPath
                }
            } else {
                Write-Host -ForegroundColor Red "One or more certificates with the same common name (CN=$CommonName) are already located in the local certificate store. Use -Force to remove them";
                return $false
            }
        }
    
        $name = new-object -com "X509Enrollment.CX500DistinguishedName.1"
        $name.Encode("CN=$CommonName", 0)
    
        $key = new-object -com "X509Enrollment.CX509PrivateKey.1"
        $key.ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
        $key.KeySpec = 1
        $key.Length = 2048 
        $key.SecurityDescriptor = "D:PAI(A;;0xd01f01ff;;;SY)(A;;0xd01f01ff;;;BA)(A;;0x80120089;;;NS)"
        $key.MachineContext = 1
        $key.ExportPolicy = 1 # This is required to allow the private key to be exported
        $key.Create()
    
        $serverauthoid = new-object -com "X509Enrollment.CObjectId.1"
        $serverauthoid.InitializeFromValue("1.3.6.1.5.5.7.3.1") # Server Authentication
        $ekuoids = new-object -com "X509Enrollment.CObjectIds.1"
        $ekuoids.add($serverauthoid)
        $ekuext = new-object -com "X509Enrollment.CX509ExtensionEnhancedKeyUsage.1"
        $ekuext.InitializeEncode($ekuoids)
    
        $cert = new-object -com "X509Enrollment.CX509CertificateRequestCertificate.1"
        $cert.InitializeFromPrivateKey(2, $key, "")
        $cert.Subject = $name
        $cert.Issuer = $cert.Subject
        $cert.NotBefore = $StartDate
        $cert.NotAfter = $EndDate
        $cert.X509Extensions.Add($ekuext)
        $cert.Encode()
    
        $enrollment = new-object -com "X509Enrollment.CX509Enrollment.1"
        $enrollment.InitializeFromRequest($cert)
        $certdata = $enrollment.CreateRequest(0)
        $enrollment.InstallResponse(2, $certdata, 0, "")
        return $true
    }
    
    function ExportPFXFile()
    {
        if($CommonName.ToLower().StartsWith("cn="))
        {
            # Remove CN from common name
            $CommonName = $CommonName.Substring(3)
        }
        if($Password -eq $null)
        {
            $Password = Read-Host -Prompt "Enter Password to protect private key" -AsSecureString
        }
        $cert = Get-ChildItem -Path Cert:\LocalMachine\my | where-object{$_.Subject -eq "CN=$CommonName"}
        
        Export-PfxCertificate -Cert $cert -Password $Password -FilePath "$($CommonName).pfx"
        Export-Certificate -Cert $cert -Type CERT -FilePath "$CommonName.cer"
    }
    
    function RemoveCertsFromStore()
    {
        # Once the certificates have been been exported we can safely remove them from the store
        if($CommonName.ToLower().StartsWith("cn="))
        {
            # Remove CN from common name
            $CommonName = $CommonName.Substring(3)
        }
        $certs = Get-ChildItem -Path Cert:\LocalMachine\my | Where-Object{$_.Subject -eq "CN=$CommonName"}
        foreach($c in $certs)
        {
            remove-item $c.PSPath
        }
    }
    
    if(CreateSelfSignedCertificate)
    {
        ExportPFXFile
        RemoveCertsFromStore
    }


    Monday, June 17, 2019 10:37 PM
  • I need help.  I am on step 4 above and when I click the "Grant Admin Consent for Default Directory" button, I receive the following error message: 

    This app requires access to a service (\"https://*.dps.mil/\") that your organization is not subscribed to or has not enabled. Contact your IT Admin to review the configuration of your service subscriptions.

    I have shared the SharePoint application with the Azure Admin.  Azure and O365 are not in the same Tenant.


    • Edited by aholowaty Wednesday, December 18, 2019 5:04 PM
    Wednesday, December 18, 2019 4:55 PM
  • Hello aholowaty and thank you for your question.  Your case may be special.  Please send me an email.  I will find your email faster if the subject is 'ATTN: MartinJaffer-MSFT

    For a deeper investigation we need additional information . Please send an email to AzCommunity@microsoft.com with the below details. We will respond to the thread with our findings.
    Thread URL:
    Subscription ID:  

    Friday, December 20, 2019 10:58 PM
  • Hi Martin,

    Thank you for sharing the steps, i have been able to go half way but got stuck in ServiceURL:

    When i was creating LinkedService through ADF, i am not sure what would be my service URL:

    Below is my sharepoint site url:

    https://chayanupadhyay.sharepoint.com/sites/SampleSite

    I have created one List : https://chayanupadhyay.sharepoint.com/sites/SampleSite/Lists/TestList/AllItems.aspx

    I am not sure what would be my service URL while creating the Linked Service in ADF, PFB the snapshot for your reference:

    I hope this information will help to get more clarity on the issue.

    Please let me know if you need more information and Thanks again for your help.

    Regards,

    Chayan

    Thursday, January 2, 2020 9:54 AM
  • The service URL is :

    https://{your_site_url}/_vti_bin/listdata.svc

    This would make yours be:

    https://chayanupadhyay.sharepoint.com/sites/SampleSite/_vti_bin/listdata.svc

    Thursday, January 9, 2020 10:57 PM