New-Object : Cannot find type [System.Data.SqlClient.SqlSqlConnection]:

Regular Contributor

 

Firstly, not sure if this is the correct forum so suggest the alternative, if it isn't 

 

Anyway,  I am working with a client and I am seeing a strange issue that suggests a SQL user account does not have the correct permissions to execute a stored procedure on a on-prem SQL Server 2019 server.  When I look on SSMS then it all looks ok.  

 

So on a SQLServer 2019 VM on the network I tried to run the following commands in  PowerShell 7.3 session

[Reflection.Assembly]::LoadWithPartialName('System.Data.SQlClient')

# this gets me 22.0.30-preview ( note couldn't seem to install this in Windows PowerShell 5.1, so I switched sessions to 7.3)
Install-Module -Name SqlServer -AllowPrerelease -Repository PSGallery -Force


$Server = "myserver.mydomainlocal"
$Database = "MyDatabase"
$User =  "domain\myaccount"  
$Password = "12345678"
$SqlSqlConn = New-Object System.Data.SqlClient.SqlSqlConnection("Server = $Server; Database = $Database; Integrated Security = False; User=$User; Password=$Password")
$SqlConn.Open()



New-Object : Cannot find type [System.Data.SqlClient.SqlSqlConnection]: verify that the assembly containing this type is loaded.
At line:5 char:15
+ ... qlSqlConn = New-Object System.Data.SqlClient.SqlSqlConnection("Server ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
 
You cannot call a method on a null-valued expression.
At line:6 char:1
+ $SqlConn.Open()
+ ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

 

Looks like I am missing a module or assembly.  Does anyone know what this is?

 

 

 

 

2 Replies

i don't have an answer for your issue but i would like to knwo what don't it means to .

 

SHAREit

@lounayong 

 

Not sure how I managed to mess up the syntax but once I realised I had a few typos to correct, all was good.

 

# switched to Windows PowerShell 5.1 and changed to integrated security, to just test the syntax

$SqlConn = New-Object System.Data.SqlClient.SqlConnection("Server = $Server; Database = $Database; Integrated Security=True")
$SqlConn.Open()

# I now have a valid connection!