Troubleshooting SQL Server Linked Server Error: ‘SELECT Permission Was Denied on Object'

ODBC PowerPack
, , ,
1

When working with the ZappySys Salesforce Driver (or any other ZappySys driver that supports bulk load from Microsoft SQL Server to Salesforce or other destinations), you may occasionally run into issues when running queries from SQL Server Linked Servers.

A common scenario is performing bulk inserts into Salesforce objects (such as CampaignMember) using a SQL Server table as the data source. This is often done via an OPENQUERY call against the linked server.

Example Scenario

Query via Linked Server

SELECT * FROM OPENQUERY(LS_ZAPPY_TESTSALEFORCE,
'INSERT INTO CampaignMember
SOURCE(''MSSQL''
      ,''Data Source=DESKTOP-ABC\SQLDEV2022;Initial Catalog=MyDatabase;Integrated Security=true''
      ,''SELECT [CampaignId],[ContactId],[Status] FROM [tblCampMember]'')
WITH(
       Output=1 
      ,EnableBulkMode=1
)')

Error Message Received

OLE DB provider "MSOLEDBSQL" for linked server "LS_ZAPPY_TESTSALEFORCE" 
returned message "Deferred prepare could not be completed.".

Msg 123456, Level 16, State 2, Line 10
The SELECT permission was denied on the object 'tblCampMember', 
database 'MyDatabase', schema 'dbo'.

Cause

  1. Windows Authentication in the Connection String
    • The keyword Integrated Security=true in your connection string instructs SQL Server to use Windows login instead of a SQL login.
    • However, because the query is executed through the linked server, it does not use your Windows login. It uses whatever account the linked server is configured for — in most cases NT AUTHORITY\SYSTEM.
  2. If the credentials used by the linked server do not have access to the underlying database or table (in this example, [tblCampMember]), the driver fails to fetch data, resulting in the “Deferred prepare could not be completed” error.
  3. If SQL Server cannot authenticate properly, it may also default to the wrong database context (often MyDatabase), which further contributes to permission errors.

Same Query Works from Driver UI/Directly

If you execute the same query directly from the ZappySys Driver UI (or SSIS/Power BI using the driver), it may succeed, because:

  • You are using your own Windows or SQL credentials (with full access to the table).
  • The driver connects directly without involving SQL Server’s linked server security context.

How to Fix It

1. Verify Permissions

Ensure the credentials used by the SQL Server Linked Server have the correct SELECT permission on the source table:

GRANT SELECT ON [MyDatabase].[dbo].[tblCampMember] TO [NT AUTHORITY\SYSTEM]

Also confirm the database name in the connection string is correct:

Data Source=DESKTOP-ABC\SQLDEV2022;Initial Catalog=MyDatabase;Integrated Security=true

2. Run the Data Gateway Service Under a Dedicated Account

By default, the ZappySys Data Gateway runs under the Local System account. This often causes issues because Local System doesn’t have the same network permissions as your logged-in user.

Best Practice:

  • Create a dedicated service account (e.g., svc_zappygw).
  • Assign it as the Log On As user for the Data Gateway Windows service.
  • Avoid using employee accounts — if the employee leaves and the account is disabled, your Gateway will stop working.
    Running service under a different account
    Running service under a different account768×624 48.7 KB

3. Test Connection Outside SQL Server

Run the same INSERT INTO … SOURCE('MSSQL', …) statement directly from the ZappySys driver UI or a standalone tool to confirm the issue is permission-related, not driver-related.

4. Specify Fully Qualified Table Names

Always fully qualify your table references:

SELECT [CampaignId],[ContactId],[Status] FROM [MyDatabase].[dbo].[MyDatabase]

This ensures the query is executed against the intended database, not master.

Summary

When performing bulk inserts into Salesforce (or similar targets) via ZappySys Salesforce Driver from SQL Server Linked Servers, you might encounter the “Deferred prepare could not be completed” error.
This is typically a SQL Server security or permission issue, not a driver bug.
By verifying permissions, using explicit security mappings, and fully qualifying your source tables, you can avoid or resolve this issue.

Conclusion

The “Deferred prepare could not be completed” error is usually a security context problem, not a driver issue.
Because Integrated Security=true uses Windows authentication, the linked server runs the query under NT AUTHORITY\SYSTEM (or another mapped account), not your own credentials.
If that account lacks permissions on the source database or table, the insert fails. Configuring the linked server to use a valid SQL or Windows login with proper access—or switching to SQL authentication in the connection string—resolves the issue.

References