Deploying a federation server with a SQL database

This post is as much for me as anyone else (because I’ve done and forgotten how to do this three times thus I am writing it down). If you want to stand up a new Active Directory Federation Services (AD FS) 2.0 federation server as the first server in a farm using a SQL backend this is what you need to do.

Note. When setting up labs I generally configure “farms” of one server. For me this is more representative of what I’ll encounter in the real world. Even if you’re not planning on scaling out at the moment it is probably at least worth testing a configuration that will likely be deployed in production at least once before you try for real in pre-production.

This post dives directly into the configuration. I’m therefore assuming the following:

  • You’ve built either a full installation of Windows Server 2008 x64 Service Pack 2 or Windows Server 2008 R2 Service Pack 1.
  • You’ve joined the machine to your AD DS domain.
  • You’ve installed AD FS 2.0.
  • You’ve enrolled a Web Server certificate and created a HTTPS binding for the Default Web Site.
  • You’ve created a service account for AD FS 2.0 and created the necessary HOST Service Principal Name (SPN).

Information on how to perform the latter three bullets can be found by following the links on this TechNet checklist:

AD FS must be installed first. The configuration is then achieved using FSCONFIG.EXE.

SQL Server

The FSCONFIG.EXE installation/configuration tool will create the SQL database if the context it is running under is able to do so, i.e. you have the necessary permissions in the SQL server you tell the tool to use. However if you don’t have the necessary permissions to do this then you can generate the necessary TSQL and provide .SQL scripts to the SQL Server administrators.

To generate the SQL scripts you run FSCONFIG with the following command line parameters:

fsconfig GenerateSQLScripts /ServiceAccount CORP\svcadfs /ScriptDestinationFolder d:\deploy\adfs\adfsSqlScripts 

Configuring the AD FS server with an account that has permission to create the SQL database

A clean installation, without the need for SQL DBA scripts, is achieved using FSCONFIG with the CREATESQLFARM command as follows.

PS C:\Program Files\Active Directory Federation Services 2.0> .\FsConfig.exe createsqlfarm /serviceaccount
CORP\svcadfs /sqlconnectionstring "database=adfscfg;server=sql\idmdbs;integrated security=sspi" /autocertrolloverenabled
Enter a password for CORP\svcadfs:******** 
Creating a new federation server farm... 
Stopping the AD FS 2.0 Windows Service... 
Passed 
Configuring the AD FS configuration database... 
Passed 
Creating an Active Directory container for sharing signing and decryption certificates... 
Passed 
Configuring certificates, service settings, and endpoints... 
Passed 
Deploying the browser sign-in Web site to the /adfs/ls virtual directory under the Default Web Site in IIS... 
Passed 
Starting the AD FS 2.0 Windows Service... 
Passed 
Creating default claim set... 
Passed 
Creating default claim acceptance rules on the Active Directory claims provider trust 
Passed 
Configuration is complete. 

Here’s the screenshot:

Configuring the AD FS server with an account that does not have permission to create the SQL database

If the database exists, i.e. a SQL DBA pre-created it and you want to start over, as opposed to join, you use the CLEANCONFIG switch in conjunction with the CREATESQLFARM, as follows.

C:\Program Files\Active Directory Federation Services 2.0>fsconfig createsqlfarm 
/serviceaccount corp\svcadfs /sqlconnectionstring "database=adfsconfiguration;server=adfs\idmsys;integrated security=sspi" 
/autocertrolloverenabled /cleanconfig 
Enter a password for corp\svcadfs:******** 
Creating a new federation server farm... 
Stopping the AD FS 2.0 Windows Service... 
Passed 
Configuring the AD FS configuration database... 
Passed 
Creating an Active Directory container for sharing signing and decryption certificates... 
Passed 
Configuring certificates, service settings, and endpoints... 
Passed 
Deploying the browser sign-in Web site to the /adfs/ls virtual directory under the Default Web Site 
in IIS... 
Passed 
Starting the AD FS 2.0 Windows Service... 
Passed 
Creating default claim set... 
Passed 
Creating default claim acceptance rules on the Active Directory claims provider trust 
Passed 
Configuration is complete. 

Here’s the screenshot:

Joining an additional node to the farm

Once the farm is created you join additional nodes to the farm using the following syntax where the thumbprint is that of the service certificate configured within the farm.

