Stéphane Thirion
  • Home
  • Consulting – Raidho
  • homelab
3K
0
0
0
Stéphane Thirion
Stéphane Thirion
  • Home
  • Consulting – Raidho
  • homelab
  • Windows 2016

Migrate ADFS configuration Database from WID to MS-SQL

  • January 5, 2021
  • Rodolphe Herpeux
Total
1
Shares
0
0
1
0
0
0
0

In this post, we describe how to migrate the ADFS WID database to MS SQL 2016 server. In my lab I have two servers install in Microsoft Windows Server 2016 for the OS and WID Database with a primary and a secondary server. The MS SQL server is install in version 2016 with the last SP. The instance name DB is “ADFS” and this instance port is “11433”.

Logon on the primary ADFS Server

To verify if you are on the primary ADFS server, open a Powershell console and type this command:
get-adfssyncproperties

In first time, stop the ADFS service

Open the command prompt as Administrator and type:
Net stop adfssrv
Or in the Powershell command prompt as Administrator and type:
Stop-service adfssrv

Locate the ADFS database files

Open Windows Explorer and navigate to “C:WindowsWIDDATA”.
Note: This is the default location of WID Database. We will see the ADFSArtifactStore.mdf, ADFSArtifactStore_log.ldf, ADFSConfigurationV3.mdf and ADFSConfigurationV3_log.ldf files which are the AD FS Databases.

Detach ADFS database

Save below as PowerShell script file or paste below in PowerShell ISE and run it to detach ADFS Database from WID.
Detach Script:
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = 'Server=np:\.pipeMICROSOFT##WIDtsqlquery;Database=master;Trusted_Connection=True;'
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = "EXEC sp_detach_db @dbname = N'AdfsArtifactStore';"
$result = $command.ExecuteReader()
$connection.close()
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = "EXEC sp_detach_db @dbname = N'AdfsConfigurationV3';"
$result = $command.ExecuteReader()
$connection.close()

/! report correctly the good name of the two DB in the script.

Copy the database files from ADFS server to SQL server

Copy the following files below

Paste these files to SQL server

Open another Explorer and navigate to “C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA” or the path you declared when installing the MS SQL instance.
Paste the files you copied from ADFS Server to this location.

Attach the Database to the SQL Server

Connect to SQL Server in SQL Management Studio

Click the Windows button then go to Microsoft SQL Server and SQL Server Management Studio
At the Connect to Server prompt, confirm that Server Name is SQL Server and click Connect

Create a new query to attach database

