A Fine Slice Of SQL Server
The connecting thread between an Azure virtual machine and a virtual network is a Virtual Network Interface Card, or VNic for short. These are resources that are separate and distinct from the virtual machine and network itself, which can be assigned to a given virtual machine.
If you go to the “All Resources” screen and sort by the “Type” column, you will find a number of network interface resources.
There’s some good information in here.
Comments closedTransparent Data Encryption (TDE)
If you use TDE to encrypt data at rest on the source database, DBCC CLONEDATABASE supports cloning of the source database but the cloned database is not encrypted by TDE. Thus, the backup of the cloned database will be unencrypted. If it is desired to encrypt and protect cloned database backup, you can enable TDE on cloned database before it is backed up as shown below
It’s a good read which covers several technologies.
Comments closedKevin Hill makes a great point:
A stored procedure that, out of the box, is disabled and has no explicit rights granted (or denied) is locked down to everyone but those in the sysadmin server role.
If someone exploits your SQL Server via xp_cmdshell, its because you LET them, either by granting permissions or by putting someone in sysadmin that clearly should not have been there.
For this in more detail, check out Sean McCown’s post from 2015.
Comments closedArun Sirpal shows that Transparent Data Encryption will also lead to encrypted backups:
Ok great let’s check this backup file using a cool tool (XVI32). I really want the contact number of a guy called SQLDOUBLEG because I need his help tuning my SQL Servers so I go looking for a text string, hopefully the phone number will be close by.
As Arun points out, you don’t need TDE to get encrypted backups, but it does the job.
Comments closedDrew Furgiuele looks at how replication interacts with Transparent Data Encryption:
But what happens if we set up a transactional replication publication on this database and do a snapshot? Remember that when you create a publication, your distributor and subscriber(s) need to know which network share (or FTP server) to drop all the data and schema definitions to so they can be read in by the distribution agent and recreated. In my example, I’m dropping them to a network share. Once the snapshot completes, let’s go check out our subscriber database…
Uh oh. The same query returned zero results at the subscriber. Which means no encryption! Replication won’t replicate encryption, at all. So if you have a requirement to encrypt your data at the source, you’ll need to do it on your subscribers too.
Drew points out a couple important gotchas which might lead to you exposing information you didn’t intend to make available.
Comments closedJames Anderson rails on inappropriate sa account usage:
I can already hear managers saying:
If you don’t trust your employees, why employ them in the first place?
Well there is the whole accidental damage thing. I guess you could cover that by having a good backup\restore process (if your RTO and RPO permitted the downtime) but don’t expect to pass any security audits coming your way. Hint: your clients wont like this.
Plus, supposing everybody knows the sa account, there’s no way to know who accidentally(?) dropped the customer database.
Comments closedRaul Gonzalez has a script to see if you’re in a terrible security situation:
I’m not sure what it’d scare me more, if there are many [sysadmin] or just [sa] because the first one is scary, but the second involves to find out who knows the [sa] password and knowing who did what, can be a real pain in the neck.
One way or another, as I said, I want to know the different people and level of access to my server[s], so back in the day I created this stored procedure which now I want to share with you guys.
We can find all that info using DMV’s and in my case I use sys.server_principals, sys.server_role_members and sys.server_permissions and some stored procedure which I bet it’s not that well known, sys.xp_logininfo which help to get more granular picture from Windows AD Groups.
Click through for the script.
Comments closedJon Morisi shows how to configure Knox to work with Active Directory:
I’ve recently been doing some work with Hadoop using the Hortonworks distribution. Most recently I configured Knox to integrate with Active Directory. The end goal was to be able to authenticate with Active Directory via Knox (a REST API Gateway) and then on to other services like Hive. I also configured Knox to point to Zookeeper (HA service discovery) vs. Hive directly, but that’s really more detail than we need for integrating Knox with AD.
The Knox documentation is really good and very helpful:
https://knox.apache.org/books/knox-0-9-0/user-guide.html
Worth the read if you’re putting together a Hadoop cluster.
Comments closedGinger Grant notes that there have been some big changes to Power BI security:
People who have been granted Power BI administrator rights will also notice a modification to the Admin screen. The March 2017 update to Power BI provides a major change to the security model in Power BI. Previously all the security settings were set at the Tenant Level, meaning that all the privileges were granted to all users. If I wanted to allow one group within the organization to be able to publish reports to the web, but I did not want to allow everyone to publish reports to the web, there was no way that this could be accomplished. All that has changed. It is now possible to include or exclude groups of users from having rights in Power BI. Users can be classified into security groups in Azure Active Directory, either through the Office 365 Admin Center or via the Azure AD Admin Center. Once created the security groups can be used in Power BI. Security Groups are not the same thing as the groups created in Power BI when a new work group is created.
Read the whole thing.
Comments closedCedric Charlier shows how to use server audits to track failed and successful logins (and logouts):
The core issue is that we have many users and logins on our databases and we have huge doubt their respective needs. The root cause is identified: sometimes, for a short period of time, we’re making exceptions to our own rules and let a few other friend projects access to our DEV database. On some other cases, we’re connecting our own solution in DEV environnement to the QA environnement of another solution. Why … planning, data quality issue, … we’ve valid reasons to do it … but these exceptions should be removed as soon as possible. And you know what? People forget. Nowadays, on our largest solution, we have 20 users but only 7 of them are expected and documented … other should be removed. But before executing this cleanup, we’d like to be sure that these users are not effectively used by other solutions. If it’s the case, we’ll need to update first the configuration of the corresponding solution.
Click through for a few scripts to show how to set this up as well as how to query the audit log.
Comments closed