dbatools.library
The library that powers dbatools, the community module for SQL Server professionals.
Overview
dbatools.library is a .NET library that provides the core functionality for the dbatools PowerShell module. It includes:
- SQL Server Management Objects (SMO) integration
- Microsoft.Data.SqlClient for SQL Server connectivity
- DacFx for database deployment operations
- Extended Events (XEvent) processing capabilities
- High-performance CSV library built for database workflows (also available as standalone NuGet package)
- Multi-framework support (.NET Framework 4.7.2 and .NET 8.0)
This library enables dbatools to work seamlessly across Windows PowerShell 5.1 and PowerShell 7+ on Windows, macOS, and Linux.
Standalone NuGet Packages
Dataplat.Dbatools.Csv
The CSV library built for SQL Server. High-performance CSV reader and writer optimized for database import workflows:
- Native IDataReader - Stream directly to SqlBulkCopy with zero intermediate allocations
- 6x faster than LumenWorks for typical SqlBulkCopy patterns
- Built-in compression - GZip, Deflate, Brotli, ZLib with decompression bomb protection
- Progress & cancellation - Monitor large imports with callbacks, cancel with CancellationToken
- Real-world data handling - Lenient parsing, smart quotes, duplicate headers, field mismatches
dotnet add package Dataplat.Dbatools.Csv
Resources:
Installation
Install from the PowerShell Gallery:
# Recommended: Install for all users (requires admin/sudo)
Install-Module dbatools.library -Scope AllUsers
# Or install for current user only
Install-Module dbatools.library -Scope CurrentUser
Handling DLL Conflicts with SqlServer Module
If you need to use both the SqlServer module and dbatools.library in the same session, you may encounter DLL version conflicts. dbatools.library provides the -AvoidConflicts parameter to automatically skip loading assemblies that are already loaded by another module.
Usage
Import the SqlServer module first, then import dbatools.library directly with -AvoidConflicts:
# Import SqlServer module first
Import-Module SqlServer
# Then import dbatools.library with -AvoidConflicts
# IMPORTANT: Use -ArgumentList $true (NOT a hashtable)
Import-Module dbatools.library -ArgumentList $true
Note: You must import dbatools.library directly, not via the dbatools module. The -ArgumentList parameter cannot be passed through module dependencies. If you need to use the full dbatools module with SqlServer, import dbatools.library first with -ArgumentList $true, then import dbatools.
When -AvoidConflicts is enabled, dbatools.library will:
- Check if each assembly is already loaded in the current session
- Skip loading any assemblies that are already present (including Microsoft.Data.SqlClient)
- Load only the assemblies that are missing
Examples
Basic usage with SqlServer module:
Import-Module SqlServer
Import-Module dbatools.library -ArgumentList $true
Using with the full dbatools module:
Import-Module SqlServer
Import-Module dbatools.library -ArgumentList $true # Load library first with AvoidConflicts
Import-Module dbatools # Then load dbatools (will use already-loaded library)
See what's being skipped with -Verbose:
Import-Module SqlServer
Import-Module dbatools.library -ArgumentList $true -Verbose
# Output will show: "Skipping Microsoft.Data.SqlClient.dll - already loaded" etc.
Without AvoidConflicts (default behavior):
# Loads all assemblies, may cause conflicts if SqlServer module is already loaded
Import-Module dbatools.library
Common Mistakes
❌ Wrong: Using a hashtable for ArgumentList
Import-Module dbatools.library -ArgumentList @{AvoidConflicts = $true} # This will NOT work
✅ Correct: Using a boolean value
Import-Module dbatools.library -ArgumentList $true # This works correctly
⚠️ Important: PowerShell Core + Credentials Issue
If you plan to use SQL Server credentials with PowerShell Core (pwsh), you MUST install to AllUsers scope or grant appropriate permissions.
The Issue
When using -SqlCredential with PowerShell Core, you may encounter this error:
unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll'
Root Cause
This is a PowerShell Core + Microsoft.Data.SqlClient architectural limitation, not a bug in dbatools.library:
Credential Impersonation: When credentials are passed to SQL Server connections, the .NET runtime performs thread-level impersonation using those credentials.
DLL Access Under Impersonation: During impersonation, Microsoft.Data.SqlClient tries to load its native dependency Microsoft.Data.SqlClient.SNI.dll, but file system access occurs under the impersonated credential's security context, not your current user's context.
Permission Denied: The impersonated account often lacks read permissions to the module files in your local profile directory (e.g., C:\Users\<user>\Documents\PowerShell\Modules\), causing the DLL load to fail.
Why Windows PowerShell 5.1 Works
- Uses
System.Data.SqlClient which is available in the Global Assembly Cache (GAC)
- Different assembly loading behavior that doesn't trigger the same impersonation/access pattern
- The native SNI.dll is already loaded system-wide
Solutions
Option 1: Install to AllUsers Scope (Recommended)
# Uninstall any existing CurrentUser installation first
Uninstall-Module dbatools.library -Force
Uninstall-Module dbatools -Force
# Install to AllUsers scope (requires admin on Windows, sudo on Linux/macOS)
Install-Module dbatools.library -Scope AllUsers
Install-Module dbatools -Scope AllUsers
This places the module in C:\Program Files\PowerShell\Modules\ (Windows) or /usr/local/share/powershell/Modules (Linux/macOS), which typically has broader read permissions.
Option 2: Grant Permissions (Windows)
If you cannot use AllUsers scope, grant the credential account read access to your PowerShell modules folder:
$modulePath = "$env:USERPROFILE\Documents\PowerShell\Modules"
icacls $modulePath /grant "DOMAIN\User:(OI)(CI)R" /T
Option 3: Use Windows PowerShell 5.1
If neither option above works for your environment, use Windows PowerShell instead of PowerShell Core for credential-based connections:
# From Windows PowerShell 5.1
Import-Module dbatools
Connect-DbaInstance -SqlInstance server -SqlCredential $cred
Additional Notes
- Commands like
Get-DbaDiskSpace -Credential work fine because they use WinRM/PowerShell Remoting, not SQL Server authentication
- This issue affects any PowerShell Core module using Microsoft.Data.SqlClient with credentials
- Related to PowerShell Issue #11616
For more details, see Issue #28.
Development
Prerequisites
- .NET SDK 8.0 or later
- .NET Framework 4.7.2 Developer Pack (Windows only)
- PowerShell 7.2+ or Windows PowerShell 5.1
- Git
Building
# Clone the repository
git clone https://github.com/dataplat/dbatools.library.git
cd dbatools.library
# Build the library
dotnet build project/dbatools.sln
# Or build for specific configuration
dotnet build project/dbatools.sln -c Release
The compiled assemblies will be output to artifacts/lib/.
Multi-Framework Support
The library targets both:
- .NET Framework 4.7.2: For Windows PowerShell 5.1 compatibility
- .NET 8.0: For PowerShell 7+ cross-platform support
Project Structure
dbatools.library/
├── project/
│ ├── dbatools/ # Main C# library project
│ │ └── Csv/ # CSV reader/writer source
│ ├── Dataplat.Dbatools.Csv/ # Standalone CSV NuGet package
│ ├── dbatools.Tests/ # Unit tests
│ └── dbatools.sln # Solution file
├── build/ # Build scripts
├── var/ # Runtime dependencies
├── dbatools.library.psm1 # PowerShell module script
├── dbatools.library.psd1 # PowerShell module manifest
└── README.md
Testing
# Run tests
dotnet test project/dbatools.Tests/dbatools.Tests.csproj
Importing for Development
To use your local development version:
# Import the module from your local clone
Import-Module /path/to/dbatools.library/dbatools.library.psd1 -Force
# Verify the version and path
Get-Module dbatools.library | Select-Object Name, Version, Path
Key Dependencies
This library includes several major SQL Server components:
| Package |
Version |
Purpose |
| Microsoft.Data.SqlClient |
6.0.2 |
SQL Server connectivity |
| Microsoft.SqlServer.SqlManagementObjects |
172.76.0 |
SQL Server Management Objects (SMO) |
| Microsoft.SqlServer.DacFx |
170.0.94 |
Data-tier Application Framework |
| Microsoft.AnalysisServices |
19.101.1 |
Analysis Services management |
| Microsoft.SqlServer.XEvent.XELite |
2024.2.5.1 |
Extended Events processing |
Standalone Packages
Contributing
Contributions are welcome! This library is primarily maintained by the dbatools team.
Reporting Issues
- For bugs specific to this library, open an issue in this repository
- For dbatools-related issues, use the dbatools repository
- For the DLL loading issue with credentials, see Issue #28
Pull Requests
- Fork the repository
- Create a feature branch (
git checkout -b feature/your-feature)
- Make your changes
- Add tests if applicable
- Ensure the build succeeds
- Commit your changes (
git commit -m 'Add some feature')
- Push to the branch (
git push origin feature/your-feature)
- Open a Pull Request
Resources
License
This project is licensed under the MIT License - see the LICENSE file for details.