More on SQLPS and SQL Server on Linux

In tonight’s PowerScripting Podcast, I shared a few things about SQL Server on Linux, and talked a bit about SQLPS. All after a lengthy intro about my tech life and Cajun culture ;) You can watch the whole thing, skip to 28 minutes to the podcast intro, or skip to around 59 minutes for the SQL/Linux/SQLPS portion.

Or you can just read below. This information came from a member within the SQL Server team and has been shared with permission.

Microsoft says…

  • Current state of SMO support: SMO running on a Windows computer can be used to connect to and work with SQL Server running anywhere (including on Linux).
  • Users can use SQL Server Management Studio (and Visual Studio/SQL Server Data tools) running on Windows to connect to and work with SQL Server running anywhere (including on Linux).
  • Users can use third party tools (e.g. Eclipse, MyLittleAdmin, sqlsh, sql-cli, etc.) that use our connectivity drivers (e.g. JDBC, PHP, ODBC, Node.js, ADO.NET, etc.) to connect to and work with SQL Server running anywhere (including on Linux).
  • Microsoft is investigating whether it makes sense to port SMO over to corefx on Linux and/or offer a set of non-.NET APIs (e.g. Node.js, Python, etc.) for users to programmatically manage SQL Server from Linux.
  • Microsoft is investigating porting sqlpackage.exe and the DacFX APIs to corefx on Linux.
  • Microsoft is investigating options for a cross-platform lightweight SQL Management Studio GUI tool for Linux.
  • Microsoft is investigating open sourcing the SQL Server PowerShell provider and cmdlets, and that it “makes a lot of sense” and “aligns with what Microsoft has already done with our Azure PowerShell cmdlets on github.” This is being tracked by connect item 2442788.
  • Microsoft doesn’t have dates or more details to share for any of these items at this time and will keep the community updated on their progress as they continue to evaluate our plans based on customer feedback

So let’s give them the feedback they are looking for! If you haven’t voted already, upvote these four key Connect items if you are so inclined:

  1. Open source SQLPS module and publish to GitHub
  2. SQLPS module is slow to load
  3. Loading SQLPS module changes current directory to PS SQLSERVER:\>
  4. SQLPS module uses unapproved PowerShell verbs

Talk about thrilling! I can’t wait to see how this develops. Oh, and if you’re feeling charitable, there are a bunch of other SQLPS bugs on Connect that would love your upvotes.

Posted in Linux, PowerShell, SQL Server

SQL Server Client Tools, Linux and Integrated Authentication in a Few Easy Steps

Back in 2013, I wrote about “Using Windows Authentication to Connect to SQL Server from Linux“. Considering how convoluted using Linux can get, doing so was surprisingly straightforward then and it’s even easier now.

Want to see a webcast about this topic? Aaron Nelson and I recorded a session for the SQL PASS PowerShell Virtual Chapter meeting “SQL Server’s sqlcmd on Linux using Windows Authentication” on March 22, 2016.


Scope

This post is intended to show a simplified way to login to SQL Server from your Linux machine using Windows credentials. It covers

  1. Installing SUSE Linux
  2. Updating the server’s hostname
  3. Joining the Active Directory domain
  4. Installing the prerequisites for SQL Client Tools
  5. Downloading and unzipping the SQL Client Tools
  6. Installing the ODBC Driver that contains sqlcmd and bcp
  7. Logging in as a Windows user and executing sqlcmd
  8. Partying

“Hey, installing Linux can’t be counted as one step!” True, however, this can all be done in 5 commands if your server is already setup.

If it’s not, however, this post will walk you through the whole process. Note that this demo uses SUSE Linux and its amazingly simplified setup tool, YaST.

If you’d prefer using RedHat or seeing what goes on behind the scenes, check out this awesome article, “Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos authentication” by Jensd.

Step 1: Install OpenSUSE with most of the defaults

Download OpenSUSE Leap 42.1 NET install and then install, keeping all of the defaults (Next, Next, Next, Next, really.) Well, except two. Don’t install a GUI and do enable SSH.

install-1        install-2

Step 2: Prepare to join AD by updating your hostname

Assuming your DHCP server hands out your domain’s DNS servers, fire up yast by typing sudo yast. Then go to System -> Network Settings -> Hostname / DNS. Enter in your desired server name, and update the DNS stuff.

install-4

Tab to OK, and select.

Step 3: Join the Windows domain

