0

I am trying to connect the SQL server through Powershell. 

$Server = 'RDS End Point,PortNO'
$Database = 'DBName'
$uid ='UserID'
$pwd = 'Password'

$connstring = 'Server=$Server; Database=$Database; '
$connstring += 'User ID=$uid; Password=$pwd;'

$connection = New-Object System.Data.SqlClient.SqlConnection($connstring)
$connection.Open()

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:1 char:1
+ $connection.Open()
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

What's wrong?

vonPryz
  • 22,996
  • 7
  • 54
  • 65
Rohit Singh
  • 47
  • 2
  • 10

1 Answers1

1

Only double-quoted strings do interpolation in PowerShell. This

$connstring = 'Server=$Server; Database=$Database; '
$connstring += 'User ID=$uid; Password=$pwd;'

should be

$connstring = "Server=$Server; Database=$Database;User ID=$uid; Password=$pwd;"
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • I changed the $connstring and got this error message : { Exception calling "Open" with "0" argument(s): "Cannot open database "userid" requested by the login. The login failed. Login failed for user 'userid'." At line:1 char:1 + $connection.Open() + ~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException } – Rohit Singh May 12 '20 at 13:56
  • Just to add I have different Port no open on SQL (RDS) and I am performing these steps on the same machine from where I can access the SQL DB from Microsoft SQL server management Studio. – Rohit Singh May 12 '20 at 14:04
  • 1
    Check your connection string. The database is wrong: "Cannot open database "userid" requested by the login. The login failed. Login failed for user 'userid'. – David Browne - Microsoft May 12 '20 at 14:16
  • 'UserID' is an example. I used the correct DB name in the connection string. Sorry for confusion – Rohit Singh May 12 '20 at 15:39
  • 1
    Well the error means that you have the wrong database or the user has no access to that database. – David Browne - Microsoft May 12 '20 at 16:57
  • I have Microsoft SQL management Studio and I am able to log in with the same credential. Does this string works with RDS end point as I have SQL rds in AWS and What if I have another port open for SQL – Rohit Singh May 12 '20 at 20:23
  • SSMS will initially connect to Master, unless you change the advanced settings of the connection dialog. – David Browne - Microsoft May 12 '20 at 20:44
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/213763/discussion-between-rohit-singh-and-david-browne-microsoft). – Rohit Singh May 13 '20 at 09:32