PowerShell.com
Library » Script Library » SQL Server » Connect-MSSQL-IPSQLAuth

Connect-MSSQL-IPSQLAuth

Previous | Next | View all files | View Slideshow
posted by Richard Giles
10-06-2008
Downloads: 480
File size: 2.8kB
Views: 1,694
Embed
Connect-MSSQL-IPSQLAuth
  1. ## ===================================================================== 
  2. ## Title       : Connect-MSSQL-IPSQLAuth 
  3. ## Description : Connect to $ServerName using SQL Server authentication. 
  4. ##               This connection is not encrypted.  
  5. ##               User ID and Password are transmitted in plain text. 
  6. ## Author      : Idera 
  7. ## Date        : 6/27/2008 
  8. ## Input       : -ipAddress < xxx.xxx.xxx.xxx | xxx.xxx.xxx.xxx\instance > 
  9. ##               -verbose  
  10. ##               -debug     
  11. ## Output      : Database names and owners 
  12. ## Usage            : PS> . Connect-MSSQL-IPSQLAuth -ipAddress 127.0.0.1 -v -d 
  13. ## Notes         :  
  14. ##    Tag            : MSSQL, connect, IP, SQL Authentication 
  15. ## Change Log  : 
  16. ## ===================================================================== 
  17.  
  18. param 
  19. ( 
  20.       [string]$ipAddress = "127.0.0.1", 
  21.     [switch]$verbose, 
  22.     [switch]$debug 
  23. ) 
  24.  
  25. function main() 
  26. { 
  27.     if ($verbose) {$VerbosePreference = "Continue"} 
  28.     if ($debug) {$DebugPreference = "Continue"} 
  29.     Connect-MSSQL-IPSQLAuth $ipAddress 
  30. } 
  31.  
  32. function Connect-MSSQL-IPSQLAuth($ipAddress) 
  33. { 
  34.     trap [Exception]  
  35.     { 
  36.         write-error $("TRAPPED: " + $_.Exception.Message); 
  37.         continue; 
  38.     } 
  39.      
  40.     # Load SMO assemblies 
  41.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
  42.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") 
  43.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") 
  44.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") 
  45.      
  46.     # Instantiate a server object 
  47.     $smoServer = New-Object -typename Microsoft.SqlServer.Management.Smo.Server ` 
  48.         -argumentlist "$ipAddress" 
  49.      
  50.     # The connection will use SQL Authentication, so set LoginSecure to FALSE 
  51.     $smoServer.ConnectionContext.set_LoginSecure($FALSE) 
  52.      
  53.     # Pop a credentials box to get User Name and Password 
  54.     $LoginCredentials = Get-Credential 
  55.      
  56.     # If the user does not specify a domain, UserName will begin with a slash. 
  57.     # Remove leading slash from UserName 
  58.     $Login = $LoginCredentials.UserName -replace("\\","") 
  59.      
  60.     # Set properties of ConnectionContext 
  61.     $smoServer.ConnectionContext.set_EncryptConnection($FALSE) 
  62.     $smoServer.ConnectionContext.set_Login($Login) 
  63.     $smoServer.ConnectionContext.set_SecurePassword($LoginCredentials.Password) 
  64.      
  65.     # The connection is established the first time you access the server's properties. 
  66.     cls 
  67.     Write-Host Your connection string contains these values: 
  68.     Write-Host 
  69.     Write-Host $smoServer.ConnectionContext.ConnectionString.Split(";") 
  70.     Write-Host 
  71.      
  72.     # List info about databases on the instance. 
  73.     Write-Host "Databases on $ipAddress " 
  74.     Write-Host 
  75.     foreach ($Database in $smoServer.Databases)  
  76.     { 
  77.         write-host "Database Name : " $Database.Name 
  78.         write-host "Owner         : "    $Database.Owner 
  79.         write-host 
  80.     } 
  81. } 
  82.  
  83. main 

Connect to a SQL Server using SQL Server authentication. This connection is not encrypted. User ID and Password are transmitted in plain text.

Concentrated Tech NSoftware Dell Compellent Sponsored by Idera and Concentrated Tech and NSoftware and Dell Compellent
Copyright 2011 PowerShell.com. All rights reserved.