Search Posts

ADFS EventID 28005 MSSQL$MICROSOFT##WID

Error:

An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.

Solution:

This only works with Windows server 2012R2. If you are using 2016 then use SQL server manager studio

Run the following powershell command on the primary ADFS server

Open an administrative powershell

Change the service account

#Service Account ADFS
$_account = “domain\adfsserviceaccount”

#Connect to the local WID, this does not require the SQL PowerShell module
$_db_connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$_db_connection.ConnectionString =
“Server=\\.\pipe\MICROSOFT##WID\tsql\query;Database=AdfsConfiguration;Integrated Security=True;”
$_db_connection.Open()
#Create a command and run a T-SQL query
$_db_command = $_db_connection.CreateCommand()
$_upade = @”
select s.name
from sys.schemas s
where s.principal_id = user_id(‘$_account’)
alter authorization on schema::identityServerPolicy to dbo
use adfsconfiguration exec sp_dropuser ‘$_account’
use adfsconfiguration exec sp_changedbowner ‘$_account’
use adfsartifactstore exec sp_dropuser ‘$_account’
use adfsartifactstore exec sp_changedbowner ‘$_account’
“@
$_db_command.CommandText = $_upade
#Load the results in a table
$_db_results = $_db_command.ExecuteReader()
$_data_table = New-Object -TypeName System.Data.DataTable
$_data_table.Load($_db_results)
$_data_table
$_data_value = $_data_table.Value
$_data_table.Dispose()
$_data_value
#Close the connection with the local DB
$_db_connection.Close()

479 Total Views 11 Views Today

Leave a Comment

Leave a Reply