Redacted script based on this article: SQL Server Authentication Using a Virtual Drive
# Set the SQL Server instance name and database name
$SqlServerInstance = 'myServer.MyDomain.local\myInstance'
$DriveRoot = "SQLSERVER:\SQL\$SqlServerInstance"
$DatabaseName = "myDb"
# Set the SQL Server credentials Hint: user in pps = tx-export-sql
function sqldrive
{
param( [string]$driveName, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
$password = read-host -AsSecureString -Prompt "Password"
$credential = new-object System.Management.Automation.PSCredential -argumentlist $login,$password
New-PSDrive $driveName -PSProvider SqlServer -Root $root -Credential $credential -Scope 1
# New-PSDrive WindowsAuth -PSProvider SqlServer -Root $root -Scope 1 # Windows Auth
}
## Use the sqldrive function to create a SQLAuth virtual drive.
sqldrive SQLAuth 'MyLogin' $DriveRoot
## Set-Location to the virtual drive, which invokes the supplied authentication credentials.
Set-Location "SQLAuth:\Databases\$DatabaseName\Schemas"
From the Set-Location, I get an error message [redacted]: SQL Server PowerShell provider error: Could not connect to 'myServer.MyDomain.local\myInstance**+MyLogin**'. [Failed to connect to server myServer.MyDomain.local. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) --> The system cannot find the file specified.]
version info Powershell 7.2.9 SqlServer 2019
I did a successful test with the created $credential object using a Invoke-Sqlcmd statement to rule out problems with credentials and rights.
If Set-Location is succesful a Get-ChildItem will list all the schemas in the database. I have tried this with WindowsAuth (by not including a credential for the PSDrive) and that works fine.
Is the "+MyLogin" in the "Could not connect to"-part of the error message something to worry about?
End-Goal: Trying to use the scripting capabilities of the SqlServer Powershell Module
I think I need a PSDrive for this (oversimplified):
foreach ($ObjectType in Get-ChildItem -Path SqlInstance:\Databases\$DatabaseName) {
foreach ($Object in Get-ChildItem -Path $($ObjectType.PSPath)) {
$Object.Script() | Out-File -Filepath (New-Item -Path $ObjectPath -Force) -Encoding utf8NoBOM
}
}
New script (with redactions):
$ErrorActionPreference = "Stop"
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls11 -bor [System.Net.SecurityProtocolType]::Tls12
# Import the SQLServer module
Import-Module SqlServer
# Set the SQL Server instance name and database name
$SqlServerInstance = "servername\instancename"
$DriveRoot = "SQLSERVER:\SQL\$SqlServerInstance"
$DatabaseName = "dbname"
# Set the SQL Server credentials
if (-not $Credential) {
$Credential = Get-Credential
}
# Test SQL Server credentials
Invoke-Sqlcmd -ServerInstance $SqlServerInstance -Database $DatabaseName -Credential $Credential -Query "SELECT TOP (20) [name] FROM [sys].[schemas]" | Out-String
# recreate PSDrives
$drive = 'windrv'
if (Get-PSDrive | Where-Object Name -eq $drive) {
Write-Output "Remove PSDrive '$drive'"
Remove-PSDrive $drive
}
$drive = 'sqldrv'
if (Get-PSDrive | Where-Object Name -eq $drive) {
Write-Output "Remove PSDrive '$drive'"
Remove-PSDrive $drive
}
New-PSDrive "windrv" -PSProvider SqlServer -Root $DriveRoot # Windows authentication
New-PSDrive "sqldrv" -PSProvider SqlServer -Root $DriveRoot -Credential $Credential # Sql authentication
Write-Output "Root value for sqldrv looks funny ..."
Get-PSDrive 'windrv', 'sqldrv' | Select-Object "Name", "Root"
Write-Output "Test PSDrive 'windrv' Getting database schemas ..."
Get-ChildItem -Path windrv:\Databases\$DatabaseName\Schemas
Write-Output "Test PSDrive 'sqldrv' Getting database schemas ..."
Get-ChildItem -Path sqldrv:\Databases\$DatabaseName\Schemas
Output:
name
----
dbo
guest
INFORMATION_SCHEMA
sys
dsa
dfc
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter
Remove PSDrive 'windrv'
Name Used (GB) Free (GB) Provider Root CurrentLocation
---- --------- --------- -------- ---- ---------------
windrv SqlServer SQLSERVER:\SQL\servername\…
sqldrv SqlServer SQLSERVER:\SQL\servername\…
Root value for sqldrv looks funny ...
Name : windrv
Root : SQLSERVER:\SQL\servername\instancename
Name : sqldrv
Root : SQLSERVER:\SQL\servername\instancename+sqluser
Test PSDrive 'windrv' Getting database schemas ...
Name : dfc
Name : dsa
Test PSDrive 'sqldrv' Getting database schemas ...
Get-ChildItem: myscript.ps1:43:1
Line |
43 | Get-ChildItem -Path sqldrv:\Databases\$DatabaseName\Schemas
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: Could not connect to 'servername\instancename+sqluser'. [Failed to connect to server servername.
| --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL
| Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) --> The system cannot find the file specified.]
