Adding a Windows Environment Fails with "Logon failed for login 'username' due to trigger execution" (KBA5448)

Issue

Adding a Windows Environment to the Delphix Engine may fail with the following error:

Warning

Failed to get the list of databases for instance "MSSQLSERVER" on host "HOSTNAME".

Error: "C:\Program Files\Delphix\DelphixConnector\Delphix_COMMON_4a8eb2a71989_host\client\bootstrap\dlpxrunas.exe" powershell -NonInteractive -NoProfile -InputFormat none -ExecutionPolicy RemoteSigned -Command & 'C:\Program Files\Delphix\DelphixConnector\discovery\564d3619-51b4-a14e-84f8-4a8eb2a71989-WINDOWS_HOST_ENVIRONMENT-1\FetchDatabasesMetadata2012.wrap.ps1'  | Out-Host

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Logon failed for login 'USERNAME' due to trigger execution.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.4.0, 6.0.4.1, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
4.3	4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0
4.2	4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1
4.1	4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Resolution

This error indicates that a trigger on the SQL Server instance is preventing normal login from the Delphix Engine to the SQL Server. For more information on logon triggers, see Microsoft's document logon triggers.

To validate whether any logon triggers have been set for the affected SQL Server instance, you can use the following query:

SELECT * FROM sys.server_triggers;

Such triggers will also be visible from SQL Server Management Studio, from which they can be disabled or dropped:

Note:

For SQL Server Availability Groups, each instance participating in the Availability Group needs to be checked separately.

Troubleshooting

Run the following on all nodes and instances to check if triggers are enabled

SELECT * FROM sys.server_triggers;