SQL认证创建SQL Server PSDrive失败，报错无法连接求助

问题描述 #

基于微软文档《SQL Server Authentication Using a Virtual Drive》编写脚本，通过SQL认证创建PSDrive后，执行Set-Location或Get-ChildItem时出现连接失败错误。脚本如下：

# 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"

错误信息 #

执行操作时报错：

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.]

环境与已验证信息 #

• PowerShell 7.2.9
• SQL Server 2019
• 已验证内容：
  • 使用Invoke-Sqlcmd通过相同凭据可成功执行查询，排除凭据权限问题
  • Windows认证的PSDrive可正常操作
  • 补充测试：SQL认证的PSDrive Root值自动拼接了+sqluser，执行Get-ChildItem仍报错，Windows认证无此问题

目标 #

解决连接问题，实现通过SqlServer模块的脚本生成功能，示例代码：

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
      }
}

解决方案 #

问题核心是SQL Server PowerShell Provider在处理SQL认证的PSDrive时，错误地将用户名拼接进服务器实例名称（即报错中的+MyLogin部分），导致连接目标无效。可通过以下两种方法修复：

方法1：调整PSDrive的Root路径格式 #

创建PSDrive时，Root路径不要使用SQLSERVER:\SQL\实例名的格式，改用仅实例名作为Root参数：

function sqldrive  
{  
    param( [string]$driveName, [string]$login = "MyLogin", [string]$root = "MyComputer\MyInstance" )  
    $password = read-host -AsSecureString -Prompt "Password"  
    $credential = new-object System.Management.Automation.PSCredential -argumentlist $login,$password  
    # 去掉SQLSERVER:\SQL前缀，直接用实例名作为Root
    New-PSDrive $driveName -PSProvider SqlServer -Root $root -Credential $credential -Scope 1  
}  

# 调用时直接传入实例名，不要带SQLSERVER:\SQL前缀
sqldrive SQLAuth 'MyLogin' $SqlServerInstance

# 切换路径时使用驱动器名+完整层级
Set-Location "SQLAuth:\Databases\$DatabaseName\Schemas"

方法2：使用Connect-SqlInstance预建立连接 #

先通过Connect-SqlInstance用SQL认证建立连接，再基于该连接创建PSDrive：

$SqlServerInstance = 'myServer.MyDomain.local\myInstance'
$DatabaseName = "myDb"

$password = Read-Host -AsSecureString -Prompt "Password"
$credential = New-Object System.Management.Automation.PSCredential -ArgumentList 'MyLogin', $password

# 先建立SQL连接
$sqlInstance = Connect-SqlInstance -ServerInstance $SqlServerInstance -Credential $credential

# 基于连接创建PSDrive，Root直接使用实例对象
New-PSDrive -Name SQLAuth -PSProvider SqlServer -Root $sqlInstance -Scope 1

# 后续操作正常执行
Set-Location "SQLAuth:\Databases\$DatabaseName\Schemas"

验证脚本生成功能 #

修复PSDrive连接后，即可正常运行脚本生成代码：

$ObjectPath = "C:\Your\Output\Path" # 替换为实际输出路径
foreach ($ObjectType in Get-ChildItem -Path SQLAuth:\Databases\$DatabaseName) {
    foreach ($Object in Get-ChildItem -Path $($ObjectType.PSPath)) {
        $outputFile = Join-Path -Path $ObjectPath -ChildPath "$($Object.Name).sql"
        $Object.Script() | Out-File -FilePath $outputFile -Encoding utf8NoBOM -Force
    }
}

内容的提问来源于stack exchange，提问作者Marco Jansen