Skip to main content
Delphix

MSSQL Hook Login Fails When Connecting Over Network Not Allowing AD Authentication (KBA1534)

 

 

KBA

KBA#1534

Issue

SQL Server VDB Provisioning, Refresh, Rewind or Snapshot operations may fail if a Hook Script uses the sqlcmd -E ("trusted connection") parameter and the VDB_INSTANCE_HOST environment variable.

UI1.png

These authentication failures will report the following error:

Error:
"Default PowerShell" Hook script "Configure clone (1)" on "MyVDB", executed as "MyDomain\Delphix" on "myserveralias.mydomain", failed with exit code 1

Error Code:
exception.db.generictarget.user_ps_script.failed
(earlier Delphix Engine releases may report exception.db.generictarget.user_script.failed)

Suggested Action:
Review the output of the script, correct the error(s), and run the operation again.

Command Output:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication..

This is a sample of a hook script that may fail:

$ErrorActionPreference = "Stop"

$VDB_HOST = $env:VDB_INSTANCE_HOST
$VDB_PORT = $env:VDB_INSTANCE_PORT
$VDB_DATABASE_NAME = $env:VDB_DATABASE_NAME

sqlcmd -l 30 -b -S "$VDB_HOST,$VDB_PORT" -d "$VDB_DATABASE_NAME" -W -E -s ";" -i "E:\Path\To\MyHook.sql"
if ($LASTEXITCODE -ne 0) {
    exit 1
}

Troubleshooting

The VDB_INSTANCE_HOST environment variable is populated by the Delphix Engine using the Host Address of the VDB Environment from the Management interface.

In some cases, sqlcmd may not understand that it should attempt to perform Integrated (Windows) Authentication to this address:

  • If the Host Address configured in the Delphix Engine is a DNS alias, and does not match the server's Computer Name in Active Directory
  • If the Host Address is configured as an IP Address which the server cannot resolve to an Active Directory Computer
  • If the server is not connected to Active Directory
  • If the Delphix Engine is configured to connect to the server using a secondary or storage network

Resolution

Instead of using the VDB_INSTANCE_HOST variable, you can use the server's built-in computerName variable so that the script can correctly attempt Integrated (Windows) Authentication:

$ErrorActionPreference = "Stop"

$VDB_HOST = $env:computerName
$VDB_PORT = $env:VDB_INSTANCE_PORT
$VDB_DATABASE_NAME = $env:VDB_DATABASE_NAME

sqlcmd -l 30 -b -S "$VDB_HOST,$VDB_PORT" -d "$VDB_DATABASE_NAME" -W -E -s ";" -i "E:\Path\To\MyHook.sql"
if ($LASTEXITCODE -ne 0) {
    exit 1
}

For information on use of MSSQL Server hooks, see Customizing SQL Server VDB Management with Hook Operations. The Knowledge Base article Executing SQL in a PowerShell Hook Script (KBA1370) also provides examples and error handling suggestions.