Click the New Query button to open the query panel
Copy the following code and paste it to the SQLQuery panel:
USE [master] GO
CREATE DATABASE [AdfsConfigurationV3] ON
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAdfsConfigurationV3.mdf' ),
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAdfsConfigurationV3_log.ldf' )
FOR ATTACH
GO
USE [master] GO
CREATE DATABASE [AdfsArtifactStore] ON
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAdfsArtifactStore.mdf' ),
( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDATAAdfsArtifactStore_log.ldf' )
FOR ATTACH
GO
ALTER DATABASE AdfsConfiguration set enable_broker with rollback immediate
GO

Click Execute to run the query and attach the database.
Verify if the query was successfully run.

Check the databases are attached

On the Object Explorer, expand Databases and check that the New Databases are attached.

Create a new login to provide access to ADFS group managed account

In object Explorer, expand Security, right-click at Logins and select New Login…
Note: Now verify the security settings of the SQL Server to make sure that AD FS Service Account has permissions to access the Database.
Choose the AD FS service account
Select Server Roles
Select role “Public”
Click OK.
Open the login’s properties

Check the user mapping

At User Mapping, select the AD FS Configuration Databases (AdfsArtifactStore and AdfsConfigurationV3)
Make sure that options “Public” and “Db_genevaservice” are selected. (If not, select it and click OK)
Click Ok.

Change the Connection String at ADFS to connect to SQL server

Paste the following powershell script on ADFS server powershelll ISE (Modify data source server to SQL server before running it).
Paste the following code into the new script area
$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=sqladfs.ad.localADFS,11433; initial catalog=adfsconfigurationV3;integrated security=true”
$temp.put()
Get-WmiObject -namespace root/ADFS -class SecurityTokenService

Start the AD FS service

Start AD FS Service with the command:

Start-Service adfssrv

Connect the AD FS Server to the Artifact Database

Open PowerShell as an administrator.
Execute the following command:
Set-adfsproperties –artifactdbconnection “data source= sqladfs.ad.localADFS,11433; initial catalog=adfsartifactstore;integrated security=true”

Restart the AD FS service

Restart the AD FS Service by executing
Restart-Service adfssrv

Check the event log

On ADFS server, open Event Viewer.
Open ‘Application And Services Logs’ and navigate to the AD FS Admin event log.
Look for Event ID 100 showing the AD FS service started successfully.

Check ADFS SQL connection

Run below command to check database surce on ADFS:

Get-ADFSproperties | fl artifactdbconnection

Migrate another server in the farm

Stop ADFS service

Stop AD FS Service with the command:

stop-servervice adfssrv

Change the Connection String at ADFS to connect to SQL server

Paste the following PowerShell script on ADFS server powershelll ISE (Modify data source server to SQL server before running it).
Paste the following code into the new script area
$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=sqladfs.ad.localADFS, 11433; initial catalog=adfsconfiguration;integrated security=true”
$temp.put()
Get-WmiObject -namespace root/ADFS -class SecurityTokenService

Start the AD FS service

Start AD FS Service with the command:
Start-Service adfssrv

Connect the AD FS Server to the Artifact Database

Open PowerShell as an administrator.
Execute the following command:
Set-adfsproperties –artifactdbconnection “data source=sqladfs.ad.localADFS,11433; initial catalog=adfsartifactstore;integrated security=true”

Restart the AD FS service

Restart the AD FS Service by executing

Restart-Service adfssrv
Make sure your migration is done correctly

Watch the events logs

Make sure the ID 100 event is present after a server or service restart.

Check DB connections

Configuration DB

Get-WmiObject -namespace root/ADFS -class SecurityTokenService

__GENUS                                   : 2
__CLASS                                   : SecurityTokenService
__SUPERCLASS                              :
__DYNASTY                                 : SecurityTokenService
__RELPATH                                 : SecurityTokenService=@
__PROPERTY_COUNT                          : 3
__DERIVATION                              : {}
__SERVER                                  : ADFS02
__NAMESPACE                               : rootADFS
__PATH                                    : \ADFS02rootADFS:SecurityTokenService
=@
ConfigurationChannelMaxMessageSizeInBytes : 20971520
ConfigurationDatabaseConnectionString     : data source=sqladfs.ad.localADFS,11433;
initial
catalog=adfsconfiguration;integrated
security=true
ConfigurationServiceAddress               : net.tcp://localhost:1500/policy
PSComputerName                            : ADFS02

Artifact DB

Get-ADFSproperties | fl artifactdbconnection
ArtifactDbConnection : data source= sqladfs.ad.localADFS,11433; initial catalog=adfsartifactstore;integrated security=true

Uninstall WID DB sources

When all operations are done, you need to uninstall all composents of WID db on the farm servers ADFS.

Total
1
Shares
Tweet 0
Share 0
Share 1
Share 0
Share 0
Share 0
Share 0
Related Topics
  • ADFS
  • identities
  • IDP
  • Migration
  • sql server
Rodolphe Herpeux

Previous Article
  • ArchY.net Site
  • Azure
  • Citrix
  • Cloud
  • Experience
  • Microsoft
  • News
  • Office365
  • Raidho
  • VMware

Mettre en place une solution de travail à distance (RemoteOffice / Remoteworking) 2/2

  • March 24, 2020
  • Stephane Thirion
View Post
Next Article
  • Windows 2016
  • Windows 2019

Migration farm ADFS operating system Windows server 2016 to Windows server 2019

  • January 6, 2021
  • Rodolphe Herpeux
View Post
You May Also Like
View Post
  • RDS
  • Security
  • Windows 2016

RDS access to applications with second authentication factor by smartcard

  • Rodolphe Herpeux
  • January 7, 2021
View Post
  • Windows 2016
  • Windows 2019

Migration farm ADFS operating system Windows server 2016 to Windows server 2019

  • Rodolphe Herpeux
  • January 6, 2021
View Post
  • Citrix
  • Citrix Virtual Apps and Desktops
  • Microsoft
  • PowerShell
  • Scripting
  • Windows 2016
  • Windows 2019
  • XenApp
  • XenDesktop

Enable SSL on Citrix Virtual Apps and Desktops 1912(+) XML Service

  • Stephane Thirion
  • February 13, 2020
View Post
  • Experience
  • Microsoft
  • PowerShell
  • Scripting
  • Windows 2016
  • XenApp

XenApp Windows 2016 build report

  • Stephane Thirion
  • May 25, 2018
View Post
  • Microsoft
  • PowerShell
  • Uncategorized
  • Windows 2016

Hyper-V 2016 – Add-VMTPM issue

  • Rodolphe Herpeux
  • October 28, 2017
View Post
  • Microsoft
  • PowerShell
  • Windows 2012R2
  • Windows 2016

Active Directory Certificate Services [Part2]

  • Rodolphe Herpeux
  • October 5, 2017
View Post
  • Experience
  • Microsoft
  • News
  • Windows 2016

Microsoft Honolulu project

  • Rodolphe Herpeux
  • September 28, 2017
View Post
  • Microsoft
  • PowerShell
  • Uncategorized
  • Windows 2012R2
  • Windows 2016

DHCP – Activate Filter “Allow” & import MAC address from SCCM by WMI request

  • Rodolphe Herpeux
  • August 29, 2017
vmware
Coinbase – Affiliated link
Blog Stats
  • 1,237,011 hits
Categories
  • Amazon (1)
  • Apple (20)
    • iOS (5)
    • Mac OSx (11)
  • ArchY.net Site (30)
  • Azure (8)
  • Certifications (3)
  • Citrix (207)
    • ADC (1)
    • Citrix Virtual Apps and Desktops (3)
    • NetScaler (12)
    • Password Manager (3)
    • Personal vDisk (5)
    • Power and Capacity Management (3)
    • Provisioning Services (22)
    • Receiver (29)
    • ShareFile (8)
    • Single Sign On (3)
    • SmartAuditor (2)
    • Storefront (12)
    • Synergy (25)
    • User Profile Management (2)
    • VDI (7)
    • WebInterface (21)
    • XenApp (84)
    • XenApp Plugin (3)
    • XenClient (10)
    • XenDesktop (55)
    • XenServer (42)
  • Cloud (12)
  • Crystal Ball (2)
  • CTP (13)
  • Docker (2)
  • Events (35)
    • E2E – PubForum (9)
    • Geek Speak (3)
  • Experience (53)
  • Kubernetes (2)
  • Licensing (3)
  • Linux (12)
  • Microsoft (145)
    • Azure (8)
    • Office365 (4)
    • PowerShell (18)
    • RDS (5)
    • Windows 10 (6)
    • Windows 2003 (21)
    • Windows 2008 (20)
    • Windows 2008 R2 (54)
    • Windows 2012 (13)
    • Windows 2012R2 (13)
    • Windows 2016 (18)
    • Windows 2019 (4)
    • Windows 2022 (1)
    • Windows 7 (27)
    • Windows 8 (19)
    • Windows Virtual Desktop (1)
    • Windows XP (11)
  • News (5)
  • Raidho (2)
  • Raspberry (3)
  • Scripting (13)
  • Security (4)
  • Slide Deck (1)
  • Thin Clients (3)
  • Twitter (1)
  • Ubiquiti (1)
  • Uncategorized (12)
  • VMware (27)
    • VMWare WorkStation (2)
    • vSphere (15)
Stéphane Thirion
Don't Follow the Trend

Input your search keywords and press Enter.

 

Loading Comments...