by willsteele at 2012-08-17 08:39:35
I am a long time user of SSMS/TSQL, but, am looking to get into PowerShell for SQL Server. I know that I can start an instance of the PowerShell SQL minishell by right clicking a database in SSMS and clicking Start PowerShell. To try and set up some automated tasks (read that to mean scheduled tasks) I want to see if the same shell can be accessed via a script, and, if so, how. My first concern is how to pass credentials. I have located where the sqlps.exe is and I need to be sure and log on with an account that is in SQL. I presume the credentials are automatically passed from SQL Server via token to the minishell when you start from the context menu. In my case, I have to figure out how to ensure the same credentials are passed via script. Any links or code samples would be helpful. From a PowerShell perspective, I am a total SQL Server beginner.
by DonJ at 2012-08-17 09:55:10
So... you know the minishell is gone in SQL2012, right? And it isn't special except that it won't let you add other snap-ins. You can open a normal PowerShell console and add the SQL snap ins, and get the same functionality the minishell allows.
by willsteele at 2012-08-17 10:06:52
Ah, that's news to me. Thanks for bringing me up to speed. At this point, we are still on 2008, but, I suspect we will migrate relatively soon. Any idea why the decided to go with the SnapIn for 2012 as opposed to modules? I thought most everything was shifting to module-based extensions.
For anyone else curious about this, I get the following on my machine (PowerShell v3/SQL Server 2012) when I check:
Name : SqlServerCmdletSnapin100
PSVersion : 2.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
Name : SqlServerProviderSnapin100
PSVersion : 2.0
Description : SQL Server Provider
Once I do this I can start working with cmdlets:
Add-PSSnapin -Name SqlServerCmdletSnapin100
Guess I'll be playing around with my laptop this weekend!
by DonJ at 2012-08-17 10:16:48
So, in 2008 you've got a Snap-In, which can be loaded into any PowerShell session; the SQL minishell simply preloads it and then prohibits loading any others.
In 2012, you've got a module (two, I think), and a mainly provider-based model. So you get a SQLSERVER drive. But it's based as a module, and there's no minishell to preload it and exclude the loading of others (the minishell concept was well-intended but ultimately a bad idea).
So 2012 did repack the functionality into a module.
by willsteele at 2012-08-17 10:19:26
Got it. Thanks for filling in the blanks Don.
by cmille19 at 2012-08-19 08:44:14
One other point on SQL 2012. The sqlps host (sqlps.exe) provided which is in addition to the sqlps module no longer implements a shellid and so allow snappins and modules to be added. To use SQL authentication instead of Windows authentication as you requested you would need to use new-psdrive with cred parameter as described here:
http://blogs.msdn.com/b/buckwoody/archi ... ssion.aspx