Keeping up with SQL Server Connect Items Marked Closed as Fixed

Earlier this week, I was trying to determine people’s feelings about Microsoft Connect by looking through Twitter. A few scrolls down, and I came upon this

That gave me the idea to make a Twitter bot, which I called @closedasfixed, that searches Microsoft Connect for items recently marked as Resolved or Closed as Fixed.

Basically, it scrapes the website every 30 minutes (their feed rarely gets recompiled so is not useful) and posts one Tweet. Seems to be a hit so far, gaining nearly 50 followers in just one day.

Here’s my Scraping and Twitter code if you’d like to roll your own :)

Posted in PowerShell, SQL Server

Counting the number of occurrences of a string or file in PowerShell

I always forget how to do this, and Aleksandar Nikolić posted a really beautiful answer on Powershell.com

For a file:

For a string:

Posted in PowerShell

Trello is awesome for everything

The other week, I was introduced to Trello for the first time by this tweet and I fell in love.

Basically, Nick uses Trello to gauge what people would like to hear about. Later on when trying to figure out how to best organize the Microsoft Connect items about SQLPS, it occurred to me that Trello would be the perfect solution. And it is.

The SQLPS Trello board worked out so well, that when the topic of getting overdue some improvements into SSMS was brought up, Microsoft suggested that setting up another board for SQL Server Management Studio might work well too.

If you use either of these products, I strongly suggest you join us and give Microsoft your feedback. They actively participate on both boards which is just amazing.

To add to the fun, I created a PowerShell module to help easily tally the votes for any Trello board, and I put it in the PowerShell Gallery. To install and run, just run

This gives you 3 functions
– Get-SqlPsVote
– Get-TrelloVoteCount
– Get-SsmsVote

Get-SqlPsVote shows the tally from the SQLPS Trello board in a GridView. Get-SsmsVote shows the votes from the SSMS board and Get-TrelloVoteCount is a generic function that works with any public board. Here’s the one from Nick’s board:

votecount

Or, if you’re into it, check out an overview video:


In addition to community stuff, I’m also using Trello to help me sort out the design of my own module, dbatools.

So if you’re into community involvement or PowerShell module design, give Trello a shot. It’s my favorite new thing.

Posted in PowerShell, SQL Server

Active Directory and PHP on Apache on Bash on Ubuntu on Windows

Recently, I wrote about Joining Ubuntu to an Active Directory Domain. That was for an actual Ubuntu box and the plan was that the next post would be about adding Apache to the mix. But then I got distracted by Apache on Bash on Ubuntu on Windows (check out my gists for sneak peaks).

I should be practicing for my upcoming sessions for PowerShell Conference EU 2016, but look at the reward!

ubuntu

First a few things that I remember that I’ve learned in this 2 day journey

  • Apache is broken out of the box, but works if you mkdir /run/lock.
  • The Linux are in C:\Users\username\AppData\Local\lxss\rootfs but don’t try to edit them directly from Windows. They disappear from within bash or become corrupt.
  • Instead, access /mnt/c and copy from within Linux.
  • Samba doesn’t work, but you don’t need it for this. SSSD doesn’t either.
  • You can access bash’s Apache from Windows no sweat, but Kerberos within Linux doesn’t like localhost or the hostname when going between hosts. Make an extra A record to deal with that.

Getting Started

First, part of this is done in Windows because ktpass is required. The Ktpass command-line tool “allows non-Windows services that support Kerberos authentication to use the interoperability features provided by the Kerberos Key Distribution Center (KDC) service.”

Basically, it creates a binary file that contains some encrypted stuff that authenticates as a valid AD user.

ktadd is Linux’s equivalent of ktpass. If you see it mentioned while you’re setting up interoperability, it’s not applicable to Active Directory. ktpass.exe is supposedly found Remote Server Administration Tools for Windows or RSAT, but that wasn’t my experience with Windows 10. It’s nowhere to be found on my 64-bit machine. Instead, I Enter-PSSession to my domain controller because all of my testing is done in a lab environment so it’s acceptable.

Now for my setup

  • Active Directory domain: base.local
  • Service acct: base\ubuntuauth
  • Service acct pass: SkiAlta2009
  • Win 10 w/bash workstation name: nimy.base.local
  • Secondary DNS name: localweb.base.local
  • Firewall allows port 80

“Wait, why is a secondary hostname needed? I just wanna hit http://localhost.” Because I think Bash on Windows confuses Kerberos. Or maybe that’s just the way it’s supposed to work, if anyone knows, feel free to comment.

In this example, open http://localweb in your browser. Since the localweb hostname set in my DNS, it worked both locally and remotely for me.

The Windows Part

The Bash on Ubuntu on Windows Part

Initially, you’ll install a few packages and krb5-user will actually prompt you for a few things then write it all out to /etc/krb5.conf

Capitalization is important for the default realm.

krb-1

krb-2

krb-3

If you don’t see this all in a row, don’t panic. It flips back to the default black screen while it sets up the configuration files. That’s actually it for the Kerberos part, mostly. Now it’s time to test and then setup Apache.

That’s it, now you can hit the service using a web browser :D Open up http://localweb in Chrome and check it.

Want the PHP part?

Ain’t she a beaut?
ubuntu

Wanna see this all in one shot? Here’s the Gist. If you’re into integrated authentication, there are other gists there that you may enjoy.

Posted in Active Directory, Apache, Linux, Security, Windows

Joining Ubuntu to an Active Directory Domain

Back in 2009, I did a whole lot of messing around with Linux and Active Directory integration, primarily for Apache. Now that Linux is coming to Windows, I figured I’d brush up on my Linux and Windows Integrated Authentication skills to work with Ubuntu, too.

Clients and Servers

