如何在PowerShell中无需连接字符串传凭据连接本地SQL Server

以下几种方式可以避免在连接字符串中直接明文传递SQL Server凭据：

• 使用Windows集成身份验证（本地场景首选）
  如果你的本地SQL Server启用了Windows身份验证，直接利用当前运行PowerShell的Windows账号权限连接，无需手动指定用户名密码：

  $SqlServer = "localhost"
  $Database = "YourDatabaseName"
  
  # 连接字符串启用集成安全
  $connString = "Data Source=$SqlServer;Database=$Database;Integrated Security=True"
  
  $conn = New-Object System.Data.SqlClient.SqlConnection $connString
  $conn.Open()
  
  # 执行数据库操作...
  $conn.Close()
  

• 使用SqlCredential对象封装SQL身份验证凭据
  把用户名和密码封装到SqlCredential对象中，再绑定到连接实例，连接字符串无需包含凭据信息：

  $SqlServer = "localhost"
  $Database = "YourDatabaseName"
  $SqlAuthLogin = "sql_login_account"
  # 将明文密码转换为安全字符串
  $SqlAuthPw = ConvertTo-SecureString "your_sql_password" -AsPlainText -Force
  
  # 创建SqlCredential对象
  $credential = New-Object System.Data.SqlClient.SqlCredential($SqlAuthLogin, $SqlAuthPw)
  
  # 连接字符串仅包含服务器和数据库信息
  $connString = "Data Source=$SqlServer;Database=$Database"
  
  $conn = New-Object System.Data.SqlClient.SqlConnection $connString
  $conn.Credential = $credential
  $conn.Open()
  
  # 执行数据库操作...
  $conn.Close()
  

• 交互式获取凭据（适合手动操作场景）
  通过Get-Credential弹出交互式对话框让用户输入凭据，避免硬编码密码：

  $SqlServer = "localhost"
  $Database = "YourDatabaseName"
  
  # 弹出凭据输入框
  $userInput = Get-Credential -Message "输入SQL Server身份验证凭据"
  $credential = New-Object System.Data.SqlClient.SqlCredential($userInput.UserName, $userInput.Password)
  
  $connString = "Data Source=$SqlServer;Database=$Database"
  $conn = New-Object System.Data.SqlClient.SqlConnection $connString
  $conn.Credential = $credential
  $conn.Open()
  
  # 执行数据库操作...
  $conn.Close()
  

• 从Windows凭据管理器读取存储的凭据
  提前将SQL凭据存储到Windows凭据管理器，再通过PowerShell读取使用，完全避免明文密码：

  $SqlServer = "localhost"
  $Database = "YourDatabaseName"
  # 凭据管理器中存储的目标名称，格式一般为MicrosoftSQLServer/服务器名
  $targetName = "MicrosoftSQLServer/$SqlServer"
  
  # 需先安装CredentialManager模块：Install-Module CredentialManager
  $storedCredential = Get-StoredCredential -Target $targetName
  
  $credential = New-Object System.Data.SqlClient.SqlCredential($storedCredential.UserName, $storedCredential.Password)
  
  $connString = "Data Source=$SqlServer;Database=$Database"
  $conn = New-Object System.Data.SqlClient.SqlConnection $connString
  $conn.Credential = $credential
  $conn.Open()
  
  # 执行数据库操作...
  $conn.Close()
  

内容的提问来源于stack exchange，提问作者Shubh kumar