How-To: Visual Studio Database Project and ADSI

If you are working with a Visual Studio Database Project and have to deal with data from the Active Directory via a Linked Server, you have to announce the data structure of the AD data in order to get the project compiled.

Step 1 - Linking to the Active Directory

First of all you have to connect your SQL Server to the AD permanently, by running following SQL script once on your SQL Server:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', 
    @srvproduct=N'Active Directory Service Interfaces', 
    @provider=N'ADSDSOObject', 
    @datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'mydomain\myadminuser',
    @rmtpassword='mypassword'
GO

EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', 
    @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Step 2 - Fetching ADSI data

To get data, use OpenQuery against the Linked Server. In order to get only persons and no system accounts, you should filter out all users, which has no firstname (givenName) or lastname (sn):

SELECT 
    UserPrincipalName, 
    DisplayName, 
    sAMAccountName AS [SamAccountName], 
    sn AS [LastName], 
    givenName AS [FirstName], 
    title AS [Title], 
    Mail as [MailAddress],
    department AS [Department],
    l AS [Location], 
    postalCode AS [PostCode], 
    streetAddress AS [Street],
    st AS [State]
FROM OpenQuery(ADSI, '
    SELECT 
        UserPrincipalName, 
        DisplayName, 
        sAMAccountName, 
        sn, 
        givenName, 
        department,
        title, 
        Mail, 
        l, 
        postalCode, 
        streetAddress, 
        st
    FROM ''LDAP://mydomain.de/DC=mydomain,DC=de'' 
    WHERE objectClass =  ''User'' 
    AND objectCategory = ''Person'' 
    AND sn=''*'' 
    AND givenName = ''*'' 
')    

In most cases you’re done with that … except your organisation has more the 900 users! Then you have to split the fetch in several requests, because SQL Server quits with an error, when trying to read more than 900 records via ADSI.

Best option is, to filter the ADSI statement by something like ‘get all user starting with a to j’, when you are sure, that in this case less than 900 records will be given back and repeat the statement several times and glue the data together via a UNION statement:

SELECT  
    UserPrincipalName,  
    DisplayName,  
    sAMAccountName AS [SamAccountName],  
    sn AS [LastName],  
    givenName AS [FirstName],  
    title AS [Title],  
    Mail as [MailAddress],  
    department AS [Department],  
    l AS [Location],  
    postalCode AS [PostCode],  
    streetAddress AS [Street],  
    st AS [State]  
FROM (  
    SELECT *  
    FROM OpenQuery(ADSI, '  
        SELECT  
            UserPrincipalName,  
            DisplayName,  
            sAMAccountName,  
            sn,  
            givenName,  
            department,  
            title,  
            Mail,  
            l,  
            postalCode,  
            streetAddress,  
            st  
        FROM ''LDAP://mydomain.de/DC=mydomain,DC=de''  
        WHERE objectClass =  ''User''  
        AND objectCategory = ''Person''  
        AND sn=''*''  
        AND givenName = ''*''  
        AND sAMAccountName <= ''j''
    ')  

    UNION ALL  

    SELECT *  
    FROM OpenQuery(ADSI, '  
        SELECT [...same as above]  
        FROM ''LDAP://mydomain.de/DC=mydomain,DC=de''  
        WHERE objectClass =  ''User''  
        AND objectCategory = ''Person''  
        AND sn=''*''  
        AND givenName = ''*''  
        AND sAMAccountName > ''j''  
        AND sAMAccountName < ''p''  
    ')

    UNION ALL  

    SELECT *  
    FROM OpenQuery(ADSI,  '  
        SELECT [...same as above]  
        FROM ''LDAP://mydomain.de/DC=mydomain,DC=de''  
        WHERE objectClass =  ''User''  
        AND objectCategory = ''Person''  
        AND sn=''*'' AND givenName = ''*''  
        AND sAMAccountName >= ''p''  
    ')  
) AD

When you store this as a VIEW, you can join it wherever you want on SQL Server:

CREATE VIEW [dbo].[vADUsers]
AS
    [...SQL code from above]

GO

Step 3 - SQL Server Database Project

If you work with a SQL Server Database Project, to have the complete structure of your database available in a version control system, you will get some reference errors on compiling and publishing your newly added SQL View vADUsers and on some objects, which rely on this View, because of following problems:

1. Project doesn’t know the Linked Server ADSI
2. The structure (fields) of the data source is unknown

Declare the Linked Server

To show the Project that there is a Linked Server called ADSI, just add following lines at the start of your view:

sp_addlinkedserver 'ADSI'
GO

CREATE VIEW [dbo].[vADUsers]
AS
    [...SQL code from above]

This mimics the adding of a Linked Server, but will be ignored by SQL Server on publish, because you already have a Linked Server with this name. The project is happy with it.

Declare the data structure

When you use the SQL-View vADUsers in a Stored Procedure for example, this object won’t compile, because the project knows nothing about the fields of the ADSI data source. The SELECT in the view is not enough. You have to add an empty SELECT to the View vADUsers, just for the declaration of the fields and without returning any records and join this via UNION with the other statements:

sp_addlinkedserver 'ADSI'
GO

CREATE VIEW [dbo].[vtADAllUsers]
AS

SELECT
    UserPrincipalName,
    DisplayName,
    sAMAccountName AS [SamAccountName],
    sn AS [LastName],
    givenName AS [FirstName],
    title AS [Title],
    Mail as [MailAddress],
    department AS [Department],
    l AS [Location],
    postalCode AS [PostCode],
    streetAddress AS [Street],
    st AS [State]
FROM (

    -- Fake SELECT to declare the structure of the view
    SELECT TOP 0
        '' UserPrincipalName,
        '' DisplayName,
        '' sAMAccountName,
        '' sn,
        '' givenName,
        '' department,
        '' title,
        '' Mail,
        '' l,
        '' postalCode,
        '' streetAddress,
        '' st

    UNION ALL

    SELECT *
    FROM OpenQuery(ADSI, '
        SELECT
            UserPrincipalName,
            DisplayName,
            sAMAccountName,
            sn,
            givenName,
            department,
            title,
            Mail,
            l,
            postalCode,
            streetAddress,
            st
        FROM ''LDAP://mydomain.de/DC=mydomain,DC=de''
        WHERE objectClass =  ''User''
        AND objectCategory = ''Person''
        AND sn=''*''
        AND givenName = ''*''
        AND sAMAccountName >= ''j''  
    ')

    UNION ALL  

    SELECT *  
    FROM OpenQuery(ADSI, '  
        SELECT [...same as above]  
        FROM ''LDAP://mydomain.de/DC=mydomain,DC=de''  
        WHERE objectClass =  ''User''  
        AND objectCategory = ''Person''  
        AND sn=''*''  
        AND givenName = ''*''  
        AND sAMAccountName < ''j''  
        AND sAMAccountName > ''p''  
    ')

    UNION ALL  

    SELECT *  
    FROM OpenQuery(ADSI,  '  
        SELECT [...same as above]  
        FROM ''LDAP://mydomain.de/DC=mydomain,DC=de''  
        WHERE objectClass =  ''User''  
        AND objectCategory = ''Person''  
        AND sn=''*''  
        AND givenName = ''*''  
        AND sAMAccountName <= ''p''  
    ')
) AD

Now, you can fetch data from Active Directory and store the code in a Database Project properly.

HAPPY CODING :)