Since 2009, it seems that a couple things have changed in the client realm. In particular, winbind fell out of favor to Likewise Open (which I used to <3) which was bought by BeyondTrust and turned into PowerBroker Open. But that’s since fallen out of favor to the SSSD or “System Security Services Daemon“. SSSD seems pretty cool but everyone hates its name and assumes that its name is keeping it from greater adoption.

Sometimes when researching SSSD, you’ll come across a few mentions of FreeIPA which is similar to Active Directory, OpenLDAP, and ApacheDS. Oh, and I recently found out that Samba4 allows Linux servers to join Active Directory as Domain Controllers (!!) but I can’t tell if it can be a forest of its own (reddit review here).

There are other players I’m leaving out but after a bit of casual research, no others seem to stand out. Ultimately, while there are a number of ways to setup AD/Linux authentication with Ubuntu, it appears that SSSD is the current way to go. Let’s go ahead and set that up.

Before We Begin

There’s an official Ubuntu guide for SSSD and Active Directory, but this one is slimmed down. If you have any issues, you can comment here or reference some of the solutions they offer. First, some assumptions.

  • Fresh install of Ubuntu 15.10 Server
  • DNS is set to AD’s DNS servers
  • The Active Directory domain is base.local
  • The test user is base\adadmin, which has domain admin privs on AD

If you’re behind a proxy, apt-get and curl/wget/etc won’t work out of the box. Here’s how to add some proxy variables (kinda like Internet Properties -> Connections -> LAN settings -> Proxy Server) so that you can use these tools.

Also, you’re going to need to make sure that your time is set properly. Kerberos is heavily dependent on time, and will break if your computer is more than 5 minutes skewed from the AD domain.

Joining the domain

Joining an Active Directory in Ubuntu isn’t quite as easy as SUSE, but it’s still decently straight-forward.

  • Install required packages
  • Create and modify sssd.conf
  • Modify smb.conf
  • Restart services
  • Join domain

First, installed the required package using apt-get. I also recommend command-not-found and mlocate, which help you with finding files.

Note, in this tutorial, I use vi. I used to use pico, which became nano, but found that vi could be found across all distributions by default. vi or “vim” can be intimidating, but honestly, I only know about 5 commands and it gets me by. Here’s a nice tutorial on Learning vi progressively.

Next, setup SSSD by creating the file, setting the owner, and changing its permissions

Note that this config only allows 2 users and 2 groups to gain access. If you remove the last 2 lines, anyone can login. There are a few ways to restrict access but it looks like this is the simplest way.

Next, sudo vi /etc/samba/smb.conf and replace the line workgroup = WORKGROUP with the following:

Time to restart services and join the domain! Here’s a screenshot of my Ubuntu server “ubuntunew” joining my domain, base.local:

join-domain-sudo

It’s just these commands, nothing scary:

If you have permissions to add computers to the domain and everything went well, then you should now be able to see your Ubuntu server in Active Directory!

ad-computer-list

Now that everything is setup all nice, start SSSD to do some caching and interception that makes things much smoother somehow.

Login as Windows user

If you’d like to login to the machine as a windows user, as opposed to just grabbing a ticket using kinit, you can either login via SSH or by using su or “substitute user”.

Here’s an example of me using su. First, I logged in to the Linux server as a regular user, then I login as my own Active Directory account “base\ctrlb” by issuing the command su ctrlb. An alternative way is su base\\ctrlb. That extra backslash is intentional, it escapes the second backslash.

su-ctrlb

Alternatively, you can ssh in directly with ssh or PuTTY.
full-login

And that’s it! Want to see this all in one shot? Check out the gist.

Posted in Active Directory, Linux, Security

WE DID IT! Microsoft Fixed Those 3 SQLPS Issues in SQL Server 2016

In my previous blog post “Can We Get These 3 SQLPS Issues Fixed before SQL Server 2016 RTMs?“, Aaron Nelson and I asked the SQL and PowerShell community to help upvote 3 SQL Connect items. The items addressed three problems with SQL Server’s PowerShell module, SQLPS.

  • It took 3-5 seconds to load
  • It changed the present working directory when loaded
  • It produced approved verb warnings when loaded

Today, Microsoft responded, letting everyone know that the issues were addressed in SQL Server Management Studio March 2016 Refresh.

That is absolutely amazing! Your 243 combined upvotes helped this happen:
sqlps

Check that out! Fixed verbs + backwards compatibility. No changing of the present working directory. And SQLPS now loads in 0.22 seconds instead of 5 seconds.

But wait, there’s more!

Microsoft’s “SQL Tools Guy” Ken Van Hyning left the following comment in the previous post:

  Please keep the connect bugs and suggestions coming. We are working on making more improvements to SQLPS in the months ahead.  

You saw that right! Microsoft is working on SQLPS! And they want to know what needs fixing.

Let Them Know What’s Broken in SQLPS

The best way to let the SQL Server Team know what you’d like addressed is by using SQL Server’s Connect Feedback page. To file a bug or suggestion, click the link, then search to see if your bug has already been submitted. If not, click the Submit feedback button, and fill it out.

mahbug

We found that effective bug and suggestion reports include the following if at all possible

  • Why the issue matters
    • “Slow load times decrease adoption rates”
  • A solution if at all possible
    • “Remove line 7 and replace with XYZ because it is a more efficient call”
  • Address potential concerns
    • “Backwards compatibility can be addressed by using aliases for the unapproved verbs.”

Of course, this is just a suggestion. It’s what worked well for us. Oh, and if you haven’t already, please upvote the request for Microsoft to Open Source SQLPS by putting it on GitHub, and publishing it to the PowerShell Gallery.

And finally, thank you to everyone Microsoft that helped make this happen!

Posted in PowerShell, SQL Server

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

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

Upvotes Needed

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


 
Upvote now on Microsoft Connect

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