March 1, 2016

Scripts to check SQL connection strings


 Some powershell functions I wrote to test SQL Connection strings:





    Function Test-SQLConnTrust ($Server, $DB)
    {
    $connectionString = "Data Source=$Server;Trusted_Connection=True;Initial Catalog=$DB;Connect Timeout=3;"
    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
    trap
    {
    Write-Error "Cannot connect to $Server.";
    continue
    }
    $sqlConn.Open()
    if ($sqlConn.State -eq 'Open')
    {
    $sqlConn.Close();
    "Opened successfully."
    }
    }

    Function Test-SQLConn ($Server, $DB)
    {
    $connectionString = "Data Source=$Server;Integrated Security=true;Initial Catalog=$DB;Connect Timeout=3;"
    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
    trap
    {
    Write-Error "Cannot connect to $Server.";
    continue
    }
    $sqlConn.Open()
    if ($sqlConn.State -eq 'Open')
    {
    $sqlConn.Close();
    "Opened successfully."
    }
    }

    Function Test-SQLServTrust ($Server, $DB)
    {
    $connectionString = "Server=$Server;Database=$DB;Trusted_Connection=True;Connect Timeout=3;"
    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
    trap
    {
    Write-Error "Cannot connect to $Server.";
    continue
    }
    $sqlConn.Open()
    if ($sqlConn.State -eq 'Open')
    {
    $sqlConn.Close();
    "Opened successfully."
    }
    }


    Function Test-SQLServTrust ($Server, $DB, $Username, $Password)
    {
    $connectionString = "Server=$Server;Database=$DB;User Id=$Username;Password=$Password;"
    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
    trap
    {
    Write-Error "Cannot connect to $Server.";
    continue
    }
    $sqlConn.Open()
    if ($sqlConn.State -eq 'Open')
    {
    $sqlConn.Close();
    "Opened successfully."
    }
    }

References:
  • https://www.connectionstrings.com/sql-server-2008/
  • https://www.connectionstrings.com/sql-server-2012/
  • http://www.sqlstrings.com/sql-server-connection-strings.htm