Connect Using Alternate Credentials

Mar 31, 2009 at 2:45 AM
Edited Mar 31, 2009 at 2:48 AM
Great job thus far on this project!  I had one suggestion that I think might make this a bit more flexible.  Currently it seems that it is assumed you have the proper credentials using Integrated Security.  Maybe add a parameter to accept a ServerConnection object or username/password when making connections to an instance of SQL?  This might prove to be alot of work since you would need to add it to many of the functions you have provided but as it stands right now I can't connect to a server instance using SQL credentials unless I am missing something.

I can help with this addition if needed.

Mar 31, 2009 at 3:02 AM
Good point, I had someone else ask for the same feature a few months ago and provided the following modification to the Get-SqlServer function via email. I guess I'm just so used to using Windows authentication, I forget not everyone has the luxury. I'll modify the Get-SqlServer function and dependent functions in a future release. The parameters could be cleaned up a a little (use [switch] instead of bool.

function Get-SqlServer
param([string]$sqlserver=$(throw 'Get-SqlServer:`$sqlserver is required.'),$LoginSecure=$true,[string]$username,[string]$password)
#When $sqlserver passed in from the SMO Name property, brackets
#are automatically inserted which then need to be removed
$sqlserver = $sqlserver -replace "\[|\]"

Write-Verbose "Get-SqlServer $sqlserver"

if ($LoginSecure -eq $true)
{ $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver }
{ $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver,$username,$password }

$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $con
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject")
#trap { "Check $SqlServer Name"; continue} $server.ConnectionContext.Connect()
return $server

} #Get-SqlServer

#To use Windows auth:
#Get-SqlServer 'Z002\SqlExpress'
#To use SQL auth:
#Get-SqlServer 'Z002\SqlExpress' $false 'sa' 'password'

Mar 31, 2009 at 3:13 AM
Perfect!  I guess I will customize the version we have until you implement this in the main branch.  Advanced functions should make this much cleaner with Parameter Sets.

Mar 31, 2009 at 11:23 AM
If you make any progress implementing SQL authenication, please upload a patch using the source code area and I'll take a look. I'm planning on doing one more Powershell V1 release within next 30 to 60 days, before starting work re-implementing functions in Powershell V2.
Mar 31, 2009 at 9:55 PM
I'll try and get it in before the 30 day mark.  Got most of the LibrarySmo done today and started work on the LibraryRmo.