Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Question

Here's the main part of the PowerShell script.

$databases = @("devDB.serverName.com/devTest")

foreach($database in $databases) {

    #Query to find any failed login attempts yesterday
    $query = "SELECT * FROM error_log WHERE Text LIKE 'Login failed%'"
    #Standard connection string object
    $connectionString = New-Object System.Data.Sqlclient.Sqlconnection("Data Source="+$database+";Initial Catalog=ErrorReports;Integrated Security=True;")
    #Standard adapter object
    $adapter = New-Object System.Data.Sqlclient.Sqldataadapter($query, $connectionString)
    #Standard Datatable object
    $dataTable = New-Object System.Data.Datatable
    #Try to execute the query and store it in a datatable
    $adapter.Fill($dataTable) | Out-Null

}

And here's the error I kept getting. The PowerShell script is run on the same machine that the sql server instances are installed. The user who logged in to run the PowerShell script has local administrator rights and SQL Server Sysadmin permission.

There was a problem connecting to and/or querying devDB.serverName.com/devTest for the failed SQL Server login attempts. Specific error:
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

Answer 1

That is a generic "cannot connect to server" error message and does not help diagnose the actual issue in any way.

I suggest you start here:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15

Answer 2

Did you try tilting the forward slash to be a backward slash?

Answer 3

Thanks! The backslash makes a different. Now I got this error.

There was a problem connecting to and/or querying devDB.serverName.com\devTest for the failed SQL Server login attempts. Specific error:
Exception calling "Fill" with "1" argument(s): "Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication."

My confusion is, the script is running locally with my user account which has sysadmin permission in the sql server. I wonder if line 8 of my code is correct.

Answer 4

I tried to follow this tutorial and yet I still have the same error. Here's the code.

    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = “Server="+$database+";Integrated Security=true;Initial Catalog=ErrorReports”
    $sqlConn.Open()

    #create command
    $sqlcmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlcmd.Connection = $sqlConn
    $query = "SELECT * FROM current_error_log WHERE Text LIKE 'Login failed%'"
    $sqlcmd.CommandText = $query

    #Standard adapter object
    $adapter = New-Object System.Data.Sqlclient.Sqldataadapter($sqlcmd)

    #Standard Datatable object
    $dataTable = New-Object System.Data.Datatable

Answer 5

There is no error with your code as such, but apparently there is an issue in your environment that prevents Windows authentication from working. I would expect that you would get the same error if you tried to log on this instance from SSMS on the same machine.

Exactly what the issue is, I don't know, since I have zero knowledge about your environment. But some possible causes are:

• You are in a workgroup; Windows auth in a workgroup does not work well.
• The SQL Server instance is another domain than you are, and there is no trust between the domains.
• Your machine is not in a domain at all, but the SQL Server machine is.

The fact that you are sysadmin on the server is irrelevant, since you cannot be authenticated at all.