[CmdLetBinding()]
Param([Parameter(
Mandatory = $true,
HelpMessage = "The path to a config file. For example: .\Web.config."
)][string]$Path)

$ErrorActionPreference = "Stop"

Add-Type -AssemblyName System.Configuration
Add-Type -AssemblyName System.Data
Add-Type -AssemblyName System.Data.Entity
Add-Type -AssemblyName System.Web

function Test-ConnectionString
{
    Param([Parameter(Mandatory = $true)][string]$ConnectionString,
          [Parameter(Mandatory = $true)][System.Data.Common.DbProviderFactory]$DbProviderFactory)
    $connection = $DbProviderFactory.CreateConnection()
    $connection.ConnectionString = $ConnectionString
    try
    {
        $connection.Open()
    }
    catch [System.Data.Common.DbException]
    {
        Write-Warning $_.Exception.Message
        return $false
    }
    finally
    {
        $connection.Close()
    }

    return $true
}

function New-DbProviderFactory
{
    [OutputType([System.Data.Common.DbProviderFactory])]
    Param([Parameter(Mandatory = $true)] [string]$ProviderName)

    # Special handling for Entity Framework - its factory is not installed by default
    if ($ProviderName -eq "System.Data.EntityClient")
    {
        return [System.Data.EntityClient.EntityProviderFactory]::Instance
    }

    return [System.Data.Common.DbProviderFactories]::GetFactory($ProviderName)
}

function Parse-ConnectionString
{
    [OutputType([System.Data.Common.DbConnectionStringBuilder])]
    Param([Parameter(Mandatory = $true)][string]$ConnectionString,
          [Parameter(Mandatory = $true)][System.Data.Common.DbProviderFactory]$DbProviderFactory)
    $builder = $DbProviderFactory.CreateConnectionStringBuilder()
    $builder.set_ConnectionString($ConnectionString)
    return $builder
}

function Read-Configuration
{
    [OutputType([System.Configuration.Configuration])]
    Param([Parameter(Mandatory = $true)][string]$ConfigFilename)
    if ($ConfigFileName -match "Web.config")
    {
        $fileInfo = New-Object System.IO.FileInfo -ArgumentList $ConfigFilename
        $directoryMapping = New-Object System.Web.Configuration.VirtualDirectoryMapping -ArgumentList $fileInfo.DirectoryName, $true, $fileInfo.Name
        $configMap = New-Object System.Web.Configuration.WebConfigurationFileMap
        $configMap.VirtualDirectories.Add("/", $directoryMapping)
        return [System.Web.Configuration.WebConfigurationManager]::OpenMappedWebConfiguration($configMap, "/")
    }
    else
    {
        $configMap = New-Object System.Configuration.ExeConfigurationFileMap
        $configMap.ExeConfigFilename = $ConfigFilename
        return [System.Configuration.ConfigurationManager]::OpenMappedExeConfiguration($configMap, [System.Configuration.ConfigurationUserLevel]::None)
    }
}

$step = 1
$config = Read-Configuration -ConfigFilename (Convert-Path $Path)
foreach ($cs in $config.ConnectionStrings.ConnectionStrings)
{
    $factory = New-DbProviderFactory -ProviderName $cs.ProviderName
    $builder = Parse-ConnectionString -ConnectionString $cs.ConnectionString -DbProviderFactory $factory

    # Special handling for Entity Framework (nested connetion strings) (YUCK)
    if ($builder -is [System.Data.EntityClient.EntityConnectionStringBuilder])
    {
        $isEntityFramework = $true
        $entityfactory = $factory
        $entityBuilder = $builder
        $factory = New-DbProviderFactory -ProviderName $builder.Provider
        $builder = Parse-ConnectionString -ConnectionString $builder.ProviderConnectionString -DbProviderFactory $factory
    }
    else
    {
        $isEntityFramework = $false
    }

    # Skip connection strings that use Windows Authentication (Integrated Security is SSPI -or- true -or- yes)
    $integrated = $builder.'integrated security'
    if ($integrated -match 'SSPI' -or $integrated -match 'true' -or $integrated -match 'yes')
    {
        continue
    }

    $name = $cs.Name
    $server = $builder.DataSource
    $database = $builder.InitialCatalog
    $user = $builder.UserID
    while (!(Test-ConnectionString -ConnectionString $builder.ConnectionString -DbProviderFactory $factory))
    {
        $user = $builder.UserID
        Write-Warning "$step : ($name) Login failed! User ID: '$user', Database: '$database', Server: '$server'"
        $credential = Get-Credential -UserName $builder.'User ID' -Message "$step : Please enter database credentials for '$name' (Database: '$database', Server: '$server')"
        if ($credential -eq $null)
        {
            break
        }

        $credential =  $credential.GetNetworkCredential()
        $builder.'User ID' = $credential.UserName
        $builder.Password = $credential.Password
    }

    if (Test-ConnectionString -ConnectionString $builder.ConnectionString -DbProviderFactory $factory)
    {
        Write-Host "$step : ($name) Successfully logged in! User ID: '$user', Database: '$database', Server: '$server'"

        # Special handling for Entity Framework (nested connetion strings) (YUCK)
        if ($isEntityFramework)
        {
            $entityBuilder.'provider connection string' = $builder.ConnectionString
            $builder = $entityBuilder
        }

        $cs.ConnectionString = $builder.ConnectionString
    }

    $step += 1
}

$config.Save()