Script checking SQL DB table and creating an event entry

Hello!

I’d like to create a script which will be checking SQL databases in terms of default password and if such password exist, create an event log entry.
I don’t have much experience with PowerShell, and after few hours I’m stuck.
At the moment I’ve created such script, which isn’t perfect, but gathering some data. I stuck on the point, how to verify if the default password exist and if yes, move to step creating an event log entry.

I’ll be grateful for any advises how to achieve that.


$Look4DBs = “select name from sys.databases where name like ‘%XXX%’”
$PassSelect = “select code, name, password from table
where code in (‘User1’,‘User2’,‘User3’) and
password in (‘Password0’,‘Password1’,‘Password3’)”

$DBs = Invoke-Sqlcmd -ServerInstance localhost -Database master -Query $Look4DBs
For ($db = 0; $db -lt $DBs.Count; $db++)
{
Invoke-Sqlcmd -ServerInstance localhost -Database $DBs.Name[$db] -Query $PassSelect | Select name,password
Write-Output $DBs.Name[$db]
Write-Output “”
}

The query is looking for any of the users that have any of the passwords. If the query returns something, then it would be rolled into the $results variable. Get this working first and then you could generate a Windows Event or do anything else you want to do with that data:

$Look4DBs = "select name from sys.databases where name like '%XXX%'"

$PassSelect = @"
select code, 
       name,
       password 
from table
where code in (
  'User1',
  'User2',
  'User3'
) 
and password in (
  'Password0',
  'Password1',
  'Password3'
)
"@

$DBs = Invoke-Sqlcmd -ServerInstance localhost -Database master -Query $Look4DBs

$results = foreach ($db in $dbs) {
  Invoke-Sqlcmd -ServerInstance localhost -Database $db.Name -Query $PassSelect | 
    Select-Object *, @{Name='Database';Expression={$db}}
}

$results

@rob-simmers thank you for an answer. I slightly modified the code, by adding another variable with passwords, which I’d like to use to check after get the results into $Results with if statement, but I honestly don’t know why that isn’t working.
I try to google the solution and find e.g. that topic but as I early mentioned, don’t know why that isn’t working.
Maybe you can help explain?

$Look4DBs = "select name from sys.databases where name like '%_XXX_%'"

$PassSelect = "
    select code, name,password 
    from table
    where code in ('User1','User2','User3')
    and password in ('Password1','Password2','Password3')
"
$Passwords = @('Password1','Password2','Password3')

$DBs = Invoke-Sqlcmd -ServerInstance localhost -Database master -Query $Look4DBs

$Results = foreach ($db in $dbs) {
    Invoke-Sqlcmd -ServerInstance localhost -Database $db.Name -Query $PassSelect | 
    Select-Object @{Name='Database';Expression={$db.Name}},@{Name='Username';Expression={$_.code}},@{Name='Password';Expression={$_.password}}
}

$Passwords | ForEach-Object {
        if ($Results -contains $_){
            New-EventLog # rest of details #
        }
}
    

Well, we can assist in troubleshooting, but you have not specifically said what is not working? Provide details on the error message, null results or what you are seeing? We can’t see your screen or your script running and I don’t even have SQL in front of me, so be specific.

Oh, sorry about that. There’s null results when I’m using if statement.

A bit confused. The SQL query is running and finding all of the users with a password that is in your IN statement. If a result is returned, then you have users with those default passwords. The results would already contain users with password, code and the database. The SQL query should return nothing if you there was none of the users or none of the users with the password.

Yes, we have results from the SQL, but my general idea is to:

  1. Perform a query to check DB names
  2. Check DB tables if there’s a default password
  3. If password exist, then create an event log entry.

So if I understand it correctly, what we achieved at the moment, still needs to be passed to IF statement checking if within the results we have one of default passwords and then create an event entry.
But I don’t know why the IF isn’t working.

Have you reviewed $results? All of the rows should have users with the default password and the database they are in. Let’s look at a Powershell example:

Emulate a similar structure:

$mockDb = @"
Database,Code,Name,Password
db1,user1,Frank,password2
db1,user3,Julie,changedPassword2
db2,user6,Sam,password1
db2,user7,Tom,password3
db3,user4,Hank,changedPassword1
db3,user2,Sally,changedPassword3
"@ | ConvertFrom-Csv

Which give us an object or table like so:

Database Code  Name  Password
-------- ----  ----  --------
db1      user1 Frank password2
db1      user3 Julie changedPassword2
db2      user6 Sam   password1
db2      user7 Tom   password3
db3      user4 Hank  changedPassword1
db3      user2 Sally changedPassword3

We can perform the same query you basically are performing in SQL:

$mockDb | Where-Object -FilterScript {$_.Code -in @('user1','user2','user3','user4','user6','user7') -and $_.Password -in @('password1','password2','password3')}

This returns only records where one of the users or one of the passwords matched the query:

Database Code  Name  Password
-------- ----  ----  --------
db1      user1 Frank password2
db2      user6 Sam   password1
db2      user7 Tom   password3

It does not return the records with the changedPassword:

PS C:\Users\rasim> $mockDb | Where-Object -FilterScript {$_.Password -like 'changedPassword*'}

Database Code  Name  Password
-------- ----  ----  --------
db1      user3 Julie changedPassword2
db3      user4 Hank  changedPassword1
db3      user2 Sally changedPassword3

The records in $results should be all of the matched query they is being specified for audit. Is this what you are seeing?

Morning Rob,
Filtering with Where-Object is working as example:

$mockDb | Where-Object -FilterScript {$_.Code -in @('user1','user2','user3','user4','user6','user7') -and $_.Password -in @('password1','password2','password3')}

But that isn’t exactly what I’m looking for.
I’d like to create an EventLog entry when $Results contains one of default passwords.
We’ve already check the DB’s under they have or not the default password and we’ve $Results of that.
Now I assume that needs to be passed to IF statement and as a second step {New-EventLog…}.
Because, when we create a new line in the script with New-EventLog after Where-Object filtering it will be creating event’s every time, even if there won’t be a default password - and that’s the trigger to rise an alert.

Hi,
I found some help within the team and below is final solution for the case if someone is interested.
Script looking for DB’s with particular DB name, then checking every database in terms of having default password.
Later for every DB with default password creating an event log with information about default password.

Thanks for help @rob-simmers !

$Look4DBs = "select name from sys.databases where name like '%_XXX_%'"

$PassSelect = "
    select code, name,password 
    from com_usr
    where code in ('User1','User2','User3')
    and password in ('Password1','Password2','Password3')
"

$Passwords = @('Password1','Password2','Password3')

$DBs = Invoke-Sqlcmd -ServerInstance localhost -Database master -Query $Look4DBs

$Results = foreach ($db in $dbs) {
    Invoke-Sqlcmd -ServerInstance localhost -Database $db.Name -Query $PassSelect | 
    Select-Object @{Name='Database';Expression={$db.Name}},@{Name='Username';Expression={$_.code}},@{Name='Password';Expression={$_.password}}
}

$Results | ForEach-Object {
    if ($Passwords -contains $_.password)
    {
        New-EventLog -LogName "Application" -Source 'DBPassCheck'
        Write-EventLog -LogName "Application" -Source "DBPassCheck" -EventId 10 -EntryType Information -Category 0 -Message "Database $($_.database) user $($_.username) has default password."
    }
}