A Fine Slice Of SQL Server
Raul Gonzalez wants to combine Always Encrypted with temporal tables in SQL Server 2016:
Again the wall. There is no way you can choose a temporal table and apply encryption to a column or columns using the wizard.
I tried then using the powershell (after manually creating the keys) as this is true the only way to encrypt existing columns, just in case.
Raul doesn’t stop there, though, and he does figure out a workaround.
Comments closedRamkumar Chandrasekeran discusses DeployR, an enterprise security model for R:
DeployR Enterprise is designed to deliver analytics solutions at scale to whomever needs it: inside or outside the enterprise. It also guarantees secure delivery of your analytics via DeployR web services. These secure web services integrate seamlessly with existing enterprise security solutions: Single Sign-On, LDAP, Active Directory, PAM, and Basic Authentication, can enforce access privileges already defined by your IT department for existing enterprise users and also have the capability to safely support anonymous users when needed.
There’s nothing groundbreaking here: it’s TLS (to encrypt network transmissions) and LDAPS (to control authentication and authorization). That there’s nothing groundbreaking is a good thing—that means companies will have most of the infrastructure in place to support this.
Comments closedMichael Noll shows security features of Kafka Streams:
First, which security features are available in Apache Kafka, and thus in Kafka Streams? Kafka Streams supports all the client-side security features in Apache Kafka. In this short blog post we cannot cover these client-side security features in full detail, so I recommend reading the Kafka Security chapter in the Confluent Platform documentation and our previous blog post Apache Kafka Security 101 to familiarize yourself with the security features that are currently available in Apache Kafka.
That said, let me highlight a couple of important Kafka security features that are essential for implementing robust data infrastructures, whether these are used for building horizontal services at larger companies, for multi-tenant infrastructures (e.g. microservices), or for shared platforms such as in the Internet of Things. Later on I will then demonstrate an example application where we use some of these security features in Kafka Streams.
It’s important to secure sensitive data, even in “transient” media like Kafka (though the transience of Kafka is user-definable, so “It’ll go away soon” isn’t really a good argument).
Comments closedSlava Murygin shows how to create a firewall rule to allow SSIS connections:
Recently tried to connect to Remote SQL Server Integration Service directly from SSMS and got following error:
TITLE: Connect to Server
——————————
Cannot connect to 10.1.32.66.
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
——————————
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
——————————
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
——————————
BUTTONS:
OK
——————————
Slava then shows how to work around this.
Comments closedJakub Szymaszek notes that Azure SQL Database can now support Always Encrypted:
I’m happy to announce Always Encrypted in Azure SQL Database is now generally available!
Always Encrypted is a feature designed to ensure sensitive data and its corresponding encryption keys are never revealed in plaintext to the database system. With Always Encrypted enabled, a SQL client driver encrypts and decrypts sensitive data inside client applications or application servers, by using keys stored in a trusted key store, such as Azure Key Vault or Windows Certificate Store on a client machine. As a result, even database administrators, other high privilege users, or attackers gaining illegal access to Azure SQL Database, cannot access the data.
To be honest, I’d much rather try Always Encrypted against an Azure SQL Database instance than an on-premise instance, mostly because if I hose Azure SQL Database that badly or the company decides that Always Encrypted isn’t a good fit, I can grab the data and dump the instance. It’s a little harder to do that with physical hardware or even an on-prem VM.
Comments closedRyan Adams discovered that Database Mail cannot use TLS 1.2 at this time:
You may recall something called the POODLE attack that revealed a vulnerability in SSL 3.0 and TLS 1.0. This particular server had SSL 3.0, TLS 1.0, and TLS 1.1 disabled in the registry. Also note that TLS 1.2 was NOT disabled. The server was running Windows 2012 R2. These protocols were disabled to prevent the possibility of a POODLE attack. If you are wondering how to disable these protocols on your servers then look at Microsoft Security Advisory 3009008. To disable them for the whole OS scroll down to the Suggested Actions section and look under the heading “Disable SSL 3.0 in Windows For Server Software”.
I also want to note that the PCI Security Standards Council pushed back the date for getting off of SSL and TLS 1.0 to June 30th, 2018. In addition to that, it should also be noted that Microsoft’s Schannel implementation of TLS 1.0 is patched against all known vulnerabilities.
The root cause is interesting: it’s because Database Mail requires .NET Framework 3.5. Ryan has more details, including a fix, so read on.
Comments closedErik Darling notes that databases using Transparent Data Encryption now support backup compression:
First, the database without a Max Transfer Size at the bottom was a full backup I took with compression, before applying TDE. It took a little longer because I actually backed it up to disk. All of the looped backups I took after TDE was enabled, and Max Transfer Size was set, were backed up to NUL. This was going to take long enough to process without backing up to Hyper-V VM disks and blah blah blah.
The second backup up, just like the blog man said, no compression happens when you specify 65536 as the Max Transfer Size.
You can see pretty well that the difference between compressed backup sizes with and without TDE is negligible.
Check it out, including the table Erik put together. I’m glad that backup compression is now supported, although I’m kind of curious how they can do that while retaining encrypted backups—are they decrypting data, writing to backup (and compressing), and then encrypting the backup? That’d be worth checking out with a hex editor.
Comments closedReza Rad takes a crack at row-level security within Power BI desktop:
Row Level security is about applying security on a data row level. For example sales manager of united states, should only see data for United States and not for the Europe. Sales Manager of Europe won’t be able to see sales of Australia or United States. And someone from board of directors can see everything. Row Level Security is a feature that is still in preview mode, and it was available in Power BI service, here I mentioned how to use it in the service. However big limitation that I mentioned in that post was that with every update of the report or data set from Power BI Desktop, or in other words with every publish from Power BI Desktop, the whole row level security will be wiped out. The reason was that Row Level Security wasn’t part of Power BI model. Now in the new version of Power BI Desktop, the security configuration is part of the model, and will be deployed with the model.
This is a great security feature, so I’m happy to see the Power BI team taking it the next step forward and integrating RLS directly into Power BI desktop.
Comments closedRob Sewell looks into some new Powershell cmdlets for SQL Server management:
Chrissy LeMaire has written about the new SQL Agent cmdlets
Aaron Nelson has written about the new Get-SqlErrorLog cmdlet
Laerte Junior has written about Invoke-SQLCmd
All four of us will be presenting a webinar on the new CMDlets via thePowerShell Virtual Chapter Wed, Jul 06 2016 12:00 Eastern Daylight Time If you cant make it a recording will be made available on YouTube on the VC Channel https://sqlps.io/video
There are 17 new Always Encrypted cmdlets and 25 new cmdlets in total.
Comments closedRandolph West discusses production access:
During a recent client meeting about a database migration, I realised that I have never logged into a SQL Server on their production environment. My involvement has been strictly dealing with setting up the new environment and log shipping the backups.
I get that I’m not a full-service DBA for this client, but it got me wondering about the many security discussions I’ve seen and participated in, in the past: that not even a junior DBA might need access to production database systems, if it’s not within the scope of his or her work.
Limiting production access is a smart move, but it’s important to realize the downstream consequences: the people who still have access to production will (at least in the short term) have to perform a lot of the tasks that others were doing previously, including data fixes, research, etc. It’s important to be prepared for that.
Comments closed