Keywords: PowerShell | SQL Server | Connection String | Database Connection | Authentication Configuration
Abstract: This article provides an in-depth analysis of common connection string configuration errors when connecting to SQL Server databases from PowerShell. Through examination of a typical error case, it explains the mutual exclusivity principle between integrated security and user credential authentication, offers correct connection string configuration methods, and presents complete code examples with best practice recommendations. The article also discusses auxiliary diagnostic approaches including firewall configuration verification and database connection testing.
Problem Background and Error Analysis
When connecting to SQL Server databases from PowerShell scripts, developers frequently encounter authentication configuration conflicts. From the provided error message "Windows logins are not supported in this version of SQL Server," we can infer that the root cause lies in using both integrated security and user credentials simultaneously in the connection string.
Authentication Mechanism Principles
SQL Server provides two primary authentication methods: Windows Integrated Authentication and SQL Server Authentication. Windows Integrated Authentication relies on current Windows user credentials and is enabled by setting Integrated Security = True. SQL Server Authentication requires explicit specification of username and password parameters. These two authentication mechanisms are mutually exclusive by design and cannot be used together in the same connection string.
Solution Implementation
According to the best answer recommendation, the correct connection string configuration should select one authentication method based on requirements:
Using Windows Integrated Authentication:
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"Using SQL Server Authentication:
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $uid; Password = $pwd;"Complete Code Example
Below is a complete PowerShell script example using SQL Server Authentication:
$SQLServer = "aaaa.database.windows.net"
$SQLDBName = "Database"
$uid = "john"
$pwd = "pwd123"
$SqlQuery = "SELECT * from table;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | out-file "C:\Scripts\xxxx.csv"
$SqlConnection.Close()Auxiliary Diagnostic Methods
Beyond correcting connection string configuration, the following verification steps should be performed:
- Attempt to connect to the database using the same username and password in SQL Server Management Studio
- Check network firewall settings to ensure PowerShell scripts have access to the database server
- Verify that the database server address and port are accessible
- Confirm that the database user has appropriate permissions to execute queries
Best Practice Recommendations
In production environments, the following security best practices are recommended:
- Use Windows Integrated Authentication for enhanced security
- Avoid hardcoding passwords in scripts; consider using secure credential storage
- Implement proper error handling and connection resource cleanup
- Encrypt sensitive data during transmission