0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brute
  • 121
  • 1
  • 10
  • 1
    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 Mar 10 '22 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 Mar 10 '22 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 Mar 10 '22 at 22:34

1 Answers1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Michael
  • 113
  • 4
  • 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` – Brute Mar 10 '22 at 22:17
  • 1
    @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 – Charlieface Mar 10 '22 at 22:37
  • @Charlieface but I can login via SQL Server Management Studio from my machine. – Brute Mar 10 '22 at 22:56
  • @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? – Charlieface Mar 10 '22 at 22:58
  • Yes, PowerShell is running on my machine(the one I logged into using Windows credentials). SSMS is using the same credentials. – Brute Mar 10 '22 at 23:22
  • @Brute - if you're still having an issue, can you try this connecting string instead? "data source=Server9\MSSQLSERVER;Initial Catalog=master;Integrated Security=SSPI"? One followup question I thought of is whether your server has multiple database instances running on it or just the default? If multiple, you'd need to identify the right instance along with the server name. – Michael Mar 14 '22 at 13:24
  • @Michael Thanks for checking up on this question. I got it connected...somehow – Brute Mar 14 '22 at 20:06