Search Active Directory Objects with PowerShell, SQL, BCS and SharePoint Search - Part 2

SharePoint provides import and search capabilities for User Accounts and Security Groups stored in Active Directory (AD) via the use of FIM, the User Profile Service, My-Sites and the SharePoint Search Service.

The following article(s) detail how other Objects stored in Active Directory (AD) can be made available in SharePoint Search Results via the use of PowerShell, SQL, BCS and SharePoint Search Display Templates.


Part 2:

Next we’re going to create an External Content Type (ECT) using the Business Connectivity Service (BCS) available in SharePoint.

Before we can do this, we first need to store some ‘Windows Credentials” in the SharePoint ‘Secure Store’, which will be used by the Business Connectivity Service to connect to the SQL Database previously created in ‘Part 1’.


Using the PowerShell below, this can be achieved by executing the following command:

.\CreateSecureStoreTargetApp.ps1 -SiteUrl "http://<yourspwebappurl>" -SecureStoreTargetAppName "AD2SPDataCreds" -SecureStoreTargetAppDispName "AD2SPDataCreds" -SecureStoreTargetAppEmail "<>" -SecureStoreTargetAppAdmin "<yourdomain>\SP_Admin" -SecureStoreTargetAppGroup "<yourdomain>\Domain Users" -SecureStoreTargetApp_CredUserAccount "<yourdomain>\yourSQL_Access_Account" -SecureStoreTargetApp_CredPassword "<yourSQL_Access_Account_Password>"



Now we have credentials stored, we can create the External Content Type for the ‘ADComputers’ SQL table.

External Content Types can be created using SharePoint Designer…




However, for this particular example (and in true Blue Peter style) we’re going to ‘Import’ a BCS Model file (.bdcm) which I created earlier and exported from SharePoint Designer...


To do this we’re going to first use PowerShell to ‘Update the BCS Model’ with the relevant SQL Server, SQL Database, SQL Table and Secure Store Target App information.

Before finally Importing the ‘BCS Model’ into the Business Connectivity Service and setting the permissions accordingly


Using the PowerShell below (along with the ‘ADComputer-template.bdcm’ file provided), this can be achieved by executing the following command:

.\UpdateImport_BCSModel.ps1 -SiteUrl "http://<yourspwebappurl>" -BCSModelTemplateFileName "ADComputer-template.bdcm" -BCSModelOutputFileName "ADComputers.bdcm" -DbServerName "<yourSQLServerName>" -DbName "<yourAD2SPDatabaseName>" -TableName "ADComputers" -SecureStoreAppID "<yourAD2SPDataSecureStoreTargetAppName>" -BCSEntityAdmin "<yourDomain>\SP_Admin" -BCSEntityUsers "<yourDomain>\SP_Crawl_Account"





Read Part 3 ...


Please download attached zip for "full/complete .ps1" files - Examples provided above are simplified abbreviation


Published by aobi


No Comments