January 2010 - Posts

SQLBits sponsors DDD 8 coaches
13 January 10 07:17 AM | MartinBell | with no comments

We are proud to announce that once again SQLBits will be sponsoring the coaches at DDD to take delegates from Reading Station to TVP.

We have two coaches leaving Reading Station for Thames Valley Park between 08:00 and 08:45  The coaches will leave earlier if they are full. Similarly there will be two coaches to take delegates back to the station after the event.

Until 15 Jan 2010 00:00 you can vote for session to be included in DDD at http://developerdeveloperdeveloper.com/ddd8/Users/VoteForSessions.aspx

Filed under: ,
Powershell and SQL Authentication
01 January 10 03:25 PM | MartinBell | 3 comment(s)

In my last posting I mentioned that not everyone could use the SQL Server Provider when using Powershell. Why should this be so? To use the SQL Server Provider requires Window Authentication and uses the account running the Powershell session (see the topic “Using the SQL Server PowerShell Provider” in books online). Unfortunately Windows Authentication will not always possible, in these circumstances how useful is Powershell?

Although the SQL Server provider makes it easy to navigate around a SQL Server instance, if you have already written SMO applications using another language you will have done so without the SQL Server Provider. For instance, I gave the example of scripting views in my post Powershell and SMO scripting – Part 1:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views foreach ($Item in Get-ChildItem) {
$out = "C:\PowerShell\" + $Item.Name + ".sql"
$Item.Script() | Out-File -Filepath $out
}

This could have been written without the SQL Server provider as:

# ===================================
# Windows Authentication
# ===================================
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-Null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST" 
$s.ConnectionContext.LoginSecure=$true
$dbs=$s.Databases
foreach ($Item in $dbs["Adventureworks"].Views)
{
    $out = "C:\Powershell\" + $Item.name + ".sql"
    $Item.Script() | out-File $out
}


This still uses Windows Authentication, but you can set the username and password by changing the servercontext so that SQL Authentication is used:

# ===================================
# Hard Coded SQL Authentication
# ===================================
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-Null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST" 
$s.ConnectionContext.LoginSecure = $false
$s.ConnectionContext.Login="sa"
$s.ConnectionContext.Password="saPassword"
$dbs=$s.Databases
foreach ($Item in $dbs["Adventureworks"].Views)
{
    $out = "C:\Powershell\" + $Item.name + ".sql"
    $Item.Script() | out-File $out
}


If you wish to prompt for the login and password you can use the Powershell Get-Credentials cmdlet to provide a dialog to input the username and password.

# ==================================
# SQL Authentication Dialog
# ===================================
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-Null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST" 
$s.ConnectionContext.LoginSecure = $false
$credential = get-Credential
$userName = $credential.UserName.Remove(0,1)
$s.ConnectionContext.Login=$userName
$s.ConnectionContext.set_SecurePassword($credential.Password)
$dbs=$s.Databases
foreach ($Item in $dbs["Adventureworks"].Views)
{
    $out = "C:\Powershell\" + $Item.name + ".sql"
    $Item.Script() | out-File $out
}


The Get-Credentials cmdlet returns the username with a leading backslash (see http://technet.microsoft.com/en-us/library/dd315327.aspx
), therefore this should be removed before it can be used. The password is returned as a SecureString. If the Snapin SqlServerCmdletSnapin100 has been added you can still use the Invoke-SqlCmd cmdlet. The cmdlet has parameters that will allow you to specify an SQL Server Instance (-ServerInstance), Database (-Database), Username (-Username) and Password (-Password) which can be shortened to –Ser, –Da, –U and –P e.g.

Add-PSSnapin SqlServerCmdletSnapin100
invoke-sqlcmd "SELECT * FROM sys.databases" -ServerInstance "LOCALHOST" -Database Master -Username sa -Password saPassword
invoke-sqlcmd "SELECT * FROM sys.databases" -Ser "LOCALHOST" -Da Master -U sa -P saPassword

In the post Starting/Stopping SQL Server using Powershell I showed that using the SQL Server Provider is not always necessarily when using WMI in Powershell, and in this post I have shown that it is not mandatory when using SMO or with the Invoke-Sqlcmd cmdlet, but if you need to use your scripts in an environment where Windows Authentication is not available you should design your scripts so they don’t use the SQL Server Provider.


 

Filed under: , ,

This Blog

SQL Blogs

Syndication