Solved

Powershell output to SQL

Posted on 2016-11-01
17
64 Views
Last Modified: 2016-11-03
so I have some file servers I'm auditing ACLS in, the file output is too large for CSV, So someone recommended using SQL, so i have 2012 express installed on a server, I created a database and created a table with the headers represented in the script below:

$ErrorActionPreference = "Continue"
$strComputer = $env:ComputerName
$colDrives = Get-PSDrive -PSProvider Filesystem
ForEach ($DriveLetter in $colDrives) {
    $StartPath = "E:\Share"
    Get-ChildItem -LiteralPath $StartPath -Recurse |
    ForEach {
      $FullPath = Get-Item -LiteralPath (Get-Item -LiteralPath $_.PSPath)
      (Get-Item -LiteralPath $FullPath).GetAccessControl() |
      Select * -Expand Access |
      Select @{N='Server Name';E={$strComputer}},
             @{N='Full Path';E={$FullPath}},
             @{N='Type';E={If($FullPath.PSIsContainer -eq $True) {'D'} Else {'F'}}},
             @{N='Owner';E={$_.Owner}},
             @{N='Trustee';E={$_.IdentityReference}},
             @{N='Inherited';E={$_.IsInherited}},
             @{N='Inheritance Flags';E={$_.InheritanceFlags}},
             @{N='Ace Flags';E={$_.PropagationFlags}},
             @{N='Ace Type';E={$_.AccessControlType}},
             @{N='Access Masks';E={$_.FileSystemRights}} } |


Could someone help with the steps that would allow the results of this script to write to that DB?
0
Comment
Question by:leadtheway
  • 10
  • 6
17 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41868956
I don't have your specific answer but I found out this free ebook a few minutes ago: https://www.syncfusion.com/resources/techportal/details/ebooks/powershell
0
 
LVL 12

Accepted Solution

by:
Dustin Saunders earned 500 total points
ID: 41868958
I would write a function and then call that for each object after you collect the information.  Edit the connection string and table name here, but this code will do the trick:

$connectionString = "server='localhost';database='testDatabase';trusted_connection=true;"
$tableName = "FilePermissions"

function WriteSQL ($query)
{
    if ($debug -eq $true) {Write-Host $query}
    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = $connectionString
    $Connection.Open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $Connection
    $command.CommandText = $query
    $command.ExecuteReader()
    $connection.Close()
}

$ErrorActionPreference = "Continue" 
$strComputer = $env:ComputerName 
$colDrives = Get-PSDrive -PSProvider Filesystem 
ForEach ($DriveLetter in $colDrives) { 
    $StartPath = "C:\test" 
    Get-ChildItem -LiteralPath $StartPath -Recurse | 
    ForEach { 
      $FullPath = Get-Item -LiteralPath (Get-Item -LiteralPath $_.PSPath) 
      (Get-Item -LiteralPath $FullPath).GetAccessControl() | 
      Select * -Expand Access | 
      Select @{N='ServerName';E={$strComputer}}, 
             @{N='FullPath';E={$FullPath}}, 
             @{N='Type';E={If($FullPath.PSIsContainer -eq $True) {'D'} Else {'F'}}}, 
             @{N='Owner';E={$_.Owner}}, 
             @{N='Trustee';E={$_.IdentityReference}}, 
             @{N='Inherited';E={$_.IsInherited}}, 
             @{N='InheritanceFlags';E={$_.InheritanceFlags}}, 
             @{N='AceFlag';E={$_.PropagationFlags}}, 
             @{N='AceType';E={$_.AccessControlType}}, 
             @{N='AccessMasks';E={$_.FileSystemRights}} } |

             %{
                $query = "INSERT INTO $tableName (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks) VALUES ('$($_.servername)','$($_.fullpath)','$($_.type)','$($_.owner)','$($_.trustee)','$($_.inherited)','$($_.inheritanceflags)','$($_.aceflag)','$($_.acetype)','$($_.accessmasks)')"
                WriteSQL $query
             }

    }
             

Open in new window


Here is the table I created to test with:
USE [testDatabase]
GO

/****** Object:  Table [dbo].[FilePermissions]    Script Date: 11/1/2016 1:52:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FilePermissions](
	[servername] [nvarchar](MAX) NULL,
	[fullpath] [nvarchar](MAX) NULL,
	[type] [nvarchar](MAX) NULL,
	[owner] [nvarchar](MAX) NULL,
	[trustee] [nvarchar](MAX) NULL,
	[inherited] [nvarchar](MAX) NULL,
	[inheritanceflags] [nvarchar](MAX) NULL,
	[aceflag] [nvarchar](MAX) NULL,
	[acetype] [nvarchar](MAX) NULL,
	[accessmasks] [nvarchar](MAX) NULL
) ON [PRIMARY]

GO

Open in new window

0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869065
that's very helpful, even fixed and issue i had with my table.  My question is where you have
$query = "INSERT INTO $tableName (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks) VALUES ('$($_.servername)','$($_.fullpath)','$($_.type)','$($_.owner)','$($_.trustee)','$($_.inherited)','$($_.inheritanceflags)','$($_.aceflag)','$($_.acetype)','$($_.accessmasks)')"
                WriteSQL $query

Open in new window


How does it make that connection to the sql server?  Sorry if it seems like a dumb question..I am trying my best to learn as I go with this stuff.  Great info on here
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869073
oh wait i didn't see connectionString at first...sorry
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869077
The first line in the code is:
$connectionString = "server='localhost';database='testDatabase';trusted_connection=true;"
$tableName = "FilePermissions"

Open in new window


That connection string tells it where to connect.  In the function:

function WriteSQL ($query)
{
    if ($debug -eq $true) {Write-Host $query}
    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = $connectionString
    $Connection.Open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $Connection
    $command.CommandText = $query
    $command.ExecuteReader()
    $connection.Close()
}

Open in new window


The line $Connection.ConnectionString tells the script where to write the data.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869123
ok when running i get :

Exception calling "Open" with "0" argument(s): "Cannot open database "Audit" requested by the login. The login failed.
Login failed for user 'ENTERPRISE-1\admin.myuser'."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:12 char:5
+     $Connection.Open()
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
connection's current state is closed."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:16 char:5
+     $command.ExecuteReader()
+     ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869130
Sounds like you don't have permission to the database-- go to 'Security\Users' and and add your user in to the database permissions.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869138
my user is listed under dbo
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869168
Is your user (ENTERPRISE-1\admin.myuser) mapped to the database?

The error states:
Exception calling "Open" with "0" argument(s): "Cannot open database "Audit" requested by the login. The login failed.

Which is why the connection can't open.  Is your login in the database Security container or the Instance Security container?
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869182
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869184
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869209
What happens if you run the script from an admin powershell window?
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869211
thats how i'm running it, also firewall on sql turned off
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41869250
Hmm...  Could you edit the connection string to use a SQL login like SA or a custom one to test?
Server=[SERVER];Database=[DATABASE];Uid=[USER];Pwd=[PASSWORD] 

Open in new window

0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869643
ill try that this morning and report back
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869706
ok i adjusted the string, i also added the instance because just putting servername was still giving me log in error  so now i have :

$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"

Open in new window



now the error i get is:

Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near 's'.
Unclosed quotation mark after the character string ')'."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:16 char:5
+     $command.ExecuteReader()
+     ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41870020
after playing around i got it to connect. I had to add the instance to the servername...thanks for the assist!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now