Read-SqlTableData : Failed to connect to server

Question

Good morning.

Greetings.

I am making a connection to a remote SQL server on the local network via the following commands:

$user = "domain\sql"
$pass = "password"
$credential = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $user, (ConvertTo-SecureString $pass -AsPlainText -Force)

Read-SqlTableData -ServerInstance "Remote\MSSQLSERVER" -DatabaseName "database" -SchemaName "dbo" -TableName "table" -Credential $credential

But it throws me the following error:

Read-SqlTableData : Failed to connect to server Remote\MSSQLSERVER.
At line:14 char:1
+ Read-SqlTableData -ServerInstance $serverInstance -DatabaseName $data ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (E-DEASSQL01:String) [Read-SqlTableData], ConnectionFailureException
    + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.ReadSqlTableData

I have not been able to find the way to be able to connect to the Microsoft SQL Server server.

The server versions are:

SQL Server 2019

Windows Server 2019 Standard

I have the Firewall turned off. It generates the same error inside the server as remotely.

I hope you can help me.

Answer 1

Hello @Jairo Javier Baleta Cali !

I understand you are having issues trying to connect to an SQL Server on premises

I can provide some steps for you to test

First , verify the Instance name . If the instance is the default, the server instance should just be the server's hostname or IP address. The MSSQLSERVER is implied and not necessary. If it is a named instance, it should be hostname\instancename. So if you're trying to connect to the default instance on a server named "Remote", you should set -ServerInstance "Remote". Also, check that the SQL Server Browser service is running if you are using a named instance.

You can try connecting to the SQL Server using SQL Server Management Studio (SSMS) from the machine where you're running the script. This can help determine whether the problem is with your PowerShell script or with the SQL Server itself.By default, SQL Server uses Windows authentication. If you're trying to authenticate with a username and password, make sure that SQL Server is set to use mixed mode authentication and that the user you're trying to connect with exists and has the necessary permissions.

Also verify that you have Dns resolution and TCP IP connectivity if Management Studio is too much to install.

Test-NetConnection -ComputerName SQLServerHost -Port 1433

Let us know how it goes !

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 2

Thank you very much Konstantinos Passadis for the answers.

I have done several tests with the IP and the name of the server only inside the SQL server and outside of it, for example:

$user = "domain\sql"
$pass = "password"
$credential = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $user, (ConvertTo-SecureString $pass -AsPlainText -Force)

Read-SqlTableData -ServerInstance "Remote" -DatabaseName "database" -SchemaName "dbo" -TableName "table" -Credential $credential

But the same error:

Read-SqlTableData : Failed to connect to server Remote.
At line:14 char:1
+ Read-SqlTableData -ServerInstance "Remote" -DatabaseName "database" ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~
     + CategoryInfo : ObjectNotFound: (E-DEASSQL01:String) [Read-SqlTableData], ConnectionFailureException
     + FullyQualifiedErrorId : ConnectionToServerFailed,Microsoft.SqlServer.Management.PowerShell.ReadSqlTableData

On the same SQL Server I have SQL Server Management Studio installed and it connects without problem.

The server has mixed mode authentication and the user is an administrator of the SQL server:

Test-NetConnection -ComputerName Remote -Port 1433:

ComputerName     : Remote
RemoteAddress    : fe80::a152:16bf:936:20b0%8
RemotePort       : 1433
InterfaceAlias   : Interno
SourceAddress    : fe80::a152:16bf:936:20b0%8
TcpTestSucceeded : True

If I run the command without the credentials if it works. Although only locally on the SQL server.:

Read-SqlTableData -ServerInstance "Remote" -DatabaseName "database" -SchemaName "dbo" -TableName "table"

Does not accept Windows user credentials.

Answer 3

Hello @Jairo Javier Baleta Cali !

Can you check the SQL for Remote Connections

Ensure that the SQL Server is set to allow remote connections. You can check this in SQL Server Management Studio by right-clicking on the server, selecting "Properties", and going to the "Connections" page. Also, check that the SQL Server Browser service is running if you are using a named instance.

To configure the Microsoft SQL Server database for remote access:

1.Launch SQL Server Management Studio.

2.In Object Explorer, right-click on your server and select Properties.

3.Click Connections.

4.Under Remote server connections, select Allow remote connections to this server.

5.Click OK to save the changes. Changing this setting does not require restarting the server.

6.Ensure TCP/IP protocols are enabled and your firewall is set to allow port 1433. For information on enabling TCP/IP, see Setting Up Microsoft SQL Server.

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 4

Hello @Jairo Javier Baleta Cali !

Can you read this thread :

https://social.msdn.microsoft.com/Forums/en-US/1aa6fa4f-4fb3-48c6-b0a4-cc279090f0be/powershell-readwritesqltabledata-fails-connecting-to-azure-sql-database?forum=ssdsgetstarted

Specific :

Use the following format:

PS C:\> $sqlcc = new-object ('System.Data.SqlClient.SqlConnection') "Data Source='<use server name>.database.windows.net';Persist Security Info=True;User ID='<user name here>';Password='<password here>'"
PS C:\> $sc = new-object ('Microsoft.SqlServer.Management.Common.ServerConnection') $sqlcc
PS C:\> $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sc
PS C:\> $db = $srv.Databases["database_name"]
PS C:\> $table = $db.Tables["table_name"]
PS C:\> Read-SqlTableData -TopN 10 -InputObject $table

Don't forget to replace these to your real values: 
<use server name>
<user name here>
<password here>
database_name
table_name

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 5

Hello @Jairo Javier Baleta Cali !

I have seen similar problems on our SQL

So try to Enable Firewall and add an Inbound Rule for 1433 from your LAN or pC

I guess you know how to do that !

Please let me know how it went

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards