Snippet Name: SQL_Connection_Fix

Tags:

Created Date: 2025-06-16 13:12:49
Last Modified Date: 2025-05-31 10:32:07

Dependencies:

DBA Notes:

Sample Output:

Title: SQL Connection Fix
Author: user
I am importing the SQL Server Module with 
Import-Module SqlServer -RequiredVersion 22.3.0 
but i get the following error based on the simple code below:

###################################################################################################
# Connect to the database
$connectionString = "Server=$serverName;Database=$databaseName;username=sql-izaguirre;password=Warrior#312"
$sqlConnection = New-SqlConnection -ConnectionString $connectionString


New-SqlConnection : The term 'New-SqlConnection' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name,
or if a path was included, verify that the path is correct and try again.

Author: ChatGPT
The error occurs because **`New-SqlConnection`** is not a valid cmdlet in the **SqlServer** module. The correct way to establish a SQL Server connection in PowerShell using the **SqlServer** module is to use `System.Data.SqlClient.SqlConnection` instead.

## Solution:
Replace **`New-SqlConnection`** with **`New-Object System.Data.SqlClient.SqlConnection`**.

### Corrected Code:
```powershell
# Import the SQL Server module
Import-Module SqlServer -RequiredVersion 22.3.0 

# Define connection parameters
$serverName = "your_server_name"
$databaseName = "your_database_name"
$username = "sql-izaguirre"
$password = "Warrior#312"

# Construct the connection string
$connectionString = "Server=$serverName;Database=$databaseName;User Id=$username;Password=$password;"

# Create a SQL connection object
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString

# Open the connection
$sqlConnection.Open()

# Check if connection is successful
if ($sqlConnection.State -eq 'Open') {
    Write-Host "Connected to SQL Server successfully."
} else {
    Write-Host "Failed to connect to SQL Server."
}

# Close the connection
$sqlConnection.Close()
```

### Explanation:
1. **Use `New-Object System.Data.SqlClient.SqlConnection`**  
   The **SqlServer** module does not provide a `New-SqlConnection` cmdlet. Instead, use .NET's `System.Data.SqlClient.SqlConnection` class.

2. **Use `User Id` and `Password` instead of `username` and `password`**  
   The SQL Server connection string expects **`User Id`** and **`Password`** instead of `username` and `password`.

3. **Check and open the connection manually**  
   - The `.Open()` method is required to establish the connection.  
   - The `if` statement verifies if the connection is successful.  
   - Always close the connection using `.Close()` to avoid resource leaks.

This should work without issues. Let me know if you need further adjustments! ????