Sample SQL Script to Update ADRMS Configuration Database during Parallels Upgrade

UPDATE [dbo].[DRMS_ClusterPolicies]

SET PolicyData = '' --(your new string with updated information goes between the ' ' )
WHERE PolicyName='CertificationUserKeyStorageConnectionString'
 
UPDATE [dbo].[DRMS_ClusterPolicies]
SET PolicyData = '' --(your new string with updated information goes between the ' ' )
WHERE PolicyName='DirectoryServicesCacheDatabase'
 
UPDATE [dbo].[DRMS_ClusterPolicies]
SET PolicyData = '' --(your new string with updated information goes between the ' ' )
WHERE PolicyName='LoggingDatabaseServer'

Update AD RMS Config Database using SQL Server Management Studio during Parallels Upgrade

Description:

During AD RMS Parallels Upgrade, you need to modify the restored database to point to the new database servers.

Resolution:

1. Log on to the AD RMS configuration database server as local Administrator or another user account that is a member of the local Administrators group.
2. Click Start, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Management Studio.
3. On the Connect to Server page, ensure that the new database server name is listed in the Server name box, and then click Connect.
4. Expand Databases, expand DRMS_Config_<RMS cluster name>_<Port>, and then expand Tables.
5. Right-click DRMS_ClusterPolicies, and then click Open Table.
6. In the results pane, change the value in the PolicyData column of the LoggingDatabaseServer row to the new RMS database server name.
7. Change the value in the PolicyData column of the CertificationUserKeyStorageConnectionString row to reflect the new database server. The value should be data source=<new database server name>;integrated where <new database server name> is the name of the new database server.
8. Repeat steps 6–7 for the value in the PolicyData column of the DirectoryServicesCacheDatabase row.
9. Close Microsoft SQL Server Management Studio.

How to set mutisubnetfailover option on ADFS to SQL Database connection string

Description:

You want to use SQL Always On capability for your application. 

You need to change the Database Connection string.

Resolution:

Please run the following at PowerShell on all ADFS Server (one by one):

$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=<you sql instance>;multisubnetfailover=true;initial catalog=adfsconfiguration;integrated security=true”
$temp.put()

Set-AdfsProperties –artifactdbconnection ”Data source=<you sql instance>;multisubnetfailover=true;Initial Catalog=AdfsArtifactStore;Integrated Security=True”

Exception Error when Running PowerShell command to Update the ADFS SQL Connection String.

Description:

As per article from https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/design/federation-server-farm-using-sql-server, we need to run the following command to update the SQL connection string for the AD FS configuration database:

PS:\>$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService

PS:\>$temp.ConfigurationdatabaseConnectionstring="datasource=<SQLCluster\SQLInstance>;initial catalog=adfsconfiguration;integrated security=true"

PS:\>$temp.put()

The update is necessary to support SQL Always On feature.

However, there's an "exception error" when you run the above script in PowerShell.

Resolution:

For modifying the connection string on the additional ADFS server in the Farm, you need to stop the ADFS Service first. After that run the above script and start the ADFS Service again.

Error when adding a new Windows Server 2016 to ADFS Server Farm with SQL Database

Description:
You have successfully configured the first ADFS Server with SQL Database in a ADFS farm. Now you want to add another node to the ADFS Farm. 

However when you run the ADFS Configuration wizard, you encountered an error and the process stops.

At the prerequisite checks, we can see several errors as per below:

• "An error occurred during an attempt to connect to the AD FS configuration database. Error: Login failed for user 'Domain\The account I am logged into the server with'.. Confirm that the database hostname and instance name are correct and that the specified service account has logon access to the database."

• "Cannot open database "AdfsConfigurationV0" requested by the login. The login failed.

• Login failed for user 'domain\managed service account$'."

Resolution:
Make sure the account that you are using have the Owner Access to the ADFS database. 
The permissions can be removed after adding the new nodes.

Cannot Start ADFS Service after changing the Database Connection string to support SQL Always On

Description:
You have configured the first ADFS 2016 Server with SQL as the Database. Later on the day, the SQL Admin has also set the ADFS Database to have Always On capability.

You've follow the syntax from https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/design/federation-server-farm-using-sql-server to change the SQL Connection string on the first ADFS server to support SQL Always On feature.

However when you try to restart the ADFS service, it is always failed.

Example Script:

PS:\>$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService
PS:\>$temp.ConfigurationdatabaseConnectionstring="data source=<SQLCluster\SQLInstance>;initial catalog=adfsconfiguration;integrated security=true"
PS:\>$temp.put()

Resolution:

You need to modify the above example script. Make sure the "data source" are correct, and also the "initial catalog" value are the same as the actual database name in SQL. For example you may need to write "adfsconfgurationv3" instead of just "adfsconfiguration" on the above script.

Error connecting to SQL Server after changing Service Account to normal domain users

Description:

As security best practice, we should not run SQL Server Services with domain admin credential. However after changing it to the normal domain user credential, you encounter connection error message when trying to connect from SQL Server Management Studio.

The error may say something like "The target principal name is incorrect.  Cannot generate SSPI context."

Resolution:

We need to provide the appropriate permission for the domain user credential to modify ServicePrincipalName attribute in Active Directory.

• Run Adsiedit.msc
• In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties.
• In the CN= AccountName Properties dialog box, click the Security tab.
• On the Security tab, click Advanced.
• In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row
• Click Edit, Open Permission Entry dialog box.
• Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:
  • Read servicePrincipalName
  • Write servicePrincipalName
• Click OK to apply all changes and exit the ADSI Edit snap-in
• Restart the SQL Service(s) that use the account in question.

Error Connecting to SQL Server Instances after enabling Windows Firewall

Description:

For security reason, you need to enable the Windows Firewall on your SQL server machine.

However, after you enable them, user cannot connect to one of your SQL Instances. You already create Inbound TCP rule to allow port 1433 and another TCP port where the instance listened, but users still cannot connect. They can connect if the specified the port number for that instance was directly written on the connection page.

Resolution:

Make sure you also create inbound rule for UDP port 1434. The SQL Server browser service runs on UDP port 1434 and listens for incoming connections to a named instance.