Security for Import-PSSession -Module SQLPS

Welcome Forums General PowerShell Q&A Security for Import-PSSession -Module SQLPS

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
3 months, 3 weeks ago.

  • Author
    Posts
  • #104863

    Participant
    Points: 0
    Rank: Member

    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?

    Thanks!

  • #104866

    Keymaster
    Points: 1,625
    Helping HandTeam Member
    Rank: Community Hero

    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.

  • #104870

    Participant
    Points: 0
    Rank: Member

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

    Thanks!

  • #104873

    Participant
    Points: 48
    Rank: Member

    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.

  • #104980

    Participant
    Points: 0
    Rank: Member

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

    Thanks!

The topic ‘Security for Import-PSSession -Module SQLPS’ is closed to new replies.