How to troubleshoot "Read-SqlTableData : Failed to connect to server" error?

CharlieLor 551 Reputation points
2024-03-22T15:58:51.97+00:00

I have this PoweShell script code:

$sqlView = Read-SqlTableData -ServerInstance $query.Instance -DatabaseName $query.DB -SchemaName $query.Schema -TableName $query.View

I keep getting this error:

Read-SqlTableData : Failed to connect to server DB1.MYDOMAIN.COM. At E:\scripts\EXPORT-DATA.ps1:59 char:24

  • ... $sqlView = Read-SqlTableData -ServerInstance $query.Instance -Databa ...
  •             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ObjectNotFound: (DB1.MYDOMAIN.COM:String) [Read-SqlTableData], ConnectionFailureException
    • FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.ReadSqlTableData

The Windows service account NT AUTHORITY\NETWORK SERVICE do have Sysadmin permission in the DB1 sql server.

User's image

I'm able to run this script as a domain server admin but when I run this PowerShell script as Network Service, it always failed with that error above. It seems to be an issue with this service account but I'm not sure where else to check further since it's already set to sysadmin. Any help is much appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,718 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,364 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,059 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,816 Reputation points
    2024-03-22T19:00:33.36+00:00

    CategoryInfo : ObjectNotFound: (DB1.MYDOMAIN.COM:String)

    Is "DB1.MYDOMAIN.COM" the one you used for the server name in the connection string?

    Such a web URL can't work for a SQL Server name, you have to use the right SQL Server name/IP address for the connection. And ensure none Firewall is blocking the access.

  2. CharlieLor 551 Reputation points
    2024-03-28T14:35:34.31+00:00

    So it seems that SQL Server does not trust the cert and a new sub-root cert was reissued. The cert that the sql server used was expired and we had to issue a new cert. We didn't realized that we have to issue a new sub-root cert as well. I didn't check if that subroot was also expired too or not but with a new subroot cert, it works again.

    0 comments