Code-Snippet: SQLPS – SQL Server PowerShell User Authentications…

Using SQLPS or if you have manage to include the SQL Server 2008 PowerShell snapins and provider to load from your user profile.  The following PowerShell code snippet will get you connected to you local SQL Server using your trusted integrated Windows Authentication:

$SQLname = "ServerName"
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLname


Now, you are probably wondering, how can I use SQL Authentication to connect to my database?  And, here’s how:

$SQLname = "ServerName"
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLname

# -
# - To change from Windows Trusted Connection and use SQL Server Security.
# -
$MySQL.ConnectionContext.LoginSecure = $false
$MySQL.ConnectionContext.set_Login('SQLUserID')
$SqlUPwd = ConvertTo-SecureString 'SQLPassword' -AsPlainText -Force
$MySQL.ConnectionContext.set_SecurePassword($SqlUpwd)

So, as you can see, the first two lines didn’t change. This is because the first two lines may expect to use Windows authentication but the following lines are the ones that change the security context to use SQL Authentication.  if there’s no errors then you can start querying your SQL engine for information.

That’s it!

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