I want to connect to my SQL server to import the SQLPS module. My problem is security to the SQL server. Domain users will be running the script but they don’t have access to the server itself so the New-PSSession cmdlet blows up with ‘access denied’. To test it and make sure the script worked I created an active directory user with domain admin access and do this:
$Password = ConvertTo-SecureString “abcd” -AsPlainText -Force
$Credentials = New-Object System.Management.Automation.PSCredential (“domainname\aduser”, $Password )
$sql1 = New-PSSession -ComputerName sql1 -Credential $Credentials
Import-PSSession -Session sql1 -Module SQLPS -Prefix sql1 -AllowClobber
Works great, but a million miles from good security and best practice.
What’s the proper way to handle this?
JEA. It’s designed expressly for this. You set up an endpoint that (a) only contains the command(s) you want people to run, which might even be proxy commands or a custom command, (b) only allows designated users in, and (c) runs under alternate credentials. Set that up on a server, people can remote into it and run the command(s) you permit.
I’m a Powershell beginner/maybe novice a little. Never heard of JEA but just googled it and see lots of info.
Also worth noting SQLPS is deprecated and the replacement is the SQLServer module available in the PSGallery. If the users have rights to the SQL instance and databases they can execute T-SQL against the server/instance with the cmdlets on their workstations without needing admin rights on the server itself which is required for PSRemoting so no need to connect via WinRM and import the cmdlets from the PSSession.
To install the module.
The SQLServer module is exactly what I need since it verifies against the SQL server instance, not the server itself. Works great.