Shane O’Neill wants to know whether the dbcreator built-in role has the ability to back up the databases it creates:
I knew from reading the documentation that
dbcreatorgrants permissions to create, alter, drop, and restore databases. My question was does it give permission to backup databases?It seems to give everything else so is backup databases just missing there? Or is it intentionally left out?
To find out whether it does, click on through.
Meagan Longoria takes us through data classifications on Azure SQL Data Warehouse:
Data classifications in Azure SQL DW entered public preview in March 2019. They allow you to label columns in your data warehouse with their information type and sensitivity level. There are built-in classifications, but you can also add custom classifications. This could be an important feature for auditing your storage and use of sensitive data as well as compliance with data regulations such as GDPR. You can export a report of all labeled columns, and you can see who is querying sensitive columns in your audit logs. The Azure Portal will even recommend classifications based upon your column names and data types. You can add the recommended classifications with a simple click of a button.
But read the whole thing, as Meagan sees a problem with it when you use a popular loading technique.
We are recommending to rely on automatic backups only, with the build-in restore functionality to restore a database from a point-in-time, restore a database to another instance (for instance from production to dev)or Geo-restore functionalities to move your database. These automatic backups can be kept up to 35 days. These built-in automatic backups are secure and enables you to be fully compliant. In this scenario COPY_ONLY backups are only in some specific cases.
Strict TDE protection don’t allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporary disable TDE, take a backup, and then enable TDE again.
It’s not really a Managed Instance-specific thing, but rather TDE: if you want to take a non-encrypted backup of an encrypted database, you’ve got to kill encryption first.
Allan Hirt takes us through recent security updates and how they pertain to SQL Server:
After Spectre and Meltdown a few months back (which I cover in this blog post from January 4), another round of processor issues has hit the chipmaker. This one is for MDS (also known as a ZombieLoad) This one comprises the following security issues: CVE-2019-11091, CVE-2018-12126, CVE-2018-12127, and CVE-2018-12130. Whew! Fun fact: CVE stands for “Common Vulnerabilities and Exposures”.
As of now, this is only known to be an Intel, not AMD, issue. That is an important distinction here. The official Intel page on this issue can be found at this link. This issue does not exist in select 8th and 9th generation Intel Core processors as well as the 2nd generation Xeon Scalable processor family. (read: the latest stuff)
Be sure to read through all of this. Most of the notes are for non-SQL Server items which have an impact rather than bugs in SQL Server itself, but that doesn’t make patching any less important.
Kenneth Fisher takes us through a security issue:
If you have to deal with linked servers then you probably have or will run into the following error:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’But I’m not trying to use the linked server. I’m trying to create/alter a stored procedure.
Kenneth explains why you might get this even when you’re just editing a procedure and not directly hitting a linked server, as well as a few tips for fixing the issue.
Yesterday I ran into the dread Kerberos Double-Hop when trying to set up a linked server. Thought it was the standard “Add an SPN using the Microsoft Kerberos Configuration tool”. Which didn’t fix it.
Click through to see what Michael had to do.
Dave Bland walks us through one way to fix an orphaned user:
In my many years of working as a DBA, I have encountered many disabled logins. However, I have never really encountered what looks to be a disabled database user account. I didn’t even think it was possible to disable a user account in a SQL Server database. I checked the user account properties just to makes sure I was correct. Sure enough, no option to disable a user account. This finally turned out to be a simple case of looks can be deceiving.
You can also use the sp_change_users_login procedure to fix orphaned users.
This post is the fourth in a series about installing R packages in SQL Server Machine Learning Services (SQL Server ML Services). To see all posts in the series go to Install R Packages in SQL Server ML Services Series.
Why this series came about is a colleague of mine Dane pinged me and asked if I had any advice as he had issues installing an R package into one of their SQL Server instances. I tried to help him and then thought it would make a good topic for a blog post. Of course, at that time I didn’t think it would be more posts than one, but here we are.
These permissions are a bit more complicated than they might first appear to be.
Kudu supports coarse-grained authorization of client requests based on the authenticated client Kerberos principal. The two levels of access which can be configured are:
1. Superuser – principals authorized as a superuser are able to perform certain administrative functionality such as using the kudu command line tool to diagnose or repair cluster issues.
2.User – principals authorized as a user are able to access and modify all data in the Kudu cluster. This includes the ability to create, drop, and alter tables as well as read, insert, update, and delete data.Access levels are granted using whitelist-style Access Control Lists (ACLs), one for each of the two levels.
Read on to see how to tie it all together.
Solomon Rutzky tries out various methods of loading certificates and private keys in SQL Server:
These results confirm that:
1. You can import a certificate from aVARBINARYliteral
2. You can import a private key when creating a certificate from aVARBINARYliteral
3. You cannot import a private key when creating a certificate from an assembly
4. Except when creating a certificate from an assembly, any combination of sources for the certificate (i.e. public key and meta-data) and the private key should be valid
It’s a long post with a lot of detail and quite a few tests, so check it out.
Kevin Feasel
2019-06-03
Backups, Security