UAG – How to Authenticate users against SQL Server

There are certain scenarios where the default authentication providers offered by Forefront UAG do not meet the requirements you have to authenticate your users. One example is the use of SQL Server Authentication which is not implemented in the UAG product by default. One of the most powerful features of UAG and/or IAG is the possibility to extend the authentication mechanisms by defining custom authentication repositories. In this scenario i would like to guide you through the process of how to implement an extranet scenario where you can authenticate extranet users against your Sharepoint 2010 installation using SQL Server as  a custom authentication provider

The Mission:

1. Setting up SQL-Express 2008 SP2
2. Configure the SQL-Express Instance
3. Add a custom authentication provider in UAG configuration
4. Add a custom repository.inc
5. Build a custom authentication function
6. Test the authentication against SQL

So, here we go.

1. Setting up SQL-Express 2008 SP2

---

In my environment i installed UAG in a existing Active Directory Domain. I also installed Sharepoint 2010 on a Windows 2008 R2 Enterprise Server and use SQL-Express as the database for Sharepoint. On this server i installed a second instance of SQL-Express 2008 SP2 for authentication. I will use this instance for claim-based FBA authentication in Sharepoint and i also like to use it as a custom repository in UAG. This is the first step to achieve SSO-Experience for external users.

After the installation of SQL-Express 2008 R2 we need to setup the ASP.Net Membership Database. An easy way to get the database up and running is to use the aspnet_regsql.exe tool. Open a command prompt and navigate to the following directory:
%windir%\Microsoft.NET\Framework64\v2.0.xxxx and start the aspnet_regsql.exe from there.

The Welcome to ASP.Net SQL Server Setup Wizard starts. Click next on the welcome screen. On the next screen select the Configure SQL Server for application services option and click next.

In the Select the server and database page enter your server\instance and the name of the database. If you do not enter a database name the default will be aspnetdb.

You can accept the authetication default values at this time because we will change the SQL-Server authentication mode to mixed mode later on. In the Confirm yout setting Dialog click next and the wizard will setup the database.

2. Configure the SQL-Express Instance

---

Run the SQL Server Configuration Manager tool from the SQL Server 2008 Program Group in your start menu. Enable the Shared Memory, Named Pipes and TCP/IP Protocols for your authentication instance and restart the SQL-Service.

Open the SQL-Server Managment Studio an make sure that the server is configured to allow remote connections. Rightclick on the instance node in the left pane and select properties. Select connections in the left pane and click the checkbox Allow remote connections to this server if it is not already selected.

Th next step is to create a SQL-Login and make this login a dbo for the aspnetdb database. In the left pane of the Management Studio expand the Security node and rightclick Logins. Select New Login…

On the General Page enter a Login name select the SQL Server authentication and enter a password. Clear the Enforce password policy checkbox and select aspnetdb as the default database.

Select the User Mapping page and click the aspnetdb checkbox. In the Database role membership for aspnetdb section select db_owner.

You can now create roles and add users to your aspnetdb. I use a tool called MembershipSeeder which you can download here.

3. Add a custom authentication provider in UAG configuration

---

To follow the next steps i assume that you have successfully configured an HTTPS portal trunk in UAG and you can access the portal with an external client. You should see the default login page like this, if you have configured portal authentication to use Active Directory Forest:

Because we want to give external users a way to authenticate without creating Active Directory Accounts for each of the external users we need to add a new instance of authentication servers to our portal configuration. This is done by clicking on the Configure… button in the Trunk Configuration section within your portal management.

In the Advanced Trunk Configuration Window select the Authentication tab and click Add..

In the Authentication and Authorization Dialog click Add… again

In the Add Authentication Server Dialog select Other as ServerType and type a ServerName like SQLServerAuthN in the ServerName textfield.

Click OK –> Select –> OK to close the dialogs. Activate the configuration changes and keep the ServerName you entered in mind.

4. Add a custom repository.inc

---

UAG is looking for a ServerName.inc file, in this case SQLServerAuthN.inc file, in the <Forefront UAG Installation Directory>\von\InternalSite\inc\CustomUpdate folder. I recommend that you copy the Repository.inc file from the <Forefront UAG Installation Directory>\von\InternalSite\samples to the <Forefront UAG Installation Directory>\von\InternalSite\inc\CustomUpdate and rename it to ServerName.inc in this case SQLServerAuthN.inc

The dependency is that the Servername you entered in the Add Authentication Dialog needs to match the Filename in the CustomUpdate folder. You can open the file with notepad and delete the functions ContinueAuthenticateRepositoryUser and ChangeRepositoryUserPassword because we will not use it at this time. The only functions which should be left in the file are the CheckCredentials, CheckChallange and AuthenticateRepositoryUser functions.

5. Build a custom authentication function

---

In this file we will write a custom function to authenticate users against SQL-Server. As an example you can use the following code:

function AuthenticateRepositoryUser(repository,user_name,password)

    'Preparing Variables
    dim oConn,oRecordset,sCommandText,sConnectionString
   
    'Setting up SQL-Command
    sCommandText="SELECT * FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserID=aspnet_Membership.UserID WHERE UserName='" & user_name & "' AND Password='" & password & "'"

    'Setting up SQL-Connection-String
    sConnectionString="Provider=SQLOLEDB;Data Source=srvmoss02\AuthN;Initial Catalog=aspnetdb;User ID=FormAuthUser;Password=Pa$$w0rd"

    'Setting up SQL-Connection Object
    set oConn=Server.CreateObject("ADODB.Connection")
    oConn.ConnectionString=sConnectionString

    'Open SQL-Connection
    oConn.Open

    'Send the query to SQL
    set oRecordset=oConn.Execute(sCommandText)
    HEAVY_TRACE "Cannot connect to SQL-Server"

    set authenticate_user_out = GetAuthenticatedUserDetails()
    if TypeName(authenticate_user_out) <> "Nothing" then
        ' User already authenticated by the Filter
        HEAVY_TRACE "Skipping authenticate user again"
        set AuthenticateRepositoryUser = authenticate_user_out
        exit function
    end if

    set authenticate_user_out = Server.CreateObject("UserMgrComLayer.AuthenticateUserOut")

    'Check if the recordset contains rows
    if not oRecordset.EOF then
        'User exists, password is ok
        if oRecordset("Password")=password then
            authenticate_user_out.Success = AUTHENTICATE_SUCCESS
            authenticate_user_out.ErrorCode = 0
            authenticate_user_out.Handle = 0
            authenticate_user_out.Message = ""
            authenticate_user_out.FullUserName = user_name
            set AuthenticateRepositoryUser = authenticate_user_out
        end if
    'Recordset contains no rows
    else
        authenticate_user_out.Success = AUTHENTICATE_FAIL
        authenticate_user_out.ErrorCode = ERROR_CODE_FAILED_TO_AUTHENTICATE
        set AuthenticateRepositoryUser = authenticate_user_out
    end if

end function

Save the file in the location as mentioned above.

6. Test the authentication against SQL

---

Open a browser window on a client and enter the URL to your portal. The Login-Screen appears.

Enter the User Name and Password of a user you created in the aspnetdb. Select the SQLServerAuthN in the authetication dropdown list and click Login.

Now you can access your portal authenticated with SQL-Express.

Let´s double check the success by viewing the session details in UAG Web Monitor.

Well done! Everything is fine now.