Dec 17 2022 07:56 AM
Dec 17 2022 07:56 AM
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?
Dec 17 2022 08:23 AM - edited Dec 30 2022 03:02 PM
i don't have an answer for your issue but i would like to knwo what don't it means to .
Dec 18 2022 11:32 AM
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!