QuickBlog – PowerShell SMO using SQL Authentication…

Most of the time, I’ve been using Windows Authentication to do all my PowerShell SMO scripting on my Development and local machine .  Now, that I’m covering for our DBA, I have been given a SQL User-Id for monitoring jobs on our production servers.  So, I build some PowerShell scripts to use SMO with SQL Authentication so I can extract some information. 

  1. For those out there wondering, how-can-I-do this, then here’s code on “How to use SQL Authentication” in your script:
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
$SQLSvr = "YourSQLServerName"
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLSvr
$MySQL.ConnectionContext.LoginSecure = $false
$MySQL.ConnectionContext.set_Login("SqlUserID")
$SqlUPwd = ConvertTo-SecureString '$y0urPassword!' -AsPlainText -Force
$MySQL.ConnectionContext.set_SecurePassword($SqlUpwd)
$MySQL,Information
Result:
Keep in mind, “Windows Authentication” is the default.  To change from Windows to SQL Authentication, change the “$MySQL.ConnectionContext.LoginSecure” to be “$false”. 
 
Do you want to check how the connection string look like? Here’s the PowerShell line to display it:
 

$MySQL.ConnectionContext.ConnectionString
Result:
 

By the way, there’s two ways to load the “Microsoft.SqlServer.SMO” Assembly:

1. For PowerShell V1 and SQLPS.exe (SQL Server 2008 / 2008 R2 [November]):

## - PowerShell V1, Loading Assembly: 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

2. For PowerShell V2:

## - PowerShell V2, Loading Assembly:
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

I hope this helps…  Happy PowerShell!!

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s