Ssis-927 May 2026

Each data flow engine processes rows in in‑memory buffers.

| Parameter | Effect | Recommended Settings | |-----------|--------|----------------------| | DefaultBufferSize | Max bytes per buffer (default 10 MB). | Set to 10‑20 MB for high‑throughput pipelines; keep under 100 MB (SQL Server limit). | | DefaultBufferMaxRows | Max rows per buffer (default 10 000). | Adjust when row size is large (e.g., 1 KB rows → reduce rows to keep buffer < 10 MB). | | EngineThreads | Parallel execution threads (default = #CPU cores). | For I/O‑bound sources, increase to #CPU * 2. |

Rule of thumb: If you see “Buffer overflow” warnings, reduce DefaultBufferMaxRows or increase DefaultBufferSize.

SSIS‑927 is not an SSIS‑specific error code; it is a SQL Server error number 927 that bubbles up to SSIS when the package attempts to connect to a database for which the current Windows or SQL login lacks the necessary permissions. SSIS-927

Message:
The server principal "<login>" is not able to access the database "<database>" under the current security context.

In SSIS, you typically see this as a Data Flow or Execute SQL Task failure, and the error appears in the Execution Results tab or the job history (if run via SQL Server Agent).

  • Running via SQL Server Agent?

    • Tip: You can force a different context by creating a SQL Server Agent Proxy that uses a credential tied to a domain account with proper DB rights. Each data flow engine processes rows in in‑memory buffers

    Scenario:
    A nightly SSIS package loads daily sales data into DW_Sales. It runs via a SQL Server Agent job under the service account NT SERVICE\SQLSERVERAGENT. After a weekend security hardening, the job starts failing with:

    Error 927: The server principal "NT SERVICE\SQLSERVERAGENT" is not able to access the database "DW_Sales" under the current security context.

    Resolution Steps Taken

    Takeaway: Even built‑in service accounts need explicit permissions when they access user databases. Rule of thumb: If you see “Buffer overflow”

    A Script Component (C#) evaluates business rules expressed in a domain‑specific language (DSL) that compiles to C# expressions at runtime. Example rule:

    RuleID = 101;
Expression = "Quantity > 0 && (Discount <= 0.5 || CustomerType == 'VIP')";
Severity = 'Critical';

    During execution, the script parses the DSL, builds a Lambda Expression, and applies it to each data row. Violations are written to the ErrorQueue with the rule ID, enabling downstream analysts to trace root causes.

    | Command | Purpose | |---------|---------| | SELECT ORIGINAL_LOGIN(), SUSER_SNAME(); | Shows the login under which the current session runs. | | EXEC sp_helpuser; | Lists users and their permissions in the current DB. | | EXEC sp_change_users_login 'Auto_Fix', 'login_name', NULL, 'password'; | Fixes orphaned users (rare, but can cause 927). | | ALTER DATABASE <db> SET SINGLE_USER WITH ROLLBACK IMMEDIATE; | Switches DB to single‑user (useful for troubleshooting). | | ALTER DATABASE <db> SET MULTI_USER; | Returns DB to normal mode. |

    | Pitfall | Why It Happens | Fix | |---------|----------------|-----| | Hard‑coded credentials in connection strings | Deploying to another environment (Dev → Prod) where the login does not exist. | Use SSIS Package Configurations or Project Parameters + SSIS Catalog environments to inject credentials at runtime. | | Running the package as a 32‑bit process when the provider is 64‑bit only | Provider fails to load, sometimes surfaces as 927. | Set Run64BitRuntime = False only when you truly need the 32‑bit provider (e.g., Access, Excel). | | Database in RECOVERY or SUSPECT | SQL Server cannot open the DB, so any login is denied. | Bring the DB online before running the package. | | Missing EXECUTE AS clause in stored procedures that the package calls | The stored procedure runs under the caller’s context, which may lack rights. | Add WITH EXECUTE AS OWNER (or a specific user) to the procedure, or grant the caller rights directly. |

    Skip to content