SPO2SQL is a .NET 8 console service that replicates SharePoint Online list data into SQL Server tables. It is built for unattended, scheduled execution: configuration is validated at startup, I/O is asynchronous and cancellation-aware, and failures are reflected in the process exit code.
- Full and incremental sync — a persisted UTC watermark drives
Modified >= lastSyncqueries on subsequent runs. - Safe schema evolution — destination tables are created on first run and extended additively when new source columns appear; no destructive DDL.
- Explicit mappings — per-list and per-column rules for inclusion, exclusion, destination names and SQL types, sourced from a legacy XML file.
- Resilience — transient SharePoint/network failures are retried with exponential backoff and jitter; a cancelled run rolls back cleanly.
- Transactional bulk writes — SharePoint data is read and transformed before a SQL transaction is opened; rows are then loaded with
SqlBulkCopyin configurable batches, so no locks are held during network calls and the number of SQL round trips is constant. - Bounded parallelism — independent lists are synchronized concurrently with
Application:MaxParallelLists(default 4); failures in one list do not stop the others. - App-only authentication — certificate-based client credentials work under MFA and Conditional Access; username/password remains a legacy fallback.
- Startup health checks — authenticated SharePoint connectivity and a real SQL connection are verified before any sync starts.
- Observability — structured logging, OpenTelemetry metrics (
spo2sql.lists.synced,spo2sql.items.*,spo2sql.sync.duration) and spans for each list sync, SharePoint read and SQL bulk copy.
flowchart LR
A[Host / Program.cs] --> B[Options validation<br/>IOptions<T>]
B --> C[SyncSettings<br/>immutable snapshot]
C --> D[Application<br/>BackgroundService]
D --> E[HealthChecker]
D --> F[RefreshSQLLists]
F --> G[SPOUser<br/>MSAL auth]
F --> H[SPOList<br/>paged CSOM reads]
F --> I[SQLInteraction<br/>DDL + transactional load]
H --> I
I --> J[(SQL Server)]
The console host owns configuration, dependency injection, logging and exit-status policy. SPO2SQL.Core owns all SharePoint, SQL, credential and mapping logic and receives a single SyncSettings record — it never reads global configuration itself.
src/
SPO2SQL.Console/ Host, options, startup, health checks, CLI setup
SPO2SQL.Core/
Configuration/ SyncSettings passed through the sync pipeline
Security/ Credential provider chain (env, DPAPI, configured values)
SharePoint/ Auth, paged list access, retry, CAML, health checks, DQ
SqlServer/ Table management and transactional data transfer
XmlConfig/ Legacy list/column mapping reader
tests/
SPO2SQL.Core.Tests/ Core unit tests
SPO2SQL.Console.Tests/ Options, validation and exit-code tests
docs/ Configuration, security and modernization notes
SPOtoSQL-Snapshots/ Historical .NET Framework source (reference only)
| Concern | Library |
|---|---|
| SharePoint access | Microsoft.SharePointOnline.CSOM |
| Authentication | Microsoft.Identity.Client (MSAL) |
| SQL access | Microsoft.Data.SqlClient |
| Hosting / options / logging | Microsoft.Extensions.* 9.0 (targets net8.0) |
| Windows credential store | System.Security.Cryptography.ProtectedData |
| Testing | xUnit, FluentAssertions, NSubstitute, coverlet |
- .NET SDK 8.0 (the repository pins the 8.0 SDK band via
global.json) - Network access to the target SharePoint Online site
- SQL Server access with
CREATE TABLE/ALTERrights on the destination database - An app registration with SharePoint application permissions (recommended), or a legacy user account
dotnet restore SPO2SQL.sln
dotnet build SPO2SQL.sln --no-restore -warnaserror
dotnet test SPO2SQL.sln --no-build --no-restore
dotnet format SPO2SQL.sln --verify-no-changes --no-restoreWarnings are treated as errors and nullable reference types are enforced without blanket suppressions.
dotnet run --project src/SPO2SQL.Console/SPO2SQL.Console.csprojApplication:DailyMode selects the mode: true for incremental sync, false for a full current-time transfer. The process returns 0 on success and 1 on validation, health-check or synchronization failure.
Configuration is resolved in this order (later sources win):
appsettings.jsonappsettings.{Environment}.json- .NET user secrets
- Environment variables
- Environment variables prefixed with
SPO2SQL_ - Command-line arguments
Example:
{
"Application": {
"Name": "SPO2SQL",
"Version": "2.0.0",
"LogLevel": "Information",
"EnableHealthChecks": true,
"EnableMetrics": true,
"DailyMode": true,
"MaxParallelLists": 4,
"LegacyConfigPath": "XmlConfig/UserConfig.xml"
},
"SharePoint": {
"SiteUrl": "https://contoso.sharepoint.com/sites/example",
"Username": "",
"Password": "",
"TimeoutSeconds": 120,
"MaxRetries": 3,
"InitialRetryDelayMs": 1000,
"TenantId": "00000000-0000-0000-0000-000000000000",
"ClientId": "00000000-0000-0000-0000-000000000000",
"CertificatePath": "/secure/spo2sql.pfx",
"CertificatePassword": ""
},
"Sql": {
"ConnectionString": "Server=sql01;Database=SPO2SQL;Encrypt=true;TrustServerCertificate=false;Integrated Security=true;",
"CommandTimeoutSeconds": 300,
"BatchSize": 80,
"EnforceEncryption": true
}
}Sql:BatchSize controls both the SharePoint page size and the number of rows sent per SQL bulk-copy batch. Application:EnableMetrics publishes the SPO2SQL OpenTelemetry meter and activity source; without OTEL_EXPORTER_OTLP_ENDPOINT both are written to the console.
Supply secrets outside source control. For local development:
dotnet user-secrets set "Sql:ConnectionString" "Server=...;Encrypt=true;" --project src/SPO2SQL.ConsoleFor scheduled jobs:
export SPO2SQL_SharePoint__TenantId="..."
export SPO2SQL_SharePoint__ClientId="..."
export SPO2SQL_SharePoint__CertificatePath="/secure/spo2sql.pfx"
export SPO2SQL_Sql__ConnectionString="Server=...;Encrypt=true;TrustServerCertificate=false;"On Windows, dotnet run --project src/SPO2SQL.Console -- --setup-secrets stores username, password and connection string in the current user's DPAPI-protected store. The legacy environment names SPO_USERNAME, SPO_PASSWORD and SQL_CONNECTION_STRING are also honoured by the credential provider chain.
List and column mappings still come from the XML file referenced by Application:LegacyConfigPath. It defines which lists are processed, their site-relative paths, destination names and column rules. The file must exist at runtime; a missing file causes an explicit configuration error. Keep deployment-specific XML outside source control when it contains infrastructure details. See docs/Configuration/README.md.
| Mode | When to use | Configuration |
|---|---|---|
| App-only (recommended) | Unattended jobs, MFA/Conditional Access tenants | TenantId, ClientId, CertificatePath (and optionally CertificatePassword) |
| Username/password (ROPC) | Legacy tenants only; blocked by MFA | Username, Password |
At least one complete mode must be configured — startup validation fails otherwise. The app-only scope is {scheme}://{host}/.default for the configured site. Tokens are cached per site and renewed before expiry; token acquisition is serialized to avoid races across concurrent requests.
SQL encryption is enforced by default: Encrypt=true and TrustServerCertificate=false are required unless Sql:EnforceEncryption is explicitly disabled.
- Lists are read with explicit paging (
BatchSizeitems per page), so large lists are not truncated. - On the first run all items are read; the UTC watermark captured before the read is persisted afterwards. Items modified during a run are therefore picked up by the next incremental run.
- Failures in individual inserts are aggregated; the transaction is rolled back rather than leaving a partially loaded snapshot.
- Only additive columns are created on existing tables. Existing columns, data types and rows are never altered or removed by the tool.
- The process stops application startup if health checks report unreachable SharePoint or SQL endpoints.
- The list-specific data-quality routines in
SPO2SQL.Core/SharePointare implemented but are not yet invoked by the default orchestration. - Metrics are exported to the console by default; set
OTEL_EXPORTER_OTLP_ENDPOINTto switch to an OTLP collector. - Integration tests against a live tenant and SQL Server are not part of the automated suite.
GitHub Actions runs on pushes to main/master/develop and on pull requests, on both Ubuntu and Windows:
- Restore (with NuGet audit enabled)
- Build with warnings as errors
- Formatting verification
- Unit tests with coverage upload
- NuGet vulnerability audit
CodeQL analysis is configured separately. GitLab CI mirrors the build, test, format and audit stages.
- Never commit credentials, certificates or connection strings.
- Prefer app-only authentication and least-privilege SQL identities.
- Keep
EnforceEncryptionenabled outside isolated development environments. SharePoint:SiteUrlmust use HTTPS; startup validation rejects plain HTTP.- The Windows credential store file is ACL-restricted to the current user in addition to being DPAPI-encrypted.
- Legacy username/password authentication emits an explicit startup warning.
- Environment-backed secrets survive credential rotation without redeploying binaries.
- Stack traces are logged through the structured logger, not written ad-hoc to stdout.
See docs/SECURITY.md for the full guidance.
Work on feature branches; master receives changes through reviewed pull requests. Keep the local validation loop green before opening a PR:
dotnet build SPO2SQL.sln -warnaserror
dotnet test SPO2SQL.sln
dotnet format SPO2SQL.sln --verify-no-changesMIT.