It’s 2016, so why are you still using sp_help_revlogin?

TSQL2SDAY-150x150

This month’s T-SQL Tuesday, hosted by Michael Swart (blog | twitter), is all about SQL Server 2016.

sp_help_revlogin is now old enough to drive a car

By now in your career, you may have needed to migrate a few databases and logins. The process isn’t as bad as it could be, after all Microsoft published sp_help_revlogin over 15 years ago to help migrate logins with their passwords and SIDS. Unfortunately, there has been no notable progress since this stored procedure was first released.

Why is this an issue? Well, by using sp_help_revlogin, you are required to do the following, as demonstrated in the painfully boring 41 second video below:

  1. Find the stored procedure somewhere on your hard drive/the Internet
  2. Install sp_help_revlogin and sp_hexadecimal on the source server
  3. Execute it on the source server
  4. Copy the resulting SQL output
  5. Paste it into the query pane of the resulting server
  6. Execute

Now you’ve migrated the logins with their passwords, SIDs, and a few default properties. But you don’t have the logins’ server roles, server permission sets, database roles or database permission sets. So now you gotta find and use someone’s modified version of sp_help_revlogin, but you’re still left with manually executing the procedure against your source and destination servers.

Oh, and don’t forget different versions of SQL Server use different hashing algorithms, so you’ll need to use one of the many different versions of sp_help_revlogin if you have a mixed environment.

Let’s hope you only have one or two SQL Servers to migrate and not hundreds.

Introducing Copy-SqlLogin and Export-SqlLogin

Copy-SqlLogin and Export-SqlLogin are the PowerShell answers to performing SQL Server login migrations. These commands are not part of Microsoft’ official SQL Server module, SQLPS, but they do help illustrate how PowerShell can make our lives easier.

Want to export all of your logins to a file, complete with SID, hashed password, roles, permission sets, securables and all that? Just install the dbatools module then execute

 
    Export-SqlLogin -SqlServer sql2005 -FileName C:\temp\sql2005-logins.sql  
 

By default, Export-SqlLogin exports all logins, but you can also choose which logins you want to include or which you want to exclude by using auto-populated parameters.

export-sqllogin

Want to see sample output? Here’s sql2005-logins.sql on Gist.

Or what if you want to do a live login migration from SQL Server 2000 to SQL Server 2016? I’ve done it a ton of times (in a lab of course. SQL2k doesn’t exist in my prod ;).

 
    Copy-SqlLogin -Source sqlsvr2000 -Destination newsql2016  
 

Copy-SqlLogin works on SQL Server version 2000-2016. It takes care of the hashing algorithm differences. It works on clusters, named instances, all editions from Express to Enterprise and it copies both Windows and SQL Logins. It copies the default database, default language, password policy settings, securables, permissions sets (server & db), and roles (server & db). And it does so in just a few seconds.

copy-sqllogin-ani

If you’d like a walk-through, check out the video below where I go a bit more into depth about Copy-SqlLogin, create a login, migrate the login, then log into SQL Server as the newly migrated account.

Bonus

Need to sync your login permissions for your availability groups? Sync-SqlLoginPermissions was created by request, just for that. Unlike Copy-SqlLogin, Sync-SqlLoginPermissions doesn’t add new logins. It just syncs the server & db permission sets, as well as the server & db roles and job ownership.

 
    Sync-SqlLoginPermissions -Source sql2005 -Destination sql2016  
 

Need to copy jobs for you availability groups, too? Copy-SqlJob has you covered.

In 2016, DBAs will begin to fully adopt PowerShell

The year 2016 is an especially exciting time for the SQL and PowerShell community.

It’s no secret that prior to SQL Server 2016, the SQL Server team didn’t invest much in PowerShell. SQLPS was slow, rude, and didn’t conform to standard PowerShell practices. The SQL Server community was given a module with less than 50 commands, which left us needing to create solutions of our own. Meanwhile the Lync, Exchange and SharePoint communities were bestowed with over 700 commands each.

But all of that has changed changed. Microsoft now has a dedicated SQL PowerShell engineer and the SQL Server Tools team is actively asking for community feedback. They’ve even promised new cmdlets each month! Just check out the exciting things going on with the soon-to-be-released cmdlet, Get-SqlErrorLog.

Convinced yet?

If you’ve been a fan of PowerShell, I hope the SQL Server team’s recent dedication to PowerShell renews your excitement. If you’re a PowerShell holdout, I understand. I adopted PowerShell for SharePoint long before I did PowerShell for SQL Server. I thought it was slow (have you seen the performance of Start PowerShell in SSMS prior to 2016?!) and hard to learn.

But now I see its power and it will only get more awesome now that PowerShell has a dedicated resource within the SQL Server team.

If you still need convincing, check out dbatools.io for some fun and powerful commands like Reset-SqlAdmin, Get-SqlServerKey, Set-SqlMaxMemory and Start-SqlMigration.

Thanks for joining me for this month’s T-SQL Tuesday!

Posted in PowerShell, SQL Server

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.

Troubleshooting

If you’re having an issue joining the domain with the error message “Failed to join domain: failed to lookup DC info for domain ‘BASE.LOCAL’ over rpc: An internal error occurred” you can specify the exact domain controller you want to contact (h/t Florent Appointaire).

If you’re getting the error “failed to lookup dc info for domain base rpc undetermined error”, you may have a stale DC. Consider following applicable portions of this tutorial by Microsoft (h/t Rob Sewell)

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