Troubleshooting Login Failures

Jana Sattainathan explains why the default login failure message is so unhelpful:

At first glance, it appears to be utterly useless. The only useful message seems to be “Login failed for user ‘myuser‘.” but what we really need to know is “why?”.

That information is somewhat buried in this section of the message:

1
2
3
4
Server Name: mysqlserverinstance
Error Number: 18456
Severity: 14
State: 1

Based on the documentation for this error, the severity and state actually mean something. I have copied and pasted the same from the link.

Read on to find out how to get the actual error.

Azure Networking

Joshua Feierman has an article on how Azure Networking works, particularly from the viewpoint of a DBA:

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.

Cloned Database Security

Parikshit Savjani explains what happens when you run DBCC CLONEDATABASE on databases with various security measures activated:

Transparent 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.

xp_cmdshell Not A Security Risk

Kevin 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.

TDE And Encrypted Backups

Arun 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.

Replication And TDE

Drew 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.

Always sa

James 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.

Everyone’s A Sysadmin

Raul 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.

Knox With Active Directory

Jon 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.

Power BI Security Updates

Ginger 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.

Categories

April 2017
MTWTFSS
« Mar  
 12
3456789
10111213141516
17181920212223
24252627282930