Skip to main content
Delphix

MSSQL Hook login fails when connecting over network not allowing AD authentication

Issue

Hook scripts executing "sqlcmd -E ..." (using the trusted connection parameter) to login to a virtual database (VDB) may fail to authenticate when using the VDB_INSTANCE_HOST environment variable with the following error:

Error 
Shell script executed as "WinDomain\delphix_env_user" on "env_hostname.com" failed with exit code 1: # Write a PowerShell script here.& "C:\Delphix\scripts\myhook.ps1"

Error Code 
exception.db.generictarget.user_script.failed

Suggested Action 
Review the output of the script, correct the error(s), and run the operation again. To access the VDB you may reenable it. See the Delphix documentation for information on enabling and disabling VDBs.

Command Output 
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed. The login is from an untrusted domain and cannot be used with Windows authentication..

This is a sample of the hook, setting variables and invoking sqlcmd with the -E option:

$VDB_HOST = $env:VDB_INSTANCE_HOST
$VDB_PORT = $env:VDB_INSTANCE_PORT
$VDB_INSTANCE = $env:VDB_INSTANCE_NAME
$VDB_DATABASE_NAME = $env:VDB_DATABASE_NAME
$CONNECT_STRING = "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT"
$script:ErrorActionPreference = "Continue"

sqlcmd -l 30 -b -S "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT" -d "$VDB_DATABASE_NAME" -W -E -s ";" -i "E:\Delphix\scripts\myhook.sql"

Troubleshooting

The VDB_INSTANCE_HOST environment variable is populated from the Delphix environment.  If the account being used is a local user account on the client machine, this error may occur because:

  • The mssql instance host and the Domain Controller don't recognize the local user account on a different machine.
  • Authentication conflict occurs between the local user executing the hook script and the hostname derived from the VDB_INSTANCE_HOST variable.

Resolution

Instead of using the VDB_INSTANCE_HOST variable, set the MSSQL instance host to the local computer name of the target from the context of the powershell script, to bypass the AD authentication:

<...>
$HOSTNAME = $env:computerName
<...>
sqlcmd -l 30 -b -S "$HOSTNAME\$VDB_INSTANCE,$VDB_PORT" -d "$VDB_DATABASE_NAME" -W -E -s ";" -i "C:\Delphix\scripts\myhook.sql"

The sqlcmd connection will succeed by reaching the MSSQL Server instance using the local computer name derived from the powershell environment variable.

Additional Information

For information on use of MSSQL Server hooks, see the document Customizing SQL Server VDB Management with Hook Operations.