SSIS中ODBC连接通过变量设置密码失败求助

Ah, I’ve run into similar quirks with HANA’s ODBC driver and SSIS protection levels before—this is definitely a case where ODBC behaves differently than OLE DB, so your hunch is right on target. Let’s break down some actionable troubleshooting steps to get this sorted:

• Double-check the password parameter name in your connection string expression
  HANA’s 32-bit ODBC driver (HDBODBC32) expects the password parameter to be PWD (case-insensitive, but sticking to uppercase avoids edge cases). Make sure your SSIS expression is correctly appending ;PWD=" + @[User::Password] to the connection string—no typos, missing semicolons, or extra spaces. For example, your full expression should look something like:

  "Driver={HDBODBC32};uid=" + @[User::Username] + ";servernode=#########.######.####.com:12345;PWD=" + @[User::Password]
  

  Sometimes folks accidentally use password instead of PWD, which the HANA driver won’t recognize.

• Verify SSIS variable and protection level settings

  • Ensure your password variable has its Sensitive property set to True—this tells SSIS to handle the value securely even when ProtectionLevel is set to DontSaveSensitive. If this is off, the variable’s value might get stripped when the package runs under the agent.
  • Double-check that the package’s ProtectionLevel is definitely set to DontSaveSensitive (the exact name matters—no typos like DontSaveSensitiveData). Also, if you’re passing the password via agent job parameters, confirm the parameter mapping to your SSIS variable is correct.

• Add encryption parameters to your connection string
  When you used the Encrypt... protection level locally, SSIS was handling encryption of the sensitive connection details behind the scenes. But when switching to DontSaveSensitive and building the string manually, you might be missing required encryption settings that the HANA driver enforces. Try adding these parameters to your connection string:

  ;encrypt=yes;sslValidateCertificate=no
  

  (Use sslValidateCertificate=yes if you have a valid CA-signed certificate for your HANA server.) This ensures the connection uses SSL, which many HANA instances are configured to require by default.

• Test the ODBC connection outside of SSIS
  Rule out SSIS-specific issues by testing the connection directly. Open the 32-bit ODBC Data Source Administrator (since your driver is HDBODBC32) and create a System DSN with your server, username, and password. Test the connection—if it fails here, the problem is with the driver, network, or HANA server configuration, not SSIS. You can also test via PowerShell to mimic the variable approach:

  $connectionString = "Driver={HDBODBC32};uid=YOUR_USERNAME;servernode=YOUR_SERVER:12345;PWD=YOUR_PASSWORD;encrypt=yes"
  $conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
  $conn.Open()
  Write-Host "Connection successful!"
  $conn.Close()
  

  If this works, the issue is definitely in how SSIS is handling the variable or protection level.

• Check the SQL Server Agent runtime environment

  • Make sure the agent job step is configured to use the 32-bit runtime—since you’re using the 32-bit HANA ODBC driver, a 64-bit agent process will try to load the 64-bit driver (which might not be installed) and fail. You can enable this in the job step’s Execution Options tab.
  • Verify the agent service account has network access to the HANA server’s port (12345 in your case). Your local user might have access, but the agent account could be blocked by a firewall or network policy.

• Dig into HANA server logs for detailed errors
  SSIS’s generic ODBC error doesn’t tell you much. Head to your HANA server’s trace logs (usually in /usr/sap/<SID>/HDB<instance>/trace) and look for indexserver_*.trc or nameserver_*.trc files. These will have specific error messages—like invalid credentials, missing SSL, or authentication method mismatches—that point you directly to the root cause.

内容的提问来源于stack exchange，提问作者SimonB