Setting Up the Environment for the Applicative User

MS SQL Express Settings

1. Change SQL Server Properties

Security Settings

Follow these steps to enable SQL Server authentication:

1. Open SQL Server Management Studio.
2. Right-click on the server and select Properties.
3. Navigate to the Security tab.
4. Select SQL Server and Windows Authentication mode.

2. Add New SQL Authentication User

Under Server -> Security -> Logins

To create a new SQL authentication user:

1. Right-click Logins and select New Login….
2. Under the General page:
   • Enter the username under Login Name.
   • Select SQL Server authentication.
   • Set a password and uncheck User must change password at next login.
3. Under User Mapping:
   • Select the database under Users mapped to this login:.
   • Select public and db_owner under Database role membership for: the database name.

Test Connection

Use the following PowerShell script to test the database connection:

$serverName = '.\SQLEXPRESS'
$databaseName = 'PrintBOS'
$username = 'PrintBOS_SQL'
$password = 'password'

# Convert the cleartext password to SecureString
$securePassword = ConvertTo-SecureString -String $password -AsPlainText -Force

# Create SQL connection
$connectionString = "Server=$serverName;Database=$databaseName;User Id=$username;Password=$password;TrustServerCertificate=True"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

try {
    # Attempt to open the connection
    Write-Output "[INFO] Attempting to open SQL connection..."
    $connection.Open()
    Write-Output "[SUCCESS] Database connection successfully established."
    # Additional code...
}
catch {
    Write-Output "[ERROR] Error during connection or command execution: $_.Exception.Message"
    Write-Output "[ERROR] Full Exception: $_.Exception.ToString()"
}
finally {
    if ($connection.State -eq [System.Data.ConnectionState]::Open) {
        $connection.Close()
        Write-Output "[INFO] Database connection closed."
    }
}

# Display a message indicating script completion
Write-Output "[INFO] Script execution completed."
    

Expected Result

Upon successful execution, the script should display the following messages:

[INFO] Attempting to open SQL connection...
[SUCCESS] Database connection successfully established.
[INFO] Database connection closed.
[INFO] Script execution completed.