Using SQL to make a SharePoint Contact List

I needed to make a list of contact information for all of our offices using the contact information in Active Directory. All of the "regular" solutions were not applicable because the offices were contained in the same Organizational Unit as some other contacts, requiring the offices be filtered out from the other contacts. The solution was to use the SQL server that is used to host the databases for the SharePoint site to query AD and return the filtered list.

The first step is to create the SQL Linked server. I used SQL Management Studio to do this. Under "Server Objects, right click on "Linked Servers" and the click "New Linked Server".

Here are the settings you need to make. The "Data Source" is the name of your AD server.

On the "Security" page, enter the security settings:

Create the view:

CREATE VIEW [dbo].[vwWxyzOffices]
AS
SELECT sn[Country], homephone[Business Phone], l[City], streetAddress[Street Address], postalCode[Postal Code],
mail[Email], facsimileTelephoneNumber[Fax], mobile[Mobile Phone], info[Notes], wWWHomePage[Website]
FROM OPENQUERY(ADSI,
'SELECT givenName, sn, homephone, l, streetAddress, postalCode, facsimileTelephoneNumber,
mail, mobile, info, wWWHomePage
FROM ''LDAP://OU=Contacts,DC=Wxyz,DC=local''
WHERE objectClass = ''contact''  and givenName = ''Wxyz''')
GO

Finally, add the view into SharePoint as an external data source and use it in your SharePoint list!