Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

User with DBO permission can't remotely connect

have brand new 2012 express and am using a script to out put data to a table, but when the script runs i get an access error, but I'm listed as a dbo under the database.. not sure what could be preventing

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.user'."
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
leadtheway
Asked:
leadtheway
  • 8
  • 6
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
but I'm listed as a dbo under the database
(...)Login failed for user 'ENTERPRISE-1\admin.user'."
Being a database user doesn't really means that you can login to a SQL Server instance. For that you'll need to have the database user mapped to a SQL Server login (they are different objects).
Can you connect to the database with SSMS?
0
 
leadthewayAuthor Commented:
yes, i am using the same account i installed sqlexpress with...i think i found the issue though, i needed to specific the instance
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's the other thing I wanted to ask after knowing that you can connect through SSMS: if you can post the connection string that you're using.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
leadthewayAuthor Commented:
$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"
0
 
leadthewayAuthor Commented:
heres the full script
$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$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 = "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='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


I think its authenticating but i'm getting different error now:

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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, the error is different now.
Try to print $query before the $command.ExecuteReader() line.
Btw, if you have an INSERT shouldn't be executed by ExecuteNonQuery instead of ExecuteReader command?
0
 
leadthewayAuthor Commented:
hmm ok so now i added the print $query

 print $query
    $command.ExecuteReader()
    $connection.Close()

Open in new window



now i don't get a red error, i now get invalid switch for Insert...

Invalid switch - "INSERT INTO FilePermissions (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag
,acetype,accessmasks) VALUES ('FILE01','E:\Share\Accounting','D','ENTERPRISE-1\ACCOUNTING','CREATOR OWNER','False','ContainerInherit, ObjectInherit','InheritOnly','Allow','FullControl')"
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try $command.ExecuteNonQuery instead of ExecuteReader.
0
 
leadthewayAuthor Commented:
looks like data did get into the table despite the invalid switch... I'll swap out commands
0
 
leadthewayAuthor Commented:
Invalid switch - "INSERT INTO FilePermissions (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,ace
type,accessmasks) VALUES ('domain-FILE01','E:\Share\Accounting\2016 SOC 1 Audit','D','ENTERPRISE-1\user','ENTERPRISE-1\user','True','None','None','Allow','FullControl')"
1
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Remove the print command since you don't need it anymore.
0
 
leadthewayAuthor Commented:
getting a bunch of 1 with the occasional errors.  I assume that means its working now?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not sure but at least main issue disappear.
I'm also a SQL Server guy so you might open a new question in development area to get better expertise answers.
0
 
leadthewayAuthor Commented:
yeah i ran a query against the table and the rows are increasing and if i look at the top 1000, theres data that i expect there.  Now to export this to a webpage somehow...thanks for the assist
0
 
Scott PletcherSenior DBACommented:
Btw, if that's a real password you posted, please ask E-E to edit out the password for you(!).
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now