4 Prepare Phase
4.3 User Identity and Account Provisioning Tasks
4.3.5 Advanced Option: Deploy Federation Services with SQL Server
The AD FS configuration database stores all the configuration data that represents a single instance of AD FS 2.0 (also known as the Federation Service). You can store this configuration data in either a Microsoft SQL Server® database or using the Windows Internal Database (WID).
As indicated in the Capacity Planning section of this document, customers with 15,000-60,000 users or more and multiple federation servers in the farm may want to consider using a SQL Server-based policy store. An AD FS 2.0 federation server farm configured to use WID supports a maximum of five federation servers. If you need more than five federation servers, you need to configure a SQL Server database to store the AD FS 2.0 configuration database.
4.3.5.1 Installation Steps
These steps provide a high-level instruction on how to install AD FS for use with SQL Server.
► To install SQL Server AD FS configuration database
1. Install the full version (not Express version) of SQL Server 2005 or higher in your on-premises environment. Note whether you install using the default instance or with an instance name as this is important later during the configuration process.
2. Install the AD FS Core Services onto a machine designated to run this service. Best practice is to install on a Windows Server 2008 R2/x64 with plenty of memory.
3. After the AD FS Core Services installation stops, do not d to Configuration when asked.
Uncheck the configuration option and close the application.
4. Now navigate to the following location:
c:\program files\Active Directory Federation Services 2.0 5. Run the following command:
FSConfig.exe CreateSQLFarm /ServiceAccount "domain\user"
/ServiceAccountPassword "password" /SQLConnectionString
"database=AdfsConfigurationServer;server=MSSQLSERVER\Instance [as needed];integrated security=SSPI" /port 443 /FederationServiceName
"sts.contoso.com" /AutoCertRolloverEnabled
Notes:
/ServiceAccount: Must be created before this command is run
Server: Should either be the MSSQLSERVER which is the default install for SQL or MSSQLSERVER\Instance IF an instance has been created for use with the AD FS Services.
/FederationServiceName: Should refer to the Common Name of your certificate used for your AD FS Services
4.3.5.2 Configure Communication with Federation Gateway
After the AD FS server has been configured to use SQL, use the following steps to configure the server to communicate with the Microsoft Federation Gateway.
1. Download and install the Microsoft ID components.
2. Run the following PowerShell cmdlets to create your custom federated domain:
$cred=Get-Credential [Enter your Online Admin Account]
Connect-MsolService –Credential $cred
Set-MsolAdfscontext –Computer <AD FS 2.0 primary server internal FQDN>
New-MsolFederatedDomain –DomainName <domain>
4.3.5.2.1 Install Second or future AD FS Core Services with Full SQL
1. Follow the above steps but change the command to Join the SQL Farm instead of creating, as it has already been created.
2. After the initial SQL Database has been created, use the following command on any AD FS Servers wanting to use Full SQL:
FSConfig.exe JoinSQLFarm /ServiceAccount "domain\user"
/ServiceAccountPassword "password" /SQLConnectionString
"database=AdfsConfigurationServer;server=MSSQLSERVER\Instance [as needed];integrated security=SSPI"
Note: For more information, see AD FS 2.0: How to Perform an Unattended Installation of an AD FS 2.0 STS or Proxy.
4.3.5.3 Converting from Windows Internal Database to SQL Database
The Windows Internal Database is a Windows Server feature that is automatically installed on the computer after you complete the AD FS 2.0 Federation Server Configuration Wizard for the first time. Because the wizard does not provide an option to choose SQL Server as the store for the AD FS configuration database, your organization may simply continue to use the wizard defaults to see if they work well for your infrastructure.
However, it is highly possible that in time you will want to scale out your federation server farm to use more than five federation servers by migrating the configuration database to SQL Server.
By migrating to SQL you will obtain scale, high availability and also be able to use SQL’s backup mechanisms.
This topic is provided for just this situation and will walk you through all the steps necessary to migrate your existing AD FS configuration data from your current Windows Internal Database store (in a production environment) to a new SQL Server store.
4.3.5.3.1 AD FS 2.0: Migrate Your AD FS Configuration Database to SQL Server In the steps that follow, use steps 1, 2, 3, and 5 on the primary federation server. Follow steps 1,2, 4, and 5 on each of the secondary federation servers in the farm. These steps include:
1. Backing up the federation server
2. Temporarily disable the computer in the load balancer 3. Performing steps on the primary federation server
4. Performing steps on all of the secondary federation servers 5. Enabling the computer on the load balancer
For more information about the pros and cons of using either Windows Internal Database or SQL Server to store AD FS 2.0 configuration data, see the TechNet article The Role of the AD FS Configuration Database in the AD FS 2.0 Design Guide.
Step 1: Backing up the federation server
Use Windows Server Backup to back up the entire federation server computer including the AD FS configuration database stored in Windows Internal Database. You can also use Windows Server Backup to restore the AD FS configuration database.
More information about how to back up the AD FS configuration database will be out soon.
Once this content is provided we will update this link.
Step 2: Temporarily remove server from load balancer
If your federation server is running in a farm and you have a load balancer, temporarily remove this machine from the load balancer configuration.
Step 3: Performing steps on the primary federation server
1. On the primary federation server in the farm, download the SQL Server 2008
Management Studio Express software and install it on the primary federation server. The software is available from the Microsoft Download Center.
Note: This software is necessary in order to install and register the SQLCMD command-line tool, which is used in an upcoming step.
2. Stop the AD FS 2.0 Windows Service on the primary federation server.
3. Open an elevated command prompt, type the following command-line to stop the AD FS 2.0 Windows Service and then press ENTER.
net stop adfssrv
4. Connect to the Windows Internal Database that currently stores the AD FS configuration database and then detach both the AD FS configuration and artifact databases. In the command prompt window, type the following SQLCMD command-line syntaxes in order, and then press ENTER after each one.
sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query use master
go
sp_detach_db 'adfsconfiguration' go
sp_detach_db 'adfsartifactstore' go
5. Connect to SQL server and attach the configuration and artifact database from the primary federation server. In the command prompt window, type the following SQLCMD command-line syntaxes in order, and then press ENTER after each one. In
SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.
sqlcmd -S <SQLServer\SQLInstance>
use master go
sp_attach_db 'adfsconfiguration',
'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsconfiguration.mdf', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsconfiguration_log.ldf ' go sp_attach_db 'adfsartifactstore',
'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsartifactstore.mdf', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsartifactstore_log.ldf '
go
alter database AdfsConfiguration set enable_broker with rollback immediate
go
6. Change the configuration database connection string to point to the new SQL
Server-the following command-line syntaxes in order, and Server-then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.
$temp= GEt-WmiObject -namespace root/AD FS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=<SQLServer\SQLInstance>; initial
catalog=adfsconfiguration;integrated security=true”
$temp.put()
7. Open an elevated command-line prompt, type the following command-line syntax to start the AD FS 2.0 Windows Service, and then press ENTER.
Net start adfssrv
8. Change the artifact connection string to point to the new SQL Server-based artifact data location. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are
migrating the artifact data to. For example, contososrv01\adfs-artifact.
Add-pssnapin microsoft.adfs.powershell
Set-adfsproperties –artifactdbconnection “data source=<SQLServer\SQLInstance>; initial
catalog=adfsartifactstore;integrated security=true”
9. Stop and restart the AD FS 2.0 Windows Service to refresh the new settings. Open a regular command-line prompt, type the following command-line syntaxes to stop and start the AD FS 2.0 Windows Service, and then press ENTER after each one.
Net stop adfssrv Net start adfssrv
Step 4: Performing steps on the secondary federation server
Make sure the primary federation server has been added back to the load balancer before proceeding with this section.
1. Make sure the secondary federation server has been temporarily removed from the load balancer before proceeding.
2. On a secondary federation server in the farm, open an elevated command prompt, type the following command-line to stop the AD FS 2.0 Windows Service, and then press ENTER.
net stop adfssrv
3. Change the configuration database connection string to point to the new SQL Server-based AD FS configuration database. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example,
contososrv01\adfs.
$temp= GEt-WmiObject -namespace root/AD FS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=<SQLServer\SQLInstance>; initial
catalog=adfsconfiguration;integrated security=true”
$temp.put()
4. Open a regular command-line prompt, type the following command-line syntax to start the AD FS 2.0 Windows Service, and then press ENTER:
Net start adfssrv
5. Change the artifact connection string to point to the new SQL Server-based artifact data location. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are
migrating the artifact data to. For example, contososrv01\adfs-artifact.
Add-pssnapin microsoft.adfs.powershell
Set-adfsproperties –artifactdbconnection “data source=<SQLServer\SQLInstance>; initial
catalog=adfsartifactstore;integrated security=true”
6. Stop and restart the AD FS 2.0 Windows Service to refresh the new settings. Open a regular command-line prompt, type the following command-line syntaxes to stop and start the AD FS 2.0 Windows Service, and then press ENTER after each one:
Net stop adfssrv Net start adfssrv
7. Verify that the service starts up successfully.
8. Repeat these steps for every federation server in this Windows Internal Database-based farm.
Step 5: Enabling this computer on the load balancer
Enable the computer in the load balancer so that requests are sent to it.