Author Posts

July 20, 2018 at 9:54 pm

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?


July 20, 2018 at 9:59 pm

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.

July 20, 2018 at 10:10 pm

I'm a Powershell beginner/maybe novice a little. Never heard of JEA but just googled it and see lots of info.


July 21, 2018 at 4:20 am

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.

Install-Module SQLServer

To install the module.

July 23, 2018 at 5:25 pm

The SQLServer module is exactly what I need since it verifies against the SQL server instance, not the server itself. Works great.