Security is an important aspect of SQL Server management. It’s important to control permissions within the instance and only allow the minimal level of rights to our users. This is the principle of least privilege, a fundamental concept for computer security. SQL Server provides a variety of tools to manage these permissions within the instance, but what if SQL Server has to interact with either the operating system or another application? For this, we have credentials, a way to manage authentication to other resources.
The next series of cmdlets to review from SQLPS are the ones used to manage these credentials. We’ll go through the usual process and use Get-Command to list the options:
Get-Command -Module SQLPS *SqlCredential
The result is four cmdlets for creating, retrieving, altering, and removing a credential. We’ll start by using New-Credential to create one for a windows domain login:
$pw = Read-Host 'Enter the password' -AsSecureString New-SqlCredential -Path 'SQLSERVER:\SQL\PICARD\Default\' -Name SQLTest_Windows -Identity 'SDF\SQLTest' -Secret $pw
This command will create the credential in the PICARD instance. Unlike some of the other cmdlets, though, the -SqlCredential cmdlets do not support a -ServerInstance parameter. The way to specify the instance is to use the provider path to the instance. It also, unfortunately, doesn’t accept a Powershell credential object to create the SQL credential. It will accept a secure string for the password, meaning that the password will be encrypted as the cmdlet processes it.
Now that we’ve created our credential, let’s crack it open with Get-Member and see what kind of object we’re working with:
Get-SqlCredential -Path 'SQLSERVER:\SQL\PICARD\Default\' -Name SQLTest_Windows | Get-Member
It’s an Smo.Credential object, which matches with the other object types found within the provider.
The Set-SqlCredential gives us the capability to update the identity or the password for an existing credential. This means if something changes externally with credential information, we can update the credential to reflect that. Remove-SqlCredential rounds out the functionality by giving us a cmdlet to remove a credential using Powershell. These two cmdlets will also support the -Script switch, allowing you to output the resulting T-SQL for these calls.
Why would you use these? The functionality they provide does not really offer any real savings over doing the same work in T-SQL. Conversely, it is about the same amount of code. If you use Powershell for deploying these objects, the secure string functionality can be helpful to keep your credentials encrypted. The choice between T-SQL and Powershell is a matter of having different options to create your credentials, letting you choose the appropriate tool for your deployment.