C:\Program Files\Active Directory Federation Services 2.0>fsconfig joinsqlfarm /serviceaccount corp\svcadfs /sqlconnectionstring "database=adfsconfiguration;server=adfs\idmsys;integrated security=sspi" /certthumbprint "91 f4 55 10 e9 04 18 ea 16 af 0b eb a5 13 ce f1 36 64 6c 69" 
Enter a password for corp\svcadfs:******** 

A discussion on the certificate requirement and options is probably a good subject for a future blog post.

Wrap-up

I used automatic certificate rollover above because I’m lazy. I installed the service, setup the farm and then changed the signing and decrypting certificates using the UI. This is a little backward for anywhere other than a lab. In a real environment the certificates will be installed and you’ll utilise the following command instead:

C:\Program Files\Active Directory Federation Services 2.0>fsconfig createsqlfarm /serviceaccount corp\svcadfs /sqlconnectionstring "database=adfsconfiguration;server=adfs\idmsys;integrated security=sspi" /signingcertthumbprint "91 f4 55 10 e9 04 18 ea 16 af 0b eb a5 13 ce f1 36 64 6c 69" /decryptcertthumbprint "91 f4 55 10 e9 04 18 ea 16 af 0b eb a5 13 ce f1 36 64 6c 69" 
Enter a password for corp\svcadfs:********

If I were to use any kind of real excuse for this I would say it was so that I could post both variants of the command: that that generates the self-signed certificate and that that specifically references an existing, proper certificate.

Advertisements

About Paul Williams

IT consultant working for Microsoft specialising in Identity Management and Directory Services.
This entry was posted in AD FS and tagged , , , , . Bookmark the permalink.

11 Responses to Deploying a federation server with a SQL database

  1. thvuy says:

    Paul, I tried that very last command you posted, and I can’t seem to get it right… You don’t happen to have any additional insights? see: http://setspn.blogspot.com/2012/04/configuring-adfs-with-custom-token.html Thanks in advance!

  2. thvuy says:

    Hey Paul, I tried this very last command you posted (using custom certificates) but I seem to run into some trouble… You don’t happen to have any additional insights? see: http://setspn.blogspot.com/2012/04/configuring-adfs-with-custom-token.html Thanks in advance!

  3. dotnetrevolution says:

    Hi Paul,
    thanks for you post.

    I have a problem when i deployed ADFS with SQL Server 2008 R2 SP2 Database. The error logged is 102 and 220 with this description ADMIN0012 : OperationFault

    Thanks for your help

  4. RKS85 says:

    Hi Paul, The sql connectionstring you used in CREATESQLFARM should it not be /sqlconnectionstring “database=adfsconfiguration instead of /sqlconnectionstring “database=adfscfg ? As far as i can remember the db name cannot be changed.

  5. Pingback: Windows Server의 ADFS(Active Directory Federation Service), IDentity 처리에 대한 고려 및 클라우드 시대를 위한 확장 - 꼬알라의 하얀집... - Site Home - TechNet Blogs

  6. Kevin says:

    Hi Paul,

    I am deploying ADFS on Win 2012 R2. The fsconfig file doesn’t seem to exist. Is there an article for Win 2012 R2? Thank you

    • There’s no FSCONFIG in AD FS 3.0. Instead you use Install-AdfsFarm. To deploy using SQL as the configuration DB store instead of WID you use the -SQLConnectionString parameter. There’s an example here:
      https://technet.microsoft.com/en-us/library/dn486807.aspx

      You need to look at the PowerShell section. In the note it provides the example command – here’s the snippet:

      If you want to create a SQL Server server farm, you must have an instance of SQL Server already installed and operational.
      You can use the following command to create the first federation server in a new farm that uses an instance of SQL Server: Install-AdfsFarm -CertificateThumbprint -FederationServiceName -GroupServiceAccountIdentifier \$ -SQLConnectionString “Data Source=<SQL_Host_Name?\;Integrated Security=True” where is the name of the server on which SQL Server is running, and is the name of the instance of SQL Server. If you use the default instance of SQL Server, use a SQLConnectionString value of “Data Source=;Integrated Security=True”.

  7. Pingback: HowTo – Install and Configure Microsoft Active Directory Federation Services 3.0 (ADFS 3.0) – blog – Alexander Ollischer | Citrix | Microsoft

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s