Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

User with DBO permission can't remotely connect

Posted on 2016-11-01
15
Medium Priority
?
71 Views
Last Modified: 2016-11-03
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
Comment
Question by:leadtheway
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
15 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41869727
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41869733
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41869735
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 1

Author Comment

by:leadtheway
ID: 41869912
$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869913
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41869964
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41869980
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
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41869988
Try $command.ExecuteNonQuery instead of ExecuteReader.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869990
looks like data did get into the table despite the invalid switch... I'll swap out commands
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41869992
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41870000
Remove the print command since you don't need it anymore.
0
 
LVL 1

Author Comment

by:leadtheway
ID: 41870006
getting a bunch of 1 with the occasional errors.  I assume that means its working now?
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41870011
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
 
LVL 1

Author Comment

by:leadtheway
ID: 41870019
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 41872206
Btw, if that's a real password you posted, please ask E-E to edit out the password for you(!).
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

609 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