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

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

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

SqlServer Needs You

SqlServer 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