CODEWITHSUNDEEP
sql-serverpowershellsqlps
Brute
Brute

Reputation: 141

PowerShell failure when connecting to SQL Server

I am just trying to establish a connection with the SQL Server.

The situation is that PowerShell is running on my machine and there is no instance of the sever on my machine. This SQL Server is on another server, Server9. If I have to look at the tables I use SQL Server Management Studio to look at the data and run some queries.

The authentication is set to SQL Server and Windows authentication mode. I checked the server properties under Connections the option of Allow remote connections to this server is ticked. I am guessing the server is set up for remote connection.

Additional info: I downloaded the SQL Server and SQLPS modules. Running PowerShell 5.1 on PowerShell ISE

Connection code is:

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=MSSQLSERVER;database=Server9;Integrated Security=true;Initial Catalog=master;Trusted_Connection=True”
$sqlConn.Open()

Error message:

Exception calling "Open" with "0" 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)"
At line:3 char:1

  • $sqlConn.Open()
  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : SqlException

Upvotes: 0

Views: 4103

Answers (1)

Michael
Michael

Reputation: 123

From what I can tell, you may have your connection string wrong. If the database server is Server9 and the database is MSSQLSERVER, then the connection string should be

Server=Server9;database=MSSQLSERVER;Integrated Security=true;Initial Catalog=master;Trusted_Connection=True

I would also recommend using the SqlServer module that you mentioned, and executing your query with Invoke-SqlCmd instead (ref). Something like

Invoke-Sqlcmd -ServerInstance Server9 -Database MSSQLSERVER 
              -Credential (Get-Credential) -Query "<query>"

could work

Upvotes: 1

Related Questions