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.
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.
In 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.
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.
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 the
shell()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.
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.
You 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.
The user still won’t be able to create procedures or views. And if you think about it in the right way it makes sense. While the CREATE permissions give us the ability to create objects what they don’t give us is a place to put them. Schemas are containers for most of the objects in databases. So in order change the schema (by putting an object in it) we need to grant ALTER on it. So for the CREATE to work we need to:
Getting the right granularity for permissions is a vital part of securing a SQL Server instance.
Did you know that you can change the password on the SQL Service account that is running your SQL instance without a reboot or restart? Turns out this is true. We have a new round of password requirements and it means that we need to change passwords on servers more often. But, since we need our servers up and reboots have to be heavily planned, we needed a solution that kept us from having to restart an instance after a password change. This lovely msdn article explains all the details, but let me give you the cliffs notes.
This is helpful for those max uptime scenarios where even a momentary service restart requires planned downtime.
Life we be so much easier if we could just trust everyone, but since we can’t we need solid security for our databases. Azure SQL Database has many security features to make you sleep well at night:
Most of these are exactly the same as the on-premise product—at least the SQL Server 2016 version—but it goes to show that Azure SQL Database has grown up quite a bit.
I wanted the script to do a few things. Tell me who is in a AD Group that was granted rights. IT is one thing to see the group name, but that doesn’t really tell me who has access. I also wanted the output to be a little more user readable, so I formatted the output some. There are other things I did too, but you can fun reading through the code and comparing the 2 sources.
Knowing who’s allowed to do what is key to having a successful security posture. This script won’t tell you object-level permissions, but at least gives you an idea about role and group membership.