如何使用PowerShell实现SQL基于证书的身份认证？

一、服务器端前置配置 #

• 创建或导入用于身份认证的证书：在SQL Server所在机器生成证书（或导入现有证书），确保证书包含私钥，且SQL Server服务账户拥有该证书的读取权限。
• 关联证书到SQL登录名：执行SQL语句创建绑定证书的登录名，示例：

  CREATE LOGIN CertBasedLogin FROM CERTIFICATE AuthCertificate;
  -- 给登录名分配数据库权限
  USE YourDatabase;
  CREATE USER CertBasedUser FOR LOGIN CertBasedLogin;
  GRANT SELECT, INSERT ON YourTable TO CertBasedUser;
  

• 启用SQL Server的证书身份认证：确保SQL Server实例允许使用证书登录（默认已支持，无需额外开关，但需确认证书配置正确）。

二、客户端准备 #

• 获取并安装证书：将服务器端证书的公钥（或带私钥的PFX文件）部署到客户端机器，可导入到LocalMachine\My或CurrentUser\My证书存储。
• 验证证书可访问：在PowerShell中执行以下命令确认能找到证书：

  Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object { $_.Subject -match "CN=YourCertSubject" }
  

三、修改PowerShell连接代码 #

替换原有的SQL认证代码，改为通过证书建立连接：

## SQL PART
$SqlServer = "YourSQLInstance"
$Database = "YourDatabase"

# 从证书存储获取证书（根据实际存储位置调整路径）
$cert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object { $_.Subject -eq "CN=AuthCertificate" }

# 创建连接字符串（无需用户名密码）
$connString = "Data Source=$SqlServer;Database=$Database;Integrated Security=False;"

# 创建SQL连接对象并绑定证书
$conn = New-Object System.Data.SqlClient.SqlConnection($connString)
$conn.ClientCertificate = $cert

# 尝试打开连接
try {
    $conn.Open()
    Write-Host "连接成功"
}
catch {
    Write-Host "连接失败: $_"
}
finally {
    $conn.Close()
    $conn.Dispose()
}

注意事项 #

• 证书的主题、指纹需在服务器和客户端保持一致，确保匹配。
• 如果使用PFX文件加载证书，需提供PFX的密码（若设置了密码），示例：

  $cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2(
      "C:\path\to\auth-cert.pfx",
      "PFXPassword",
      [System.Security.Cryptography.X509Certificates.X509KeyStorageFlags]::PersistKeySet
  )
  

• 确保客户端机器能访问SQL Server实例（端口开放、网络连通）。

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