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"
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.
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.
For information on use of MSSQL Server hooks, see the document Customizing SQL Server VDB Management with Hook Operations.