PowerShell failure when connecting to SQL Server

Question

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

What machine is SQL Server on? Is it on a machine named MSSQLSERVER? If so can you ping it? Have you enabled TCP and Remote Connections? Is it a named instance or a default instance of SQL Server? If the latter is SQL Server Browser running? Have you made a firewall exception for it? — Charlieface
– Charlieface, Commented Mar 10, 2022 at 22:03
@Charlieface Sql is on Server9. I don't have access to it. The remote connection is enabled on the server, I checked under properties-> connections. Server9 is up and running, I checked using Test-Connection, no issues there. Complete server name is server9.server.nii. I used this name in the connection string too but it doesn't work. If there are any settings that I can check from MS SQL Sever Management please let me know. — Brute
– Brute, Commented Mar 10, 2022 at 22:29
Then where does MSSQLSERVER come in? Is it a named instance with that name? More likely it is a default instance, as default instances usually use that name. Therefore your connection string should probably be Server=server9.server.nii;Integrated Security=true;Initial Catalog=master;Trusted_Connection=True note that Initial Catalog and database are the same thing. If it is a named instance then you need to change it to Server=server9.server.nii\MSSQLSERVER... — Charlieface
– Charlieface, Commented Mar 10, 2022 at 22:34

marc_s · Accepted Answer · 2022-03-10 21:03:15Z

1

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

edited Mar 10, 2022 at 21:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 10, 2022 at 20:56

Michael

1235 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Brute Over a year ago

thanks for answering. After changing the database it gives the following error "Login failed for user ''. The user is not associated with a trusted SQL Server connection." The SQL server is set to SQL Server and Windows authentication

Charlieface Over a year ago

@Brute That means the connection string worked to connect to the server, but your Windows AD user is not authorized to login. You need to speak to whoever controls that instance to set permissions correctly

Brute Over a year ago

@Charlieface but I can login via SQL Server Management Studio from my machine.

Charlieface Over a year ago

@Brute Is Powershell running directly on the same machine from your user? Or are you running it via Task Scheduler or something? Is SSMS using the same credentials?

Brute Over a year ago

Yes, PowerShell is running on my machine(the one I logged into using Windows credentials). SSMS is using the same credentials.

|

Collectives™ on Stack Overflow

PowerShell failure when connecting to SQL Server

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related