In YaST, go to Network Services -> Windows Domain Membership. Enter your domain name and select all of the check boxes at the top. Tab to OK and enter, then enter your domain admin credentials when prompted.

isntall-smb

Once you’ve successfully joined the domain, manually restart all of the impacted services. Kidding, just go ahead and reboot by typing sudo reboot.

Step 4: Install the prerequisites for SQL Client Tools/the ODBC driver

This is easy enough, just use zypper to install some prereqs.

Note that the README for Microsoft ODBC Driver for SQL Server on Linux says you gotta recompile unixODBC with the proper character set, but for this proof of concept, we won’t.

Step 5: Download and unzip the SQL Client Tools tarball from Microsoft

Download the Microsoft ODBC Driver 13 and 11 Previews for SQL Server – SUSE Linux by executing the following.

Want to use a different distro? Check out the System Requirements for Red Hat and Ubuntu.

Step 6: Install the ODBC Driver that contains sqlcmd and bcp

Download the Microsoft ODBC Driver 13 and 11 Previews for SQL Server – SUSE Linux by executing the following, and entering your password when prompted:

isntall-3

Step 7: Login as your Windows domain user and execute sqlcmd

This is where the magic happens.

sqlcmd

If there’s a failure, ensure your Windows account has access to the SQL Server by using SQL Server Management Studio to verify. If you’re wondering how to connect to SUSE remotely, my preferred SSH client is PuTTY. You can see your server’s IP address by typing sudo ifconfig from the command line.

Step 8: Party

Now you can pour yourself a nice cup of tea or some Belgian beer, and party knowing you’ve just accomplished so much in so little time.

4765486

Note, if you don’t want to login to the Linux box as a Windows User, you can still use integrated authentication! Check out the aforementioned article, “Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos authentication“, and do a Find for kinit.

Also, here are some other pretty awesome links on this subject:

Posted in Active Directory, Linux, SQL Server

Can We Get These 3 SQLPS Issues Fixed before SQL Server 2016 RTMs?

YES WE CAN! Microsoft responded and fixed these 3 major issues in SQL Server Management Studio March 2016 Refresh. Read more at “WE DID IT! Microsoft Fixed Those 3 SQLPS Issues in SQL Server 2016“.


I’ll be honest, I don’t use the SQLPS module even though I’m the perfect candidate. I’ve been a SQL Server DBA since 1999 and a PowerSheller since 2005. A large majority of my PowerShell work is done within SQL Server, and still, I’d rather look up the syntax to load SMO assemblies, than to Import-Module SQLPS, and I’m not the only one.

Reasons I don’t use SQLPS
  1. It takes 3-5 seconds to load
  2. It changes my directory
  3. It produces warnings upon load

I feel displaced when I load the SQLPS module. Why does it take so long? Why you gotta yell all these warnings at me? Wait, where am I? SQLPS’s behavior seems to mimic that of the sqlps.exe minishell when it should really just be itself.

Please upvote these items on Microsoft Connect

SQLPS has a lot of of bugs that need to be addressed (I’ll get to that soon), but I propose we start with these three.

  1. SQLPS module is slow to load
  2. Loading SQLPS module changes current directory to PS SQLSERVER:\>
  3. SQLPS module uses unapproved PowerShell verbs

Each item even has suggested fixes. The fixes are pretty straightforward (said the DBA who doesn’t do QA). Bugs 1 and 2 suggest modifying a few lines in SqlPsPostScript.ps1, while number 3 probably requires a recompile and we’re not really sure how challenging that will be.

If you’re thinking that Microsoft won’t approve the the disapproved verbs request because of backwards compatibility issues, no fear, the Connect item suggests using Set-Alias to address this concern.

Why now? We’ve been waiting for years.

Recently, I teamed up with Aaron Nelson, a SQL Server MVP and Lead of SQL PASS PowerShell Virtual Chapter. Aaron is a SQL Server DBA, a huge proponent of PowerShell and he is also not a user of SQLPS in its current state. But he wants to be. I do, too. You probably do too if you aren’t already.

Aaron helped get this ball rolling the other night when we were working on some SQL PASS abstracts. He was writing up a session proposal about SQLPS, and I decided to take a stab at figuring out why SQLPS loaded so slowly.

Ultimately, commenting out two lines and adding one changed the load time from 5.1 seconds to 0.345 seconds. JUST LOOK AT THE POSSIBLITIES:

loadsqlps

