Solved

User with DBO permission can't remotely connect

Posted on 2016-11-01
15
48 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
  • 8
  • 6
15 Comments
 
LVL 47

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 47

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 47

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 47

Accepted Solution

by:
Vitor Montalvão earned 500 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 47

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 47

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 69

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 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

11 Experts available now in Live!

Get 1:1 Help Now