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


# 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")

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 .





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")

# I now have a valid connection!