This doesn’t have to be a (partial) pipe dream

The fact that the change was so simple yet so significant gave us hope. Maybe beginning to fix the SQLPS module doesn’t have to be this big thing.

We all want to want to use SQLPS

SQLPS has sordid past and sometimes just talking about it seems kind of controversial. Some folks vigorously defend it. Some denounce it. Ultimately, though, we all just want it to work.

fixthree

(for now)

We want a module that imports quickly, with no warnings and no change of directory. And while we all would love a well-developed module like those bestowed upon Lync, Exchange and SharePoint, I know we’ll all accept, for now, for this RTM, a module that works well right out the box.

Share

Time is running out, so let’s rally right now for a better SQLPS in SQL Server 2016 RTM. Please share this post, a post of your own, or the Connect items themselves.

Can We Get These 3 SQLPS Issues Fixed before SQL Server 2016 RTMs?
Connect: SQLPS module is slow to load
Connect: SQLPS module uses unapproved PowerShell verbs
Connect: SQLPS module changes current directory


Going Forward

As for future bug fixes, I’m going to create a repository somewhere, perhaps this blog, perhaps somewhere more official, that will hopefully bring visibility to all SQLPS bugs that have been submitted. I’ll do what I can to keep the momentum going. Perhaps I’ll even throw out a Twitter bot that searches for SQLPS bugs and posts them for easy retweeting.

Either way, keep an eye out on my Twitter or this blog for more info.

Posted in PowerShell, SQL Server

SQL Server Solution Least Likely to Work, Actually Did

Two of my SQL Servers stopped working after an unexpected power outage. When I attempted to start them, they gave me an Access Denied error. These are two totally different machines (diff Win & diff SQL version) that both, after years of working, just stopped in their tracks with an Access Denied.

This isn’t an error that I’ve ever run into before, so I looked it up and found Jon Morisis’s post that detailed the error and solution that he found buried deep in some forums.

This photo is actually from Jon Morisis

To solve this Access Denied error, you simply have to disable the HotAdd/HotPlug capability of the VM. I doubted this would work for us for three reasons.

  1. I didn’t think we even enabled HotAdd/HotPlug (we did).
  2. Jon ran into his issue during install, and I ran into this after the servers had been up for an extended period of time.
  3. It seems so remotely related

To my and the VMware admin’s shock, it worked! Anyone know why?

Also, it appears that this access denied issue won’t just impact SQL Server, but a number of other things (admin shares were also mentioned). I’m pretty much blogging about it to remind myself about that setting when I encounter weird permissions issues that make no sense.

Posted in SQL Server, VMware

Failed Domain Relationships on SQL Server Cluster Nodes

You know what’s scary as hell? When one node of an important cluster loses its trust relationship with the domain and you see the error “the trust relationship between this workstation and the primary domain failed”. That happened to me late last year with one of my SQL Server 2008 R2 nodes. The scary part was that I just didn’t know what to expect. The fix could be simple, or it could require a node rebuild.

Generally, I address this issue not by removing servers from the domain, but by using PowerShell v3 by executing the following with an admin prompt:
Reset-ComputerMachinePassword

In this case, however, I did not have access to v3, so at an Admin prompt, I executed

netdom resetpwd /s:dc.ad.local /ud:ad\adminaccount /pd:*

This successfully reset the password and I was able to login again with a domain account. I then started the cluster service, and failed my SQL Server over with no issues. I then failed back and rebooted the server for good measure (and to ensure the trust still existed). I tested this as well on Windows 2012 R2 with a SQL 2014 cluster with success, though Reset-ComputerMachinePassword was easier to remember and worked just as well.

What caused the loss of trust? I haven’t figured it out yet, but I’m assuming that node probably cheated with his ex.

Posted in Active Directory, PowerShell, SQL Server

PowerShell Conference EU 2016, psconf.eu

I’ll be presenting some sessions about SQL Server and PowerShell at the PowerShell Conference EU in Hanover, Germany on April 20-22! You should join me, because the speaker and session lineup is amaaazing! Register at psconf.eu.

presenter

Want to know more? Check out PowerShellMagazine.com and psconf.eu!

Posted in PowerShell, SQL Server

PowerShell Tip: You may not need to load the DLL for that Assembly that can’t be found

Recently, I wanted to access the System.Windows.Forms.Integration.ElementHost type, which uses the System.Windows.Forms.Integration namespace.

When I tried to load that namespace, PowerShell complained that The assembly ‘System.Windows.Forms.Integration’ could not be found.

I read up and found that the WindowsFormsIntegration.dll would need to be loaded, and I begrudgingly added this to my script:

It’s ugly though, and I wondered if there was another way. Then I thought maybe I can just add the base name of the DLL file, and sure enough, this works!

So the next time that you see an assembly can’t be found can be loaded by using the DLL, try just the DLL name instead!

Posted in PowerShell

ShowDialog() Sucks: Use ApplicationContext and Run Instead

Update Jan 7: Added KeyboardInterop so that TextBoxes will work.

One of the lessons learned when making Popups and NotifyIcons in PowerShell and WPF is that the WPF window must be run the the proper context.

I was tipped off to this by Denniver Reining’s PowerShell / NotifyIcon article and Johnny J’s “Doing a (C#) NotifyIcon program the right way“.

While neither of the articles were using WPF, extensive testing showed that using ApplicationContexts and Application.Run instead of ShowDialog() made WPF (and WinForms) work far better. The NotifyIcon issue took a number of days to resolve as I battled with unresponsiveness when clicking on the ContextMenu to Exit. Then it would take about 5 seconds to disappear.

For about a week, this was the story of my life:

aero_busy

And it seemed especially true after hiding the Powershell taskbar application. So I scoured the Internet and found that a lot of other people had the same issue. Just search Google for “NotifyIcon doesn’t disappear.

In addition, the mouse sometimes showed as busy when hoovering over the popup window itself and sometimes the right clicking worked only once.

notify-wait

System.Windows.Forms to the rescue (!?)

So none of the solutions I found worked, but I remembered that Denniver’s NotifyIcon script was responsive, so I went back and noticed his app ended with this important line

[void][System.Windows.Forms.Application]::Run($form1)

This information, along with Johnny J’s article about NotifyIcons helped me figure out that the following would probably work, even for WPF. Now these two lines, along with Hide-PowerShell are always included in my finalized PowerShell-based WPF scripts.

$appContext = New-Object System.Windows.Forms.ApplicationContext
[void][System.Windows.Forms.Application]::Run($appContext)

According to Microsoft, “Application.Run begins running a standard application message loop on the current thread, with an ApplicationContext, which specifies the contextual information about an application thread.”

I’ll be honest, I’m probably not the best person to explain ApplicationContexts in depth. If you’d like to learn more, check out this article titled Use the ApplicationContext Class to Fully Encapsulate Splash Screen Functionality where the author goes into detail about the ApplicationContext class.

The code below (which is also the code from my previous post) shows a fully functioning WPF GUI App that runs in an application with an ApplicationContext. It also has a few other cool techniques you may enjoy.

Icon-Overlay

Note that this code should’t be copy/pasted into the console because the PowerShell window will disappear before it can complete the paste. Be sure to download the script or paste the code below into a .ps1 file and execute. It does appear to work well in the ISE, though!

Even though I had changed my own usage of WPF forms, it didn’t occur to me to blog about it until Doug Finke mentioned on Twitter that his WPF apps are sometimes unstable and crash. When I saw the ShowDialog() in his code, I knew exactly what the problem was, and knew I had to get the word out.

As of today, one of my servers has been running its WPF/PowerShell based-monitor for almost a month!

timespan

In conclusion, you should probably never use ShowDialog() to run your primary window in PowerShell unless you’re testing it (and I should probably update my old blog posts to clarify that).

Also, I don’t totally know what I’m doing, so take this with a grain of salt ;) It’s just worked better for me than ShowDialog(). I will probably revisit this one day with a PowerShell implementation of InitializeComponent().

Posted in PowerShell, WPF

Migrating SQL Server?

dbatools is an awesome PowerShell module that helps you migrate entire instances with a single command.


Available from dbatools.io and github

SQLPS Needs You

SQLPS now has a dedicated engineer and Microsoft is asking for our input!


 
Upvote priorities and cmdlets now

Authors


Chrissy LeMaire


Brandon Abshire
View Brandon Abshire, MCDBA's profile on LinkedIn

Awards

Chrissy has been awarded the Microsoft MVP for her work in the PowerShell community.

Join us!

Belgian PowerShell
User Group

  SQL PASS PowerShell
Virtual User Group

 

Upvotes Needed

Help persuade Microsoft to open source SQL Server's PowerShell module, SQLPS.


 
Upvote now on Microsoft Connect