A Fine Slice Of SQL Server
There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.
In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.
Kenneth’s getting fancy with animated GIFs, and gives us a good walkthrough of this aspect of SQL Agent security.
Comments closedChrissy LeMaire shows us how to connect to AD from Ubuntu:
Since 2009, it seems that a couple things have changed in the client realm. In particular, winbindfell 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 assume 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 thatSamba4 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.
Cf Ryan Adams and LeMaire’s separate posts back in March on the topic. As Microsoft gets serious about Linux integration, I would love to see them simplify this process significantly, either by updating an existing open-source project (my preference) or creating their own open-source project.
Comments closedBrent Ozar reports that Azure SQL Database’s CLR will be turned off:
Details are still coming in, but in the Reddit AMA for the Azure database teams (going on as we speak), it’s one of the users reports that they got an email that SQL CLR will be shut off in one week due to a security issue.
The cloud: at the end of the day, it’s just someone else’s server, and they can – and will – take tough actions to protect their product, their users, their security, and their profits.
I’m curious for more details. I’d like to know if this is particular to Azure or affect on-prem installations as well.
Comments closedJohn Morehouse shows you how to fix plaintext password storage when you can’t fix the application:
Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application. The application doesn’t know if it’s calling a table or a view so that’s why this works.
You should never store passwords in plaintext. You should almost never store passwords in a reversable format (i.e., encrypted). The primary case in which I can see storing passwords encrypted rather hashed is if you have automated systems (or non-automated technicians) which need passwords to authenticate somewhere. Even then, there’s a lot of value in using OAuth tokens. But if you can’t get around any of this, John’s solution does remove the really bad decision of leaving passwords in plaintext.
Comments closedSteph Locke has created an R package to query Troy Hunt’s Have I Been Pwned? site:
The answer in life to the inevitable question of “How can I do that in R?” should be “There’s a package for that”. So when I wanted to query HaveIBeenPwned.com (HIBP) to check whether a bunch of emails had been involved in data breaches and there wasn’t an R package for HIBP, it meant that the responsibility for making one landed on my shoulders. Now, you can see if your accounts are at risk with the R package for HaveIBeenPwned.com, HIBPwned.
This is a nice confluence of two fun topics, so of course I like it.
Comments closedJohn Martin shows that part of securing your environment includes encrypting SQL Server connections:
In order to demonstrate just how easy it can be to get hold of the information inside the TDS packets I will be using Network Monitor from Microsoft, this will capture the network packets sent and allow me to see the details of what is being sent. Other tools such as Wireshark will also provide a level of insight into what is being sent between the application and SQL Server. I have configured three Windows Server 2012 R2 systems, one with the client (SQLCMD), one with SQL Server, and finally one which will act as a router between the two subnets that each server is on. This configuration can be seen below;
Wireshark is a good friend of mine. It should be a good friend of yours, too, but only if your environment allows you to have a packet capture tool installed.
Comments closedIn the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method. The advantage to this method is not having a linked server and being able to call it directly out of TSQL. Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries. The first query is the LDAP query inside the OPENROWSET function. Once those results are returned we are using another query to get what we want from the result set. Here is where I want you to stop and think about things. If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do? I brought back a ton of extra data over the wire for no reason because I’m not planning to use it. What we should see here is that the columns on both SELECT statements are the same. They do not, however, have to be in the same order. The reason for that is because LDAP does not guarantee to return results in the same order every time. The attribute or “column” order in your first SELECT statement determines the order of your final result set. This gives you the opportunity to alias anything if you need to.
You can query LDAP using SELECT statements, but the syntax isn’t T-SQL, so in my case, it was a bit frustrating getting the data I wanted out of Active Directory because I was used to T-SQL niceties. Nevertheless, this is a good way of pulling down AD data.
Comments closedSteph Locke shows how to set up an SSH tunnel to connect to an external server within R:
Whilst down the rabbit hole, I discovered just in passing via a beanstalk article that there’s actually been a command line interface for PuTTY called
plink. D’oh! This changed the whole direction of the solution to what I present throughout.Using
plink.exeas the command line interface for PuTTY we can then connect to our remote network using the key pre-authenticated via pageant. As a consequence, we can now use theshell()command in R to use plink. We can then connect to our database using the standard Postgres driver.
PuTTY is a must-have for any Windows box.
Comments closedRyan Adams shows us how to get Active Directory authentication on Linux using Samba and PAM:
Back to our question at hand. Since the preview just got released I have not had a chance to test this out with SQL Server. However, you can use Active Directory accounts with Linux and Unix by using Samba and PAM. I see no reason why this implementation would not also work with SQL Server on Linux, but again I have not yet had a chance to test this out. Since I already have some written installation instructions, that’s what I am providing here.
Chrissy LeMaire, in the comments, shows how to do it in OpenSUSE. She also has a brand new blog post on the topic.
Comments closedYou work with Database Mirroring or AlwaysOn AG, and you want to make sure your end users work only on the secondary server. How should you do that?
This solution feels a little hacky to me. There’s enough value in it that I could see companies doing this, but it’d be nice if there were an easier way.
